Errata

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.

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

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

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

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

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

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

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

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"

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

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

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

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

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

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

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"

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

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

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

On page 485, in the code sample, the comment on line 12 contains an incorrect symbol.



Change:



-- and previous level To:



-- and previous level

@ 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

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

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

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

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

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

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"

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

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

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

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

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

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"

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%':"

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

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

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"

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;

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"

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

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');

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

"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

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 + ‘.’."

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

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.