Errata

Additional information needed
On page 15, the second bullet item needs additional information.



Change:

"Resource The Resource database was added as of SQL Server 2005 and it holds all system objects. When you query metadata information in a database, this information appears to be local to the database but in practice it resides in the Resource database. "



To:

"Resource The Resource database is a hidden, read-only database that was added as of SQL Server 2005 and it holds the definitions of all system objects. When you query system objects in a database, they appear to reside in the sys schema of the local database, but in practice their definitions reside in the Resource database."

Incorrect paragraph should be disregarded
On page 47, the third paragraph is irrelevant and should be disregarded.



The paragraph to be removed reads:

"Note that the reason that I specified the decimal value 100. (one hundred dot) in the expressions instead of the integer 100 is in order to cause implicit conversion of the integer values val and SUM(val) to decimal values. Otherwise, the division would have been an integer division and the fractional part would have been truncated."

"10/col1 > 2" should be "col2/col1 > 2"
On page 62, the first sentence of the last paragraph on the page contains an incorrect expression.



Change:

"You assume that SQL Server evaluates the expressions from left to right, and that if the expression col1 2 because at this point it is known that the whole expression is FALSE."



To:

"You assume that SQL Server evaluates the expressions from left to right, and that if the expression col1 2 because at this point it is known that the whole expression is FALSE."

accuracy should be precision
On page 76, the paragraph after table 2-1 contains several references to accuracy rather than precision.



Change:

"The storage requirements for the last three data types in Table 2-1 (TIME, DATETIME2, and DATETIMEOFFSET) depend on the accuracy you choose. You specify the accuracy as an integer in the range 0 to 7 representing the fractional second precision. For example, TIME(0) means 0 fractional second precision—in other words, one-second accuracy. TIME(3) means one-millisecond accuracy, and TIME(7) means 100-nanosecond accuracy. If you don’t specify a fractional second precision, SQL Server assumes 7 by default with all three aforementioned types."



To:

"The storage requirements for the last three data types in Table 2-1 (TIME, DATETIME2, and DATETIMEOFFSET) depend on the precision you choose. You specify the precision as an integer in the range 0 to 7 representing the fractional second precision. For example, TIME(0) means 0 fractional second precision—in other words, one-second precision. TIME(3) means one-millisecond precision, and TIME(7) means 100-nanosecond precision. If you don’t specify a fractional second precision, SQL Server assumes 7 by default with all three aforementioned types."

Syntax for DATENAME is incorrect
On page 88, near the middle of the page the syntax for DATENAME is incorrect.



Change:

DATENAME( dt_val, part )To:

DATENAME( part, dt_val)

10274 should be 10739
On page 141, the first sentence of the third full paragraph is incorrect.



Change:

"This query returns the order ID 10274. The outer row’s order ID—10248—is compared with the inner one—10274—and because there’s no match in this case, the outer row is filtered out."



To:

"This query returns the order ID 10739. The outer row’s order ID—10248—is compared with the inner one—10739—and because there’s no match in this case, the outer row is filtered out."

query should be user
On page 157, the third sentence of item 4 refers to a query, rather than a user.



Change:

"In the SELECT list of the outer query, specify DISTINCT country to return only distinct occurrences of countries, because the same country can have more than one query."



To:

"In the SELECT list of the outer query, specify DISTINCT country to return only distinct occurrences of countries, because the same country can have more than one user."

Note from the Author or Editor:
query should be customer

Currently: "...because the same country can have more than one query."

Should be: "...because the same country can have more than one customer."

Error in code output
On page 215, the sample code output at the bottom of the page is incorrect.



Change:

orderid orderdate empid custid qty

----------- -------------------------- -------------- --------- -----------

10001 2007-12-24 00:00:00.000 2 A 12

10005 2007-12-24 00:00:00.000 1 B 20

10006 2008-01-18 00:00:00.000 1 C 14

20001 2008-02-12 00:00:00.000 2 B 12

20002 2009-02-16 00:00:00.000 1 C 20

30001 2007-08-02 00:00:00.000 3 A 10

30003 2009-04-18 00:00:00.000 2 B 15

30004 2007-04-18 00:00:00.000 3 C 22

30007 2009-09-07 00:00:00.000 3 D 30

40001 2008-01-09 00:00:00.000 2 A 40

40005 2009-02-12 00:00:00.000 3 A 10To:

orderid orderdate empid custid qty

----------- -------------------------- -------------- --------- -----------

10001 2007-12-24 2 A 12

10005 2007-12-24 1 B 20

10006 2008-01-18 1 C 14

20001 2008-02-12 2 B 12

20002 2009-02-16 1 C 20

30001 2007-08-02 3 A 10

30003 2009-04-18 2 B 15

30004 2007-04-18 3 C 22

30007 2009-09-07 3 D 30

40001 2008-01-09 2 A 40

40005 2009-02-12 3 A 10

"empid" and "custid" should be "grpemp" and "grpcust"
On page 230, the second sentence of the first paragraph refers to incorrect rows.



Change:

"For example, all rows where empid is 0 and custid is 0 are associated with the grouping set (empid, custid)."



To:

"For example, all rows where grpempis 0 and grpcustis 0 are associated with the grouping set (empid, custid)."

Incorrect page numbers On pages xi-xii, the page numbers for the entries starting with Chapter 10 and on are behind by 2. 319 should be 321, 322 should be 324, etc.

100 should be 10 On page 47, the 5th line in the query at the bottom of the page uses an incorrect number. Change: NTILE(100) OVER(ORDER BY val) AS ntileTo: NTILE(10) OVER(ORDER BY val) AS ntile

Incorrect parenthesis placement On page 53, the third numbered item is incorrect. Change: "3. + (Positive), – (Negative), + (Add), (+ Concatenate), – (Subtract)" To: "3. + (Positive), – (Negative), + (Add), + (Concatenate), – (Subtract)"

"10/col1 > 2" should be "col2/col1 > 2" On page 63, the last sentence of the first paragraph and the second sentence in the third paragraph contain incorrect expressions. Change: "You can see that if SQL Server decides to process the expression 10/col1 > 2 first, this query might fail because of a divide-by-zero error." To: "You can see that if SQL Server decides to process the expression col2/col1 > 2 first, this query might fail because of a divide-by-zero error." Change: "Only if the first CASE expression does not evaluate to TRUE—meaning that col1 is not 0—does the second WHEN clause check whether the expression 10/col1 > 2 evaluates to TRUE." To: "Only if the first CASE expression does not evaluate to TRUE—meaning that col1 is not 0—does the second WHEN clause check whether the expression col2/col1 > 2 evaluates to TRUE."

Syntax for DATEPART is incorrect On page 87, near the middle of the page the syntax for DATEPART is incorrect. Change: DATEPART( dt_val, part )To: DATEPART( part, dt_val)

Incorrect date range used On page 98, the first sentence of step 5 is incorrect. Change: "Because the request involves activity in the year 2004, the query should have a WHERE clause with the appropriate date range fi lter (orderdate >= ‘20040101’ AND orderdate < ‘20050101’)." To: "Because the request involves activity in the year 2007, the query should have a WHERE clause with the appropriate date range filter (orderdate >= '20070101' AND orderdate

appear should be does not appear On page 157, solutions 3 and 4 contain incorrect instructions. In the second sentence of solution 3, change: "Write an outer query against the Employees table returning employees whose IDs appear in the set of employee IDs returned by the subquery." To: "Write an outer query against the Employees table returning employees whose IDs do not appear in the set of employee IDs returned by the subquery." In the second sentence of solution 4, change: "Write an outer query against the Customers table that filters only customer rows where the country attribute appears in the set of countries returned by the subquery." To: "Write an outer query against the Customers table that filters only customer rows where the country does not appear in the set of countries returned by the subquery."

"TOP (100)" should be "TOP (100) PERCENT" On page 174, the fourth line of the last code sample is incorrect. Change: SELECT TOP (100)To: SELECT TOP (100) PERCENT

Incorrect grouping set information On page 230, the last sentence of the second paragraph is incorrect. Change: "The grouping set (a, c) is represented by the integer 10 (1×8 + 0×4 + 1×2 + 0×1), and so on." To: "The grouping set (a, c) is represented by the integer 5 (0×8 + 1×4 + 0×2 + 1×1), and so on."

"work" should be "working" On page 289, in the second sentence of the first paragraph after first query output there is a mistake in word usage. Change: "As for the blocker, in this example you can see the statement that caused the problem, but keep in mind that the blocker may continue work and that the last thing you see in the code isn’t necessarily the statement that caused the trouble." To: "As for the blocker, in this example you can see the statement that caused the problem, but keep in mind that the blocker may continue working and that the last thing you see in the code isn’t necessarily the statement that caused the trouble." Microsoft Press is committed to providing informative and accurate books. All comments and corrections listed above are ready for inclusion in future printings of this book. If you have a later printing of this book, it may already contain most or all of the above corrections.