Errata

The third sentence of the second paragraph did read:

"For example, the trick name appears in every animal record,
but the trick_nbr should be sufficient since we've already got
the name in the Tricks table."

Now reads:

"For example, both the trick_nbr and trick_name appear in every
record of the old, 1NF Trick table. However, we don't need the
trick_name in the 2NF Animal Tricks table since we've already got
the trick_name in the 2NF Tricks table."

"au_lnname" and "au_fnname" in the "authors" table to "au_lname" and
"au_fname".

The second paragraph of comments for DEFAULT did read:

"Defaults may be table- or column-level."

Now reads:

"A column default may be declared with the column definition or
with the table definition at the end of the CREATE TABLE statement."

SQL Server 2000 and later versions will not allow double quotes in the line DEFAULT ("CA")...
it should use single quotes, like DEFAULT ('CA')

page:

"Cross join syntax is a Microsoft extension and is not supported on
Sybase ASE 11.5."

follows:

SELECT [ALL | DISTINCT] select_item1,
select_item2, select_item3, ...
[INTO [table_name]]
[FROM tablename [alias]
[(index {index_name | table_name }
[parallel [degree_of_parallelism]]
[prefetch size ][lru|mru])
[holdlock | noholdlock] [shared]] [,...,
[,...,tablename16 [alias16]
[(index {index_name | table_name }
[parallel [degree_of_parallelism]]
[prefetch size ][lru|mru])
[holdlock | noholdlock] [shared]]
[WHERE clause ]
[GROUP BY clause ]
[HAVING clause ]
[ORDER by clause ]
[COMPUTE clause ]
[FOR { READ ONLY | UPDATE [ OF list_of_
columns]}]
[AT ISOLATION {READ UNCOMMITTED | READ
COMMITTED |SERIALIZABLE}]
[FOR BROWSE]

read:

Select e.emp_id,
(SELECT MAX(hire_date) FROM employee) = "Max_Hire_Date"
FROM employee

Now reads:

SELECT e.emp_id,
(SELECT MAX(hire_date) FROM employee) = "Max_Hire_Date"
FROM employee AS e

The column describing the contents of the New_Sales table should contain
'payment_terms CHAR(12) NOT NULL' immediately under the 'order_date' entry.
Adding this value in makes the following text correct.

The second paragraph did read:

"For example, the following identifier references the byroyalty
stored procedure in the master database owned by the developer
auser."

Now reads:

"For example, the following identifier references the byroyalty
stored procedure in the pubs database owned by the developer dbo
(database owner):" (note the colon)

backward. It should be "| = OR" and "^ = Exclusive OR."

The following WHERE clause from the book:
"...WHERE LogID = (SELECT MAX(LogID) FROM StockLog) AND Type!='N'..."

Will return NO RECORDS AT ALL if the most recent Log record is of Type 'N'. It is
true that we don't want to deal with Type 'N' records, but the goal is to find the
most recent change that was not of type 'N'.
The following minor change to the WHERE clause will remedy that:

"...WHERE LogID = (SELECT MAX(LogID) FROM StockLog WHERE Type!='N')..."

The third sentence in the third paragraph did read:

"There are 10 basic categories of system datatypes..."

Now reads:

"There are 9 basic categories of system datatypes..."

"The INT datatype can accomodate values ranging from -2,147,483,647 to 2,147,483,647; consuming..."

NOW READS:
The INT datatype can accomodate values ranging from -2,147,483,648 to 2,147,483,647; consuming..."

"Another approach to the multitest IF..."

Has been replaced with the following text:

"Another approach to the multitest IF statement is available
through the OR keyword. The OR keyword enables you to couple
several conditional statements together. If any one of the
conditional statements meet the condition of the IF statement,
then the entire condition is considered TRUE. Only when all
the conditions fail to meet the condition of the IF statement
will the IF statement evaluate to FALSE. An IF...OR statement
might look like this:"

The first sentence of the second full paragraph did read:

"This example tests for the existence of two values in the titles
table."

Now reads:

"This example tests for the existence of a value in the titles
table."

The text above the header "Nested IF...ELSE Statements" did read:

"IF EXISTS (SELECT * FROM titles WHERE title_id IN ('FD1711',
'FD1032'))BEGIN SELECT...

In fact, some Transact-SQL gurus prefer to smash the BEGIN clause
onto the same line as the IF. (You would have to place the SELECT
on a new line, however.)"

Now reads:

"IF EXISTS (SELECT * FROM titles WHERE title_id IN ('FD1711',
'FD1032')) BEGIN UPDATE...

In fact, some Transact-SQL gurus prefer to smash the BEGIN clause
onto the same line as the IF. (You would have to place the UPDATE
on a new line, however.)"

The block of code did read:

IF @inventory_level <= 100 GOTO step2

IF @inventory_level BETWEEN 99 and 21
BEGIN
EXECUTE order_books @title_id
step2
END

IF @inventory_level >= 20 GOTO dangerously_low_inv

step2:
...<some more code here>...

dangerously_low_inv:
EXECUTE order_books @title_id
RAISERROR('This item may have been backordered!',16,1)
step2

Now reads:

IF @inventory_level >= 100 GOTO step2

IF @inventory_level BETWEEN 21 and 99
BEGIN
EXECUTE order_books @title_id
GOTO step2
END

IF @inventory_level <= 20 GOTO dangerously_low_inv

step2:
...<some more code here>...

dangerously_low_inv:
EXECUTE order_books @title_id
RAISERROR('This item may have been backordered!',16,1)
GOTO step2

text:

"This command returns no result, but it impacts global variables. In
Sybase the @@rowcount global variable is incremented every time there
is a fetch within a cursor. This means that only once all the rows have
been fetched can you determine how many rows were actually in the
result set to begin with. Microsoft provides a separate global variable
@@cursor_rows to report the number of rows in the last opened cursor:"

Changed "cursor_name" to "DEALLOCATE cursor_name".

DEADLOCKPRIORITY" (added a space).

Line 27 did read:

"SET big_return = *"

Now reads:

"SET big = '*'"

Line 28 did read:

"WHERE total_return > 1000"

Now reads:

"WHERE total_revenue > 1000"

"FOR INSERT, UPDATE"

Has been changed to:

"FOR INSERT, UPDATE, DELETE"

The third line of the code sample did read:

"FOR INSERT,UPDATE"

Now reads:

"FOR INSERT, UPDATE, DELETE"

The last line on the page did read:

"FOR insert,update"

Now reads:

"FOR insert, update, delete"

Line 16 did read:

RAISERROR 50001"Cannot create entry for non-existent title"

Now reads:

RAISERROR 50001 "Cannot create entry for non-existent title"

Line 24 did read:

WHERE a.author_id = b.author_id) <> @rcount

Now reads:

"WHERE a.au_id = b.au_id) <> @rcount"

Now reads:

"ROLLBACK TRIGGER (Sybase only)"

Filename | Procedure | Purpose
____________|_______________|_____________________________________
SQLV600.SQL | sp_rtable | Reverse-engineer a table
SQLV600.SQL | sp_ralltables | Reverse-engineer all tables...
SQLV600.SQL | sp_rindex | Reverse-engineer indexes...
SQLV600.SQL | sp_rallindex | Reverse-engineer all indexes...
gserver.SQL | sp_gserver | Reverse-engineer the contents...
SQLV600.SQL | sp_rbinddefs | Reverse-engineer binding...
SQLV600.SQL | sp_rbindrules | Reverse-engineer binding...

The first sentence of the third paragraph did read:

"Once you have isssued SET NOEXEC OFF, the only command that may be
executed is SET NOEXEC ON."

Now reads:

"Once you have isssued SET NOEXEC ON, the only command that may be
executed is SET NOEXEC OFF."

Performance tuning and monitoring" has been removed from the table.

The entry for CHECKIDENT in Table 21-3 did read:

"CHECKIDENT | | MS | Verifies that the internal
identify value for

Now reads:

"CHECKIDENT | SA or DBO | MS | Verifies that the internal
identify value for

The following entry was added below CHECKIDENT:

| Permis-| Vendor |
DBCC Command | sions | Support | Description

CHECKSTORAGE | SA | Sybase | CHECKSTORAGE is a powerful set of DBCC
checks available only on Sybase servers.
It requires significant database administrator
setup and configuration to use properly,
including the creation of the dbccdb system
database. We are excluding this command
because it is outside the toolset of the
average Transact-SQL developer.

In Table 21-3, column 1 did read:

"DREINDEX([..."

Now reads:

"DBREINDEX([..."

The entry for PAGE in Table 21-3 did read:

"PAGE | | MS | Prints out the contents of a specific page"

Now reads:

"PAGE | SA | MS | Prints out the contents of a specific page"

Server Version 7 omits the new function YEAR. It should be placed after the
description of VARP and before the + and -.

The row in the table explaining the YEAR function should appear as follows:

Function Type Syntax Description
-----------------------------------------------
YEAR | Date | YEAR(date) | This is functionally equivalent to the
DATEPART(year, date) function. It returns
a 4-digit year.

{CD} The calc_db_size.sql script on the CD has an error. The new
calc_db_size.sql script is listed below as a replacement for the earlier,
buggy script:

CREATE PROC calculate_db_space @dbname VARCHAR(30) AS

-- declare necessary variables
DECLARE @ld_dbsize DEC(15,2), @ld_dbunused DEC(15,2)
DECLARE @msg_line VARCHAR(50)

-- don't run this calculation on TEMPDB
IF (@dbname <> 'tempdb')
BEGIN
-- the SET NOCOUNT ON command reduces unneeded feedback from SQL Server
SET NOCOUNT ON
-- calculate the space used
SELECT @ld_dbsize = SUM(CONVERT(DEC(15,2),size))
FROM master.dbo.sysusages
WHERE dbid = DB_ID(@dbname)
-- calculate the unused space
SELECT @ld_dbunused = @ld_dbsize - (SELECT SUM(CONVERT(DEC(15,2),reserved))
FROM sysindexes
WHERE indid IN (0, 1, 255))
-- print the output
SELECT "Megabytes Assigned" = (@ld_dbsize/512),
"Megabytes Unused" = (@ld_dbunused/512)
END
ELSE
BEGIN
-- build the error msg telling which procedure spawned the error.
SELECT @msg_line = 'TEMPDB is very transient. Don''t bother sizing it.'
RAISERROR(@msg_line,16,2) WITH LOG
-- quit the program
RETURN
END