Hello -

My background is networking and system administration.  I know basic
programming constructs, and have done .bat, VBA, and *nix shell
scripting/programming.  I am looking for, at most, two books that can
help me in my new role as DBA.   Most of my SQL work will be bringing
in data that our database application's GUI can't deal with.  An
example is to do Inserts Where IDs are In a list.

What recommendations do you have on books that will serve my purpose?

Thanks

-tom

The Guru's guide to Transact by SQL Ken Henderson
Inside SQL server 2000 by  Kalen Delaney


http://sqlservercode.blogspot.com/



Show quoteHide quote

"The Guru's guide to Transact by SQL Ken Henderson" is a great book for SQL
programmers. 

I would also recommend "Sams teach yourself MS SQL Server in 21 days" for
more dba related issues.

Archer



Show quoteHide quote

in fact, all the books by Ken Henderson

Tom,

1.  Inside SQL server 2000 by  Kalen Delaney  -- by far the most indepth and
the best

2.  Microsoft SQL Server 2000 Unleashed by Ray Rankins
or
     Microsoft SQL Server 2000 Bible by Paul Nielsen

Optional 3rd - for T-SQL programming:

3.  Professional SQL Server 2000 Programming by Robert Vieira

HTH

Jerry

Show quoteHide quote

Get a book on RDBMS basics.  SQL is a declarative, high level language
which is nothing like you have seen or used before.  Once you have
foundations, then get some of my books.  SQL FOR SMARTIES is a classic,
but you might want to start with SQL PROGRAMMING STYLE or DATA &
DATABASES.

..

Tom,

SQL for Smarties is a fine book.  I do not have a copy of the others but I'm
sure they are good as well.

Nice job by the way Joe.

Jerry
Show quoteHide quote

Joe Celko highly recommends The Guru's Guide to Transact SQL by Ken Henderson.

I have read SQL for Smarties and it's good, I really enjoyed SQL Puzzles and
Answers.
Joe do you intend to write an updated puzzle book any time soon?
If so let us know.

http://sqlservercode.blogspot.com/



Show quoteHide quote

well I have peeked insed, thanks to Amazon, and I'm a little bit
surprised

the recommendation to use stored procedures (p. 122) and not to use
functions (p. 123). is it really consistent?
you list "portabilty problems" as reason to avoid UDFs, but for
- T-SQL (MS SQL Server)
- PL/SQL (Oracle)
- SQL-PL (DB2)
SQL UDFs are just as portatble, or not portable, as stored procedures
I don't see any major difference in portability between stored
prodeures and UDFs. Could you please explain?

also the statement on the same that udfs are not inline seems to be not
true, sometimes they are (MS SQL Server):

create table states(state char(2))
insert into states values('IL')
insert into states values('MI')
insert into states values('WI')

create table stateNames(state char(2), sname varchar(20))
insert into stateNames values('IL', 'Illinois')
insert into stateNames values('MI', 'Michigan')
insert into stateNames values('WI', 'Wisconsin')

CREATE FUNCTION getAllStateNames()
RETURNS TABLE
AS
RETURN ( select sname from stateNames )

now look at the execution plan of this statement:

select * from states,getAllStateNames()

obviously the UDF's body is compiled into it. the plan clearly
describes access to stateNames

AFAIK in Oracle the plan would not display what is accessed inside the
UDF, but Oracle is very different

A UDF by itself is useless; I need to port that code plus the procedure
that calls it.   I now have to maintain both modules of code.  It also
can prevent indexes from being used.  Most of what you do with a UDF
can be done with pure code.

But a UDF feels like procedural code to a 3GL programmer, so they
prefer to use it and write SQL that looks and runs like a procedural
language.  Your own example of looking up the names of States
demonstrates the problem.  You could have done a join to the State's
names table and had code that would run without translation on any SQL.

Thank you for answering.
Yet I failed to see in your answer why UDFs are less portable than
stored procedures.

why is this:

CREATE FUNCTION dbo.getAmountByDate()
RETURNS table
AS
RETURN (select order_dt, sum(amount) s from orders group by order_dt)
go

less portable than this:

CREATE procedure dbo.AmountByDate
AS
select order_dt, sum(amount) s from orders group by order_dt
go

I think my example does not demonstrate any problem at all, because I
never do things like this in real life. I needed to demonstrate that
some UDFs are compiled into the query's execution plan they are called
from. So I came up with a very short example

Thanks to everyone for their responses, and I'm sorry Joe had to deal
with the off-topic issue.

Guru's Guide to T-SQL has been ordered.

-tom

No.  The sales were oaky, but not enough to justify a seocnd edition.

--CELKO--

Question about functions
Implicit conversion from string to date
Speeding up queries using TOP
Exporting Stored Procs
LIKE for a Numeric?