Inside Microsoft SQL Server 2005: T-SQL Querying

Errata for Inside Microsoft SQL Server 2005: T-SQL Querying




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 8

Correction to table keys On page 8, at the bottom of sidebar, there is an error. Change: “On the other hand, UNIQUE and PRIMARY KEY constraints, sorting, and grouping treat NULLs as equal: You cannot insert into a table two rows with a NULL in a column that has a UNIQUE or PRIMARY KEY constraint defined on it.” To: “On the other hand, a UNIQUE constraint, sorting, and grouping treat NULLs as equal: You cannot insert into a table two rows with a NULL in a column that has a UNIQUE constraint defined on it.”

Microsoft Press  May 06, 2010 
Printed
Page 20

tag used in place of tag On page 20, the second line of the second table operator reads: {CROSS | OUTER} APPLY It should read: {CROSS | OUTER} APPLY

Microsoft Press  Jul 13, 2010 
Printed
Page 43

Yes missing from Figure 2-7 On page 43, in Figure 2-7, there should be a "Yes" above the arrow pointing from "Does query qualify for parallel plan?" to "Phase 1 parallel plan".

Microsoft Press  May 06, 2010 
Printed
Page 51

Link in MoreInfo box is incorrect On page 51, the first sentence inside the MoreInfo box contains a link that needs to be changed. Change: "A good example of extracting data from the XML showplan can be found at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/xmlshowplans.asp." To: "A good example of extracting data from the XML showplan can be found at http://msdn2.microsoft.com/en-us/library/ms345130.aspx."

Microsoft Press  Jul 13, 2010 
Printed
Page 68

THEN repeated in code sample On page 68, lines 32-35 in the code sample reads: - CASE WHEN n % 10 = 0 THEN THEN 1 + ABS(CHECKSUM(NEWID())) % 30 ELSE 0 END AS orderdateIt should read: - CASE WHEN n % 10 = 0 THEN 1 + ABS(CHECKSUM(NEWID())) % 30 ELSE 0 END AS orderdate

Microsoft Press  May 06, 2010 
Printed
Page 73

"signal_wait_time" should be "signal_wait_time_ms" On page 73, the last paragraph on the page contains an incorrect wait_time. Change: "The DMV sys.dm_os_wait_stats contains the following attributes: wait_type; waiting_tasks_count, which is the number of waits on this wait type; wait_time_ms, which is total wait time for this wait type in milliseconds (including signal_wait_time); max_wait_time_ms; and signal_wait_time, which is the difference between the time the waiting thread was signaled and when it started running." To: "The DMV sys.dm_os_wait_stats contains the following attributes: wait_type; waiting_tasks_count, which is the number of waits on this wait type; wait_time_ms, which is total wait time for this wait type in milliseconds (including signal_wait_time); max_wait_time_ms; and signal_wait_time_ms, which is the difference between the time the waiting thread was signaled and when it started running."

Microsoft Press  Jul 13, 2010 
Printed
Page 81

Missing spaces in Table 3-3 On page 81, the items in the "counter_name" column read: "Buffercachehitratio Buffer cache hit ratio base Pagelookups/sec Freeliststalls/sec Freepages Totalpages Targetpages Databasepages Reservedpages Stolenpages" They should read: "Buffer cache hit ratio Buffer cache hit ratio base Page lookups/sec Free list stalls/sec Free pages Total pages Target pages Database pages Reserved pages Stolen pages"

Microsoft Press  May 06, 2010 
Printed
Page 101

"1000" used in place of "1000000" On page 101, the second and fourth line in the sample code reads: CAST(AQ1.total_duration / 1000. CAST(SUM(AQ2.total_duration) / 1000.It should read: CAST(AQ1.total_duration / 100000. CAST(SUM(AQ2.total_duration) / 100000.

Microsoft Press  Jul 13, 2010 
Printed
Page 102, 103

"1000" used in place of "1000000" On pages 102 and 103, the 4th and 6th line of the code samples reads: CAST(AQ1.total_duration / 1000. CAST(SUM(AQ2.total_duration) / 1000.It should read: CAST(AQ1.total_duration / 100000. CAST(SUM(AQ2.total_duration) / 100000.

Microsoft Press  May 06, 2010 
Printed
Page 125
Figure 3-17 Heap

The arrow from Pointer to first(1:47120) and the arrow from Slot 0 = (1:174) in first IAM are pointing to the same page 1:174, but they are two differnt pages, one is 1:47120, the other one is 1:174, how come they are pointing to the same page 1:174. I do understand Pointer to first is not guaranteed to point to the first page after deletion operation.

Note from the Author or Editor:
Confirmed. The "Pointer to first 1:4720" and the associated arrow should be removed from the figure.

Baihao Yuan  Jun 30, 2011 
Printed
Page 137

Ordered scan referenced rather than unordered scan On page 137, the third sentence on the page reads: "With no fragmentation at all, the performance of an ordered scan of an index should be very close to the performance of an ordered scan, because both will end up reading the data physically in a sequential manner." It should read: "With no fragmentation at all, the performance of an ordered scan of an index should be very close to the performance of an unordered scan, because both will end up reading the data physically in a sequential manner."

Microsoft Press  Jul 13, 2010 
Printed
Page 139

"not used" should be "used not" On page 139, the first sentence on the page the words "not used" are out of sequence. Change: "An ordered index scan is not used only when you explicitly request the data sorted; rather, it is also used when the plan uses an operator that can benefit from sorted input data." To: "An ordered index scan is used not only when you explicitly request the data sorted; rather, it is also used when the plan uses an operator that can benefit from sorted input data."

Microsoft Press  May 06, 2010 
Printed
Page 140

The orderid should be 120 in the sixth sentence of the paragraph following the query On page 140, in the paragraph following the query, sentence 6 reads: "The second part of the access method is an ordered partial scan in the leaf level from the first key in the range until the last (orderid = 101)." It should read: "The second part of the access method is an ordered partial scan in the leaf level from the first key in the range until the last (orderid = 120)."

Microsoft Press  Jul 13, 2010 
Printed
Page 141

Index Scan referenced in place of Index Seek On page 141, the first sentence of the last paragraph reads: "Note that in the execution plan you won’t explicitly see the partial scan part of the access method; rather, it’s hidden in the Index Scan operator." It should read: "Note that in the execution plan you won’t explicitly see the partial scan part of the access method; rather, it’s hidden in the Index Seek operator."

Microsoft Press  May 06, 2010 
Printed
Page 158

The term "grows larger" is used instead of the term "gets lower" On page 158, the second sentence of the paragraph after figure 3-49 reads: "As the selectivity of the query grows larger, the more substantial the cost is of the lookups here." It should read: "As the selectivity of the query gets lower (low selectivity = high percentage of rows), the more substantial the cost is of the lookups here."

Microsoft Press  Jul 13, 2010 
Printed
Page 158
Figure 3-49

Figure 3-49 incorrectly features an Index Scan operation and Clustered Index Seek operation when it should contain on Index Scan operation and a Key Lookup operation instead.

Note from the Author or Editor:
There was a change in the representation of a lookup on a table with a clustered index in the graphical execution plans in SQL Server 2005 Service Pack 2. Prior to Service Pack 2, the graphical execution plan showed a Clustered Index Seek operator to represent a lookup on a table with a clustered index. This was a correct representation of reality, but confused people. In Service Pack 2 a lookup on a table with a clustered index is now represented as a Key Lookup operator. Therefore, if you examine the execution plans for the queries in the book involving lookups on a table with a clustered index (not just in Chapter 3, rather throughout the book), you will get slightly different plans than the ones in the book's figures. The meaning of the plans is still the same of course. For details, please refer to: http://msdn2.microsoft.com/en-us/library/bb326635.aspx.

Tim Benninghoff  Jan 24, 2011 
Printed
Page 162

The third line from the top needs UNIQUE removed and orderdate replacing orderid On page 162, the 3rd line from the top reads: "CREATE UNIQUE CLUSTERED INDEX idx_cl_od ON dbo.Orders(orderid);" It should read: "CREATE CLUSTERED INDEX idx_cl_od ON dbo.Orders(orderdate);"

Microsoft Press  May 06, 2010 
Printed
Page 163-166

Unordered Nonclustered Index Scan referred to as Unordered Covering Nonclustered Index Scan On page 163, the first cell in the second and third rows of Table 3-16 read: "Unordered Covering Nonclustered Index Scan Unordered Covering Nonclustered Index Scan + Lookups" They should read: "Unordered Nonclustered Index Scan Unordered Nonclustered Index Scan + Lookups" On page 164, the second and third items on the left-hand side of the key for Figure 3-55 read: "Unordered Covering Nonclustered Index Scan Unordered Covering Nonclustered Index Scan + Lookups" They should read: "Unordered Nonclustered Index Scan Unordered Nonclustered Index Scan + Lookups" On page 164, the first cell in the second and third rows of Table 3-17 read: "Unordered Covering Nonclustered Index Scan Unordered Covering Nonclustered Index Scan + Lookups" They should read: "Unordered Nonclustered Index Scan Unordered Nonclustered Index Scan + Lookups" On page 165, the second and third items on the left-hand side of the key for Figure 3-56 read: "Unordered Covering Nonclustered Index Scan Unordered Covering Nonclustered Index Scan + Lookups" They should read: "Unordered Nonclustered Index Scan Unordered Nonclustered Index Scan + Lookups" On page 166, the labels for the second and third columns in Figure 3-57 read: "Unordered Covering Nonclustered Index Scan Unordered Covering Nonclustered Index Scan + Lookups" They should read: "Unordered Nonclustered Index Scan Unordered Nonclustered Index Scan + Lookups"

Microsoft Press  Jul 13, 2010 
Printed
Page 168

"scan fragmentation" should be "page density" On page 168, the third sentence of the second paragraph contains an incorrect reference to "scan fragmentation". Change: "While logical scan fragmentation is never a good thing, average scan fragmentation has two facets." To: "While logical scan fragmentation is never a good thing, average page density has two facets."

Microsoft Press  May 06, 2010 
Printed
Page 173

Customer information referenced rather than session information On page 173, the second sentence in the second paragraph reads: "Ideally, you should be thinking about the number of customers, the number of different order dates, and so on." It should read: "Ideally, you should be thinking about realistic distribution of session start times, session duration, and so on."

Microsoft Press  Jul 13, 2010 
Printed
Page 173

Listing 3-7 table name should be BigSessions instead of sessions On page 173, the caption of Listing 3-7 reads: "Populate sessions with inadequate sample data" It should read: "Populate BigSessions with inadequate sample data"

Microsoft Press  May 06, 2010 
Printed
Page 187

"yes" should be "no" On page 187, the third sentence after Figure 3-66 contains an incorrect word. Change: "If the answer is yes, another seek operation is invoked against the index to check whether an order exists at all." To: "If the answer is no, another seek operation is invoked against the index to check whether an order exists at all."

Microsoft Press  Jul 13, 2010 
Printed
Page 192

Correction in fourth paragraph subquery example instructions On page 192, the fourth paragraph subquery example instructions reads: "For example, run the following code three times: once as shown, a second time with LIKE N’Kollar’ in place of LIKE N’Davolio’, and a third time with LIKE N'D%:" It should read: "For example, run the following code three times: once as shown, a second time with LIKE N’Kollar’ in place of LIKE N’Davolio’, and a third time with LIKE N'D%':"

Microsoft Press  May 06, 2010 
Printed
Page 203

Variable b referenced in place of variable c On page 203, the third sentence on the page reads: "The predicate b NOT IN(a, b, NULL) therefore returns NOT UNKNOWN, which equals UNKNOWN, and customer c is not returned by the query, either, even though c does not appear in the customer list." It should read: "The predicate c NOT IN(a, b, NULL) therefore returns NOT UNKNOWN, which equals UNKNOWN, and customer c is not returned by the query, either, even though c does not appear in the customer list."

Microsoft Press  Jul 13, 2010 
Printed
Page 203

"EXISTS" should be "NOT EXISTS" On page 203, the last sentence of the first paragraph contains an incorrect query. Change: "To make the NOT IN query logically equivalent to the EXISTS query, declare the column as NOT NULL (if appropriate) or add a filter to the subquery to exclude NULLs:" To: "To make the NOT IN query logically equivalent to the NOT EXISTS query, declare the column as NOT NULL (if appropriate) or add a filter to the subquery to exclude NULLs:"

Microsoft Press  May 06, 2010 
Printed
Page 206

"MIN(keycol) + 1" should be "MIN(keycol + 1)" On page 206, the third sentence in the second paragraph contains an incorrect expression. Change: "If 1 doesn’t exist in the table (that is, the second EXISTS predicate is always FALSE), the filter generates an empty set and the expression MIN(keycol) + 1 yields a NULL." To: "If 1 doesn’t exist in the table (that is, the second EXISTS predicate is always FALSE), the filter generates an empty set and the expression MIN(keycol + 1) yields a NULL."

Microsoft Press  Jul 13, 2010 
Printed
Page 272

"ranking functions" and "recursive queries" referred to rather than "PIVOT" and "UNPIVOT" On page 272, the first sentence of the Note at the bottom of the page reads: "Changing the compatibility mode of a database to an earlier version will prevent you from using the new language elements (for example, ranking functions, recursive queries, and so on)." It should read: "Changing the compatibility mode of a database to an earlier version will prevent you from using the new language elements (for example, PIVOT, UNPIVOT, and so on)."

Microsoft Press  May 06, 2010 
Printed
Page 328

"running" should be "sliding" On page 328, the first sentence after the Note box contains an incorrect aggregate. Change: "The main difference between the solution for cumulative aggregates and the solution for running aggregates is in the join condition (or in the subquery’s filter, in the case of the alternate solution using subqueries)." To: "The main difference between the solution for cumulative aggregates and the solution for sliding aggregates is in the join condition (or in the subquery’s filter, in the case of the alternate solution using subqueries)."

Microsoft Press  Jul 13, 2010 
Printed
Page 340

Yearly Quantities refered to rather than Yearly Orders in the caption for Table 6-16 On page 340, the caption for Table 6-16 reads: "Count of Yearly Quantities per Customer" It should read: "Count of Yearly Orders per Customer"

Microsoft Press  May 06, 2010 
Printed
Page 356

period missing from attribute On page 356, the fifth line in Step 15 contains an attribute that is missing a period. Change: "Native serialization requires that the StructLayoutAttribute be specified as StructLayout.LayoutKindSequential if the UDA is defined in a class and not a structure." To: "Native serialization requires that the StructLayoutAttribute be specified as StructLayout.LayoutKind.Sequential if the UDA is defined in a class and not a structure."

Microsoft Press  Jul 13, 2010 
Printed
Page 360

Query incorrect On page 360, the query at the top of the page is incorrect. Change: SELECT groupid, CAST(ROUND(EXP(SUM(LOG(ABS(NULLIF(val,0)))))* (1-SUM(1-SIGN(val))%4)*(1-SUM(1-SQUARE(SIGN(val)))),0) AS INT) AS product FROM dbo.Groups GROUP BY groupid;To:SELECT groupid, CAST(ROUND(EXP(SUM(LOG(ABS(NULLIF(val,0)))))* (1-SUM(1-SIGN(val))%4)*(1-MAX(1-SQUARE(SIGN(val)))),0) AS INT) AS product FROM dbo.Groups GROUP BY groupid;

Microsoft Press  May 06, 2010 
Printed
Page 364

Incorrect labels for the second and third columns of Table 6-24 On page 364, the column labels for Table 6-24: "groupid agg_or agg_or_binval" They should read: "groupid agg_and agg_and_binval"

Microsoft Press  Jul 13, 2010 
Printed
Page 364

Incorrect labels for the second and third columns of Table 6-25 On page 364, the column labels for Table 6-25 read: "groupid agg_or agg_or_binval" They should read: "groupid agg_xor agg_xor_binval"

Microsoft Press  May 06, 2010 
Printed
Page 412

"2147483648" should be "-2147483648" On page 412, the first sentence of the second to last paragraph contains an incorrect integer. Change: "CHECKSUM returns an integer between 2147483648 and 2147483647." To: "CHECKSUM returns an integer between -2147483648 and 2147483647."

Microsoft Press  Jul 13, 2010 
Printed
Page 431 and 432

#CustStage referred to rather than #CustsStage On page 431, the seventh and eigth sentences of the final paragraph read: "The UPDATE makes a single pass over the rows in #CustStage. With every row that the UPDATE visits, it stores the value of @key + 1 in KeyCol and in @key. This means that with every new row visited, @key is incremented by one and stored in KeyCol. You basically distribute the new block of sequence values among the rows in #CustStage." They should read: "The UPDATE makes a single pass over the rows in #CustsStage. With every row that the UPDATE visits, it stores the value of @key + 1 in KeyCol and in @key. This means that with every new row visited, @key is incremented by one and stored in KeyCol. You basically distribute the new block of sequence values among the rows in #CustsStage." On page 432, the first sentence on the page reads: "this code after resetting the sequence value to 0, as instructed earlier, #CustStage will contain seven UK customers, with KeyCol values ranging from 1 through 7." It should read: "this code after resetting the sequence value to 0, as instructed earlier, #CustsStage will contain seven UK customers, with KeyCol values ranging from 1 through 7." On page 432, the first sentence of the second paragraph reads: "The specialized T-SQL UPDATE statement is not standard and doesn’t guarantee it will access the rows in #CustStage in any particular order." It should read: "The specialized T-SQL UPDATE statement is not standard and doesn’t guarantee it will access the rows in #CustsStage in any particular order."

Microsoft Press  May 06, 2010 
Printed
Page 434

usp_AsyncSec refered to rather than usp_AsyncSeq On page 434, the second sentence after the Note box reads: "The rollback prevents the AsyncSeq table from growing. In fact, it will never contain any rows from calls to usp_AsyncSec." It should read: "The rollback prevents the AsyncSeq table from growing. In fact, it will never contain any rows from calls to usp_AsyncSeq."

Microsoft Press  Jul 13, 2010 
Printed
Page 445

"many" should be "one" On page 445. the second sentence of the last paragraph contains an incorrect side reference. Change: "When you modify the table on the “many” side of a one-to-many join, you might end up with a nondeterministic update."To: "When you modify the table on the “one” side of a one-to-many join, you might end up with a nondeterministic update."

Microsoft Press  Jul 13, 2010 
Printed
Page 445

Incorrect SET statement in the first code sample On page 445, the last two lines of the first code sample reads: WHERE C.CustomerID = dbo.Orders.CustomerID); WHERE C.Country = 'USA';They should read: WHERE C.CustomerID = dbo.Orders.CustomerID); WHERE CustomerID IN (SELECT CustomerID FROM dbo.Customers WHERE Country = 'USA');

Microsoft Press  May 06, 2010 
Printed
Page 480

fn_subordinates2 referenced in place of fn_subordinates1 On page 480, the last sentence before Listing 9-8 reads: "Run the code in Listing 9-8 to create the fn_subordinates2 function, which is a revision of fn_subordinates2 that also supports a level limit." It should read: "Run the code in Listing 9-8 to create the fn_subordinates2 function, which is a revision of fn_subordinates1 that also supports a level limit."

Microsoft Press  May 06, 2010 
Printed
Page 485

On page 485, in the code sample, the comment on line 12 contains an incorrect symbol. Change: -- and previous level To: -- and previous level

Microsoft Press  Jul 13, 2010 
Printed
Page 493

"SubsPath" should be "SubsSort" On page 496, line 36 of the sample code on the page needs to have "SubsPath" replaced with "SubsSort". Change: -- #SubsPath is a temp table that will hold the finalTo: -- #SubsSort is a temp table that will hold the final

Microsoft Press  May 06, 2010 
Printed
Page 494

@ should be # On page 494, in the code sample, the 4th comment above the last INSERT statement contains an incorrect symbol. Change: -- Load the rows from #SubsPath to @SubsSort sorted by the binaryTo: -- Load the rows from #SubsPath to #SubsSort sorted by the binary

Microsoft Press  Jul 13, 2010 
Printed
Page 506

Incorrect use of apostrophe On page 506, the fourth sentence of the second to the last paragraph on the page incorrect places an apostrophe before the word employee. Change: "Otherwise, its level is the parent’s level plus 1, and its path is: parent path + ‘employee id + ‘.’." To: "Otherwise, its level is the parent’s level plus 1, and its path is: parent path + employee id + ‘.’."

Microsoft Press  May 06, 2010 
Printed
Page 513

Reference to Table 9-35 should be more specific On page 513, the first two full sentences on the page read: "For each request, I’ll provide a sample query followed by its output (shown in Table 9-35). Return the subtree with a given root:" It should read: "For each request, I’ll provide a sample query followed by its output. Return the subtree with a given root, generating the output shown in Table 9-35:"

Microsoft Press  Jul 13, 2010 
Printed
Page 533

"BOMCTE" should be "BOMTC" On page 533, the first sentence after Table 9-50 includes an incorrect acronym. Change: "This solution eliminates duplicate edges found in the BOMCTE by applying a DISTINCT clause in the outer query." To: "This solution eliminates duplicate edges found in the BOMTC by applying a DISTINCT clause in the outer query."

Microsoft Press  May 06, 2010 
Printed
Page 562

Lamp 36 missing from the list of lamps that are on On page 562, the first sentence of the answer to Puzzle 10 reads: "All the lamps are off except for lamps number 1, 4, 9, 16, 25, 49, 64, 81, and 100, which are on." It should read: "All the lamps are off except for lamps number 1, 4, 9, 16, 25, 36, 49, 64, 81, and 100, which are on."

Microsoft Press  Jul 13, 2010 
Printed
Page 567

Corretion to Puzzle 19 answer On page 567, under "Puzzle 19: Find the pattern in the Sequence", in last sentence, the first number in the sequence - 4 - was added by mistake. Change: "Here’s the first part of the sequence with a few additional numbers: 4, 3, 3, 5, 4, 4, 3, 5, 5, 4, 3, 6, 6, 8, 8, 7, 7, 9, 8, 8, 6, 9, 9, 11, 10, 10, 9, 11, 11, 10, …" To: "Here’s the first part of the sequence with a few additional numbers: 3, 3, 5, 4, 4, 3, 5, 5, 4, 3, 6, 6, 8, 8, 7, 7, 9, 8, 8, 6, 9, 9, 11, 10, 10, 9, 11, 11, 10, …" 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