|
|
|
|
Advanced Transact-SQL for SQL Server 2000 Chapter 1GeneralThe "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-24In 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:
Pages 25-27In 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:
Page 37Last 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 2Page 47Last 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 54Listing 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 63Listing 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 78The 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 4Page 136Third 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 6Page 197Last sentence in third paragraph. “Finally, the IDENTITY properties of tables…” IDENTITY is a column property and not a table property. Page 203First sentence in the second paragraph after Listing 6-10. The stored procedure sp_dboption should be replaced with sp_tableoption. Page 209Last sentence in page. Should be: Page 210Third sentence in the second paragraph under the section “Using the bigint Datatype.” The function BIG_COUNT() should be replaced with COUNT_BIG(). Page 216Unbulleted list at top should include datetime. Last sentence in page. The function OBJECTPROPERTY() should be replaced with SQL_VARIANT_PROPERTY(). Page 227Last 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 9Page 311In Listing 9-12, there should be a comma after the word DEFAULT: EXEC usp_DefaultProc DEFAULT, 909, 50 Page 335On 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 339Listing 9-51. The comments "-- No existing transaction" and "-- Transaction in progress" should be swapped. Chapter 10Page 359First sentence on page: Replace the phrase "each table uses" with the phrase "the Child and GrandChild tables use". Page 361Second sentence after Listing 10-21: "If you execute the DELETE statement in Listing 10-21..." should refer to Listing 10-20 instead. Page 365Second bullet: "IDENTITY INSERT" should read "IDENTITY_INSERT".
Chapter 11Pages 404-405Listings 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 youWe would like to express our sincere gratitude to the following people who
sent us comments and corrections to the book: Contact usIf you have any comments or corrections we will be glad to hear from you. You
can contact us through the following e-mail addresses: | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
[SolidQ Home] [SolidQ Blog] [SolidQ Courses] [The SolidQ journal] |
