Microsoft SQL Server 2008 T-SQL Fundamentals

Errata for Microsoft SQL Server 2008 T-SQL Fundamentals




The errata list is a list of errors and their corrections that were found after the product was released. If the error was corrected in a later version or reprint the date of the correction will be displayed in the column titled "Date Corrected".

The following errata were submitted by our customers and approved as valid errors by the author or editor.

Color Key: Serious Technical Mistake Minor Technical Mistake Language or formatting error Typo Question Note Update



Version Location Description Submitted By Date Submitted Date Corrected
Printed
Page xi-xii

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.

Microsoft Press  May 06, 2010 
Printed
Page Page 371
Appendix A, Data Model

Data Model, Middle table name Currently: Sales.OrdersDetails Should be: Sales.OrderDetails Data Model, Bottom middle table name Currently: Production, Products Should be: Production.Products

Anonymous  Jun 12, 2012 
Printed
Page 15

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."

Microsoft Press  Jul 13, 2010 
Printed
Page 47

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."

Microsoft Press  Jul 13, 2010 
Printed
Page 47

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

Microsoft Press  May 06, 2010 
Printed
Page 53

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)"

Microsoft Press  May 06, 2010 
Printed, PDF
Page 57
Various locations

All (I count five [5]) occurrences of "Less then 1000" need to be replaced with "Less than" on p. 57.

Note from the Author or Editor:
Confirmed. All references to "less then 1000" in both the query and the query output should be replaced with "less than 1000"

Art Bergquist  Jun 13, 2012 
Printed
Page 62

"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."

Microsoft Press  Jul 13, 2010 
Printed
Page 63

"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."

Microsoft Press  May 06, 2010 
Printed
Page 76

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."

Microsoft Press  Jul 13, 2010 
Printed, PDF
Page 77
1st full paragraph

In the 4th sentence, please replace the incorrect word "covert" with the correct "convert"; the corrected sentence reads as follows: SQL Server defines precedence among datatypes, and will usually implicitly convert the operand that has lower datatype precedence to the one that has higher precedence."

Note from the Author or Editor:
Confirmed. Thanks!

Art Bergquist  Jun 18, 2012 
Printed
Page 87

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)

Microsoft Press  May 06, 2010 
Printed
Page 88

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)

Microsoft Press  Jul 13, 2010 
Printed
Page 98

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

Microsoft Press  May 06, 2010 
Printed, PDF
Page 117
1st sentence of the last paragraph

The following sentence: "The next step is to extend the previous query, adding a left outer join between Nums and the Orders tables." with either: "The next step is to extend the previous query, adding a left outer join between Nums and the Orders table." or: "The next step is to extend the previous query, adding a left outer join between the Nums and Orders tables." or: "The next step is to extend the previous query, adding a left outer join between the Nums table and the Orders table."

Note from the Author or Editor:
Confirmed. Should be: "The next step is to extend the previous query, adding a left outer join between the Nums and Orders tables." Thanks!

None  Jun 21, 2012 
Printed
Page 141

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."

Microsoft Press  Jul 13, 2010 
Printed
Page 157

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."

Microsoft Press  Jul 13, 2010 
Printed
Page 157

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."

Microsoft Press  May 06, 2010 
PDF
Page 157
3rd sentence of Item 4 on p. 157

In the errata for p. 157 found at http://oreilly.com/catalog/errata.csp?isbn=9780735626010, shouldn't the three (3) occurrences of "user" be replaced with "customer" in the following: 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:
Confirmed. The errata item in page 157 should be changed: 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."

Art Bergquist  Jun 11, 2012 
Printed
Page 174

"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

Microsoft Press  May 06, 2010 
Printed, PDF
Page 188
Exercise 5-2

My bad; scratch the other errata for Exercise 5-2; here's the correct(ed) errata for Exericse 5-2: "4-1" needs to be replaced with "5-1" in Exercise 5-2: "Using the CROSS APPLY operator and the function you created in Exercise 4-1, return, for each supplier, the two most expensive products." The correct(ed) sentence reads as follows: "Using the CROSS APPLY operator and the function you created in Exercise 5-1, return, for each supplier, the two most expensive products."

Note from the Author or Editor:
Confirmed. Thanks!

Art Bergquist  Jul 18, 2012 
Printed, PDF
Page 194
Figure 6-1

The sentence just above Figure 6-1 reads as follows: "The area marked with diagonal lines represents the result of the set operation." The result of the set operation in Figure 6-1 appears to be marked with a gray background not diagonal lines.

Note from the Author or Editor:
Error confirmed. Thanks for reporting it!

Art Bergquist  Jul 18, 2012 
Printed
Page 209
Second line

Chapter 6, Page 209, Second line Currently: "...before the rows from Customers,..." Should be: "...before the rows from Shippers,..."

Note from the Author or Editor:
Confirmed. Thanks!

Anonymous  Apr 22, 2011 
Printed, PDF
Page 209
Chapter 6, p. 209, 2nd line

In the following unconfirmed errata: - Printed Page 209 - Second line Chapter 6, Page 209, Second line - Currently: "...before the rows from Customers,..." Should be: "...before the rows from Shippers,..." - Anonymous - Apr 22, 2011 technically, "Shippers" should be replaced with "Suppliers" (cf. "Suppliers" in the last line of p. 208)

Note from the Author or Editor:
Confirmed. Thanks!

Art Bergquist  Jun 18, 2012 
Printed
Page 215

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

Microsoft Press  Jul 13, 2010 
Printed, PDF
Page 217
Last line on the page

In the last line on p. 217, a sentence is started (and then finished on p. 218). The beginning of the sentence reads as follows: "You achieve this is by not applying the PIVOT operator to the original" Please remove the word "is"; it shouldn't be in that sentence. The resultant text reads as follows: "You achieve this by not applying the PIVOT operator to the original"

Note from the Author or Editor:
Confirmed. Thanks for the feedback!

Art Bergquist  Jul 25, 2012 
Printed, PDF
Page 221
1st full sentence on the page

In 1st full sentence on the page: "You need apply a cross join between the EmpCustOrders table and a table that has a row for each customer." the word "to" needs to be inserted in between "need" and "apply". In addition, I recommend replacing "cross join" with "CROSS JOIN" (I would extrapolate that recommendation for any SQL Server keyword in any sentence throughout the book.) The resultant sentence reads as follows: "You need to apply a CROSS JOIN between the EmpCustOrders table and a table that has a row for each customer."

Note from the Author or Editor:
Confirmed. The error is in Chapter 7, Page 221, but in the Second full sentence in the page (third line). Thanks!

Art Bergquist  Jul 25, 2012 
Printed
Page 230

"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)."

Microsoft Press  Jul 13, 2010 
Printed
Page 230

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."

Microsoft Press  May 06, 2010 
Printed, PDF
Page 246
3rd full paragraph

In the 3rd full paragraph, I recommend replacing "row to T1" with "row into T1" in the following text: "For example, the following code demonstrates how to insert a row to T1 with the explicit value 5 in keycol:' The resultant text reads as follows: "For example, the following code demonstrates how to insert a row into T1 with the explicit value 5 in keycol:'

Note from the Author or Editor:
Confirmed. Thanks!

Art Bergquist  Jul 30, 2012 
Printed, PDF
Page 275
Exercise 3

In addition to the two (2) DELETE FROM solutions that were provided, the following DELETE FROM also works: DELETE FROM dbo.Orders WHERE CustId IN (SELECT CustId FROM dbo.Customers WHERE Country = N'Brazil');

Note from the Author or Editor:
True. Often in SQL the same task can be implemented in many different ways. Cheers.

Art Bergquist  Aug 02, 2012 
Printed, PDF
Page 276
2nd sentence in Solution # 5

In the 2nd sentence in Solution # 5: "You can join Orders and Customers based on a match between the order's customer ID and the customers customer ID." please replace "customers" with (the possessive) "customer's". The resultant sentence reads as follows: "You can join Orders and Customers based on a match between the order's customer ID and the customer's customer ID."

Note from the Author or Editor:
Confirmed.

Art Bergquist  Aug 02, 2012 
Printed
Page 288
Output of query

Chapter 9, Page 288, Output of query Currently: Contains the result column most_recent_sql_handle twice. Should be: Only once. Should remove third part of the output from the page.

Note from the Author or Editor:
Confirmed. Thanks!

Anonymous  Oct 05, 2010 
Printed
Page 289

"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.

Microsoft Press  May 06, 2010 
Printed, PDF
Page 328
Last sentence of the last paragraph (not including code at the bottom of the page)

In the last sentence of the last paragraph (not including code at the bottom of the page): "For example, the following code runs a full backup of the sample database TSQLFundamentals2008 if today is the first day of the month, and a differential backup (changes since last full backup) if today is not the last day of the month." the word "last" needs to be replaced with "first". The corrected sentence reads as follows: "For example, the following code runs a full backup of the sample database TSQLFundamentals2008 if today is the first day of the month, and a differential backup (changes since last full backup) if today is not the first day of the month."

Note from the Author or Editor:
Confirmed. Thanks!

Art Bergquist  Aug 14, 2012 
Printed
Page 329
Chapter 10, Page 329, Lines 5 and 6

Error sent to me by Justin Wilson. Currently: BACKUP DATABASE TSQLFundamentals2008 TO DISK = 'C:\Temp\TSQLFundamentals2008_Diff.BAK' WITH INIT; Should be: BACKUP DATABASE TSQLFundamentals2008 TO DISK = 'C:\Temp\TSQLFundamentals2008_Diff.BAK' WITH INIT, DIFFERENTIAL; Regards, Itzik

Itzik Ben-Gan  Aug 03, 2010 
Printed, PDF
Page 329
3rd line of code on the page

To be consistent with all the other PRINT statements on the same page as well as on the previous page, the 3rd line of code on the page, viz., PRINT 'Today is not the first day of the month.' should have a semi-colon at the end the statement.

Note from the Author or Editor:
Confirmed. Thanks!

Art Bergquist  Aug 14, 2012 
Printed, PDF
Page 333
3rd-to-last line on the page

In the 3rd-to-last line on the page: @ordermonth DATETIME, I recommend inserting "AS " before "DATETIME" to make the DECLARE-ation of "@ordermonth" consistent with all the other DECLARE-ations. The more consistent DECLARE-ation reads as follows: @ordermonth AS DATETIME,

Note from the Author or Editor:
Confirmed. Thanks!

Art Bergquist  Aug 14, 2012 
Printed, PDF
Page 347
1st line of the page

In the 1st line of (and in the 1st sentence on) the page, please replace "chose" with "choose" in the following text: "As of SQL Server 2005 you can chose whether to develop a routine with T-SQL or with .NET code based on the Common Language Runtime (CLR) integration in the product." I also recommend inserting a comma (",") immediately after "2005"; here is the sentence that results from incorporating both changes: "As of SQL Server 2005, you can choose whether to develop a routine with T-SQL or with .NET code based on the Common Language Runtime (CLR) integration in the product."

Note from the Author or Editor:
Confirmed.

Art Bergquist  Aug 21, 2012 
Printed, PDF
Page 348
1st sentence of the first (non-code) paragraph

In the 1st sentence of the first (non-code) paragraph, I recommend replacing "smaller" with "earlier" in the following sentence: "The function calculates the age as the difference, in terms of years, between the birth year and the event year, minus 1 year in case within the year, the event month and day is smaller than the birth month and day." The resultant sentence reads as follows: "The function calculates the age as the difference, in terms of years, between the birth year and the event year, minus 1 year in case within the year, the event month and day is earlier than the birth month and day."

Note from the Author or Editor:
Confirmed.

Art Bergquist  Aug 21, 2012