Article ID: 920141
Article Last Modified on 10/24/2007
APPLIES TO
- Inside Microsoft SQL Server 2005: T-SQL Querying, ISBN 0-7356-2313-9
SUMMARY
This article contains comments, corrections, and information about known errors relating to the Microsoft Press book Inside Microsoft SQL Server 2005: T-SQL Querying, 0-7356-2313-9.
The following topics are covered:
- Page 8: Correction to table keys
- Page 20: <table_expression> tag used in place of <right_table_expression> tag
- Page 43: Yes missing from Figure 2-7
- Page 51: Link in MoreInfo box is incorrect
- Page 68: THEN repeated in code sample
- Page 73: "signal_wait_time" should be "signal_wait_time_ms"
- Page 81: Missing spaces in Table 3-3
- Page 101: "1000" used in place of "1000000"
- Pages 102, 103: "1000" used in place of "1000000"
- Page 137: Ordered scan referenced rather than unordered scan
- Page 139: "not used" should be "used not"
- Page 140: The second orderid should be 120 in the sixth sentence of the paragraph following the query
- Page 141: Index Scan referenced in place of Index Seek
- Page 158: The term "grows larger" is used instead of the term "gets lower"
- Page 162: The third line from the top needs UNIQUE removed and orderdate replacing orderid
- Pages 163-166: Unordered Nonclustered Index Scan referred to as Unordered Covering Nonclustered Index Scan
- Page 168: "scan fragmentation" should be "page density"
- Page 173: Customer information referenced rather than session information
- Page 173: Listing 3-7 table name should be BigSessions instead of sessions
- Page 187: "yes" should be "no"
- Page 192: Correction in fourth paragraph subquery example instructions
- Page 203: Variable b referenced in place of variable c
- Page 203: "EXISTS" should be "NOT EXISTS"
- Page 206: "MIN(keycol) + 1" should be "MIN(keycol + 1)"
- Page 272: "ranking functions" and "recursive queries" referred to rather than "PIVOT" and "UNPIVOT"
- Page 328: "running" should be "sliding"
- Page 340: Yearly Quantities refered to rather than Yearly Orders in the caption for Table 6-16
- Page 356: period missing from attribute
- Page 364: Incorrect labels for the second and third columns of Table 6-24
- Page 364: Incorrect labels for the second and third columns of Table 6-25
- Page 412: "2147483648" should be "-2147483648"
- Pages 431 and 432: #CustStage referred to rather than #CustsStage
- Page 434: usp_AsyncSec refered to rather than usp_AsyncSeq
- Page 445: Incorrect SET statement in the first code sample
- Page 445: "many" should be "one"
- Page 480: fn_subordinates2 referenced in place of fn_subordinates1
- Page 485: < should be <=
- Page 493: "SubsPath" should be "SubsSort"
- Page 494: @ should be #
- Page 506: Incorrect use of apostrophe
- Page 513: Reference to Table 9-35 should be more specific
- Page 533: "BOMCTE" should be "BOMTC"
- Page 562: Lamp 36 missing from the list of lamps that are on
- Page 567: Corretion to Puzzle 19 answer
MORE INFORMATION
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.”
Page 20: <table_expression> tag used in place of <right_table_expression> tag
On page 20, the second line of the second table operator reads:
{CROSS | OUTER} APPLY <table_expression>
It should read:
{CROSS | OUTER} APPLY <right_table_expression>
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".
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.%22
To:
"A good example of extracting data from the XML showplan can be found at http://msdn2.microsoft.com/en-us/library/ms345130.aspx.%22
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 orderdate
It should read:
- CASE WHEN n % 10 = 0
THEN 1 + ABS(CHECKSUM(NEWID())) % 30
ELSE 0
END AS orderdate
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."
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"
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.
Pages 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.
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."
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."
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)."
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."
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."
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);"
Pages 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"
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."
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."
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"
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."
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%':"
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."
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:"
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."
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)."
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)."
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"
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."
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"
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"
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."
Pages 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."
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."
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');
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."
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."
Page 485: < should be <=
On page 485, in the code sample, the comment on line 12 contains an incorrect symbol.
Change:
-- and previous level < @maxlevels
To:
-- and previous level <= @maxlevels
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 final
To:
-- #SubsSort is a temp table that will hold the final
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 binary
To:
-- Load the rows from #SubsPath to #SubsSort sorted by the binary
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 + ‘.’."
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:"
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."
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."
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.
Additional query words: 0-7356-2313-9 978-0-7356-2313-2
Keywords: KB920141
