Advanced Transact-SQL for SQL Server 2000
Chapter 1
General
The “Advantage” boxes in pages: 12, 13, 18, 21, 28, are mistakenly numbered from 8 and on, instead if being numbered from 1 and on.
Pages 22-24
In the steps that explain the result shown in Table 1-14 of the query shown in Listing 1-25, Input1 refers to the Employees table and Input2 refers to the Departments table. Instead, Input1 should refer to the Departments table and Input2 should refer to the Employees table. The correct steps are:
Step 1. Let Input1 = all rows from Departments.
deptno
deptname
100
Engineering
200
Production
300
Sanitation
400
Management
Step 2. Let Input2 = all rows from Employees WHERE deptno = NULL.
empid
empname
deptno
jobid
salary
(Empty set)
Step 3. Let Result = Input1 LEFT OUTER JOIN Input2.
A LEFT OUTER JOIN returns: all matching rows between the two inputs, and also, all rows from the left input that don’t have matches in the right input, with NULLs in the columns of the right input. Since there are no matching rows between Input1 and Input2, all of the rows from Input1 are returned, with NULLs in the columns of Input2.
deptno
deptname
empid
empname
deptno
jobid
salary
100
Engineering
NULL
NULL
NULL
NULL
NULL
200
Production
NULL
NULL
NULL
NULL
NULL
300
Sanitation
NULL
NULL
NULL
NULL
NULL
400
Management
NULL
NULL
NULL
NULL
NULL
If you find following the execution plan confusing, try to think of how SQL Server logically processes the query this way:
1. Join Departments and Employees based on: D.deptno = E.deptno AND E.deptno IS NULL. No matching rows are found based on this join condition, so an empty set is returned.
2. Apply the LEFT OUTER part of the join. Since no matches were found, all rows from Departments are returned with NULLs in the columns from Employees.
Pages 25-27
In the steps that explain the result shown in Table 1-18 of the query shown in Listing 1-27, Input1 refers to the Employees table and Input2 refers to the Departments table. Instead, Input1 should refer to the Departments table and Input2 should refer to the Employees table. The correct steps are:
Step 1. Let Input1 = all rows from Departments.
deptno
deptname
100
Engineering
200
Production
300
Sanitation
400
Management
Step 2. Let Input2 = all rows from Employees.
empid
empname
deptno
jobid
salary
1
Leo
400
30
10000.00
2
George
200
20
1000.00
3
Chris
100
10
2000.00
4
Rob
400
30
3000.00
5
Laura
400
30
3000.00
6
Jeffrey
NULL
30
5000.00
Step 3. Let Input3 = Input1 LEFT OUTER JOIN Input2.
deptno
deptname
empid
empname
deptno
jobid
salary
100
Engineering
3
Chris
100
10
2000.00
200
Production
2
George
200
20
1000.00
300
Sanitation
NULL
NULL
NULL
NULL
NULL
400
Management
1
Leo
400
30
10000.00
400
Management
4
Rob
400
30
3000.00
400
Management
5
Laura
400
30
3000.00
Step 4. Let Result = filter Input3—WHERE empid IS NULL.
deptno
deptname
empid
empname
deptno
jobid
salary
300
Sanitation
NULL
NULL
NULL
NULL
NULL
Page 37
Last sentence in paragraph after Listing 1-40. “You can also use the table qualifier after the first DELETE clause” Should be: “You can also use the table qualifier after the first FROM clause”
Chapter 2
Page 47
Last sentence in first paragraph. “…if any y is NULL, then the entire predicate is FALSE, because x <> NULL is FALSE.” Should be: “…if any y is NULL, then the entire predicate is UNKNOWN, because x <> NULL is UNKNOWN.
Page 54
Listing 2-15 in the book contains the following UPDATE statement:
UPDATE C
SET F.Owner
FROM
Feed AS F
JOIN
Cars AS C ON C.License = C.License
Both the SET clause and the join condition are incorrect. The correct UPDATE statement should look like this:
UPDATE C
SET Owner = F.Owner
FROM
Feed AS F
JOIN
Cars AS C ON F.License = C.License
Page 63
Listing 2-25. The column P.AvgPrice should be replaced with A.AvgPrice.
Listing 2-26. The WHERE clause “A.CategoryID = A.CategoryID” should be replaced with “A.CategoryID = P.CategoryID”.
Page 78
The following INSERT statement:
INSERT INTO Users VALUES(1, ‘Bruce’)
Should be replaced with:
INSERT INTO Users VALUES(1, ‘BPMargolin’)
To comply with the results of the puzzle shown in page 681.
Chapter 4
Page 136
Third sentence in last paragraph. “One of the bits is used as the sign bit.” This sentence is inaccurate as the format used to store integer values in SQL Server is the twos-complement format and not a format that uses a sign bit. However, positive values have the same bit representation when using a sign bit and when using the twos-complement format, so the discussion that follows still applies.
Chapter 6
Page 197
Last sentence in third paragraph. “Finally, the IDENTITY properties of tables…” IDENTITY is a column property and not a table property.
Page 203
First sentence in the second paragraph after Listing 6-10. The stored procedure sp_dboption should be replaced with sp_tableoption.
Page 209
Last sentence in page.
“As for the WRITETEXT statement, you need UPDATE permissions on a table in order to use the UPDATE statement.”
Should be:
“As with the WRITETEXT statement, you need UPDATE permissions on a table in order to use the UPDATETEXT statement.”
Page 210
Third sentence in the second paragraph under the section “Using the bigint Datatype.” The function BIG_COUNT() should be replaced with COUNT_BIG().
Page 216
Unbulleted list at top should include datetime. Last sentence in page. The function OBJECTPROPERTY() should be replaced with SQL_VARIANT_PROPERTY().
Page 227
Last paragraph. “Note that the new_reseed_value changes the current IDENTITY value for the table. Hence, the next inserted value will be new_reseed_value + increment.” The explanation in the book applies if at least one row was inserted into the table since it was created or was last truncated. If no rows have been inserted into the table since it was created or was last truncated, the first row inserted after executing DBCC CHECKIDENT will use new_reseed_value as the identity and not new_reseed_value + increment.
Chapter 9
Page 311
In Listing 9-12, there should be a comma after the word DEFAULT:
EXEC usp_DefaultProc
DEFAULT,
909,
50
Page 335
On page 335, under “Security and Stored Procedures”, in the second paragraph about the roles db_datareader, as well as db_denydatareader and db_denydatawriter. The phrase:
“…those roles to read (INSERT) or write (INSERT, UPDATE, DELETE) data in all tables…”
Should be:
“…those roles to read (SELECT) or write (INSERT, UPDATE, DELETE) data in all tables…”
This occurs twice in the paragraph.
Page 339
Listing 9-51. The comments “– No existing transaction” and “– Transaction in progress” should be swapped.
Chapter 10
Page 359
First sentence on page: Replace the phrase “each table uses” with the phrase “the Child and GrandChild tables use”.
Page 361
Second sentence after Listing 10-21: “If you execute the DELETE statement in Listing 10-21…” should refer to Listing 10-20 instead.
Page 365
Second bullet: “IDENTITY INSERT” should read “IDENTITY_INSERT”.
Chapter 11
Pages 404-405
Listings 11-46 and 11-48 are supposed to have the recursive and non-recursive implementations of the fibonacci algorithm as UDFs. Currently the functions implement a simple summary of all consecutive integer numbers from zero to n where n is the value provided to the function as an argument. The correct recursive implementation of the fibonacci algorithm is:
CREATE FUNCTION dbo.fibonacci
(
@n AS int
)
RETURNS int
AS
BEGIN
RETURN CASE
WHEN @n > 1 THEN dbo.fibonacci(@n – 1) + dbo.fibonacci(@n – 2)
WHEN @n IN (0, 1) THEN @n
ELSE NULL
END
END
GO
The correct non-recursive implementation of the fibonacci algorithm is:
CREATE FUNCTION dbo.fibonacci2
(
@n AS int
)
RETURNS int
AS
BEGIN
IF @n < 0
RETURN NULL
ELSE
IF @n in (0, 1)
RETURN @n
ELSE
BEGIN
DECLARE
@i AS int,
@f1 AS int,
@f2 AS int,
@t AS int
SET @i = 2
SET @f1 = 0
SET @f2 = 1
WHILE @i <= @n
BEGIN
SET @t = @f2
SET @f2 = @f1 + @f2
SET @f1 = @t
SET @i = @i + 1
END
END
RETURN @f2
END
GO
page 453 Listing 12-17: Calculating Discounts.
SELECT
OrderID,
Total,
Previous4,
CASE
WHEN Previous4 < 10000.00 THEN 5
WHEN Previous4 BETWEEN 10000.00 and 15000.00 THEN 10
ELSE 20
END AS Discount
FROM
(
SELECT
T1.OrderID,
T1.Total, — The book reads “T2.Total”
(
SELECT
SUM(T2.Total) AS Total
FROM
#Totals AS T2
WHERE
T2.Sequence >= T1.Sequence – 4 — The book reads “T2.Sequence > T1.Sequence – 4”
AND
T2.Sequence < T1.Sequence
) AS Previous4
FROM
#Totals AS T1
WHERE
0 = T1.Sequence % 5
) AS X
Thank you
We would like to express our sincere gratitude to the following people who sent us comments and corrections to the book:
BP Margolin
Dejan Sarka
