Microsoft® SQL Server® 2008 Internals

Errata for Microsoft® SQL Server® 2008 Internals




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 30
Last paragraph

This is probably just a typo, but it is a technical error when the wrong word is used. I wrote: Every buffer in the data cache has a header that contains information about the last two times the page was referenced and some status information, including whether the page is dirty (that is, it has been changed since it was read into disk). This should be: Every buffer in the data cache has a header that contains information about the last two times the page was referenced and some status information, including whether the page is dirty (that is, it has been changed since it was read into MEMORY.)

Note from the Author or Editor:
Error corrected. Will be fixed in next printing.

Kalen Delaney
O'Reilly Author 
Jan 06, 2011  Mar 30, 2012
Printed
Page 49
second table, second row, fifth column

Calculated shared % for default pool reads 30. I believe it should read 25.

Note from the Author or Editor:
In Table 1-3, second row, 5th column: the calculated shared % for the default pool should be 25 (instead of current value of 30) Error corrected. Will be fixed in next printing.

Kurt Sharp  Nov 29, 2010  Mar 30, 2012
Printed
Page 53
Last bullet

For the bullet describing sys.dm_os_memory_brokers, allocations_db_per_sec should be allocations_kb_per_sec.

Note from the Author or Editor:
Error corrected. Will be fixed in next printing.

Kalen Delaney
O'Reilly Author 
Jan 06, 2011  Mar 30, 2012
Printed
Page 56
Second to last paragraph

Confusing wording of the two sentences: "If the SQL Server Browser service is not running... However, if the SQL Server browser service is not running..."

Note from the Author or Editor:
Change this paragraph (above the bulleted list): If the SQL Server Browser service is not running on a computer, you cannot connect to SQL Server on that machine unless you provide the correct port number. However, if the SQL Server Browser service is not running, the following connections will not work: To this: If the SQL Server Browser service is not running on a computer, you cannot connect to SQL Server on that machine unless you provide the correct port number. Additionally, if the SQL Server Browser service is not running, the following connections will not work: Error corrected. Will be fixed in next printing.

Daniel Fortunov  Oct 16, 2010  Mar 30, 2012
Printed, PDF
Page 56
1st paragraph (second line of page)

Other services might include the SQL Server Full-Text Search service and SQL Server Integration Services (SSIS). SQL Server 2008 introduces a new Full-Text Engine that is integrated into the relational database. Full-Text Search service is not present/does not exist in 2008.

Note from the Author or Editor:
Change this sentence: Other services might include the SQL Server Full-Text Search service and SQL Server Integration Services (SSIS). To this: Other services might include the SQL Server Reporting Services and SQL Server Integration Services (SSIS).

Anonymous  Feb 21, 2011  Mar 30, 2012
PDF
Page 56
SQL Browser section, 3rd paragraph, listings

Original: Using the DAC to connect to a named instance or the default instance if it us not using TCP/IP port 1433 it could be: Using the DAC to connect to a named instance or to the default instance if the DAC for the default instance is not using (the default) TCP/IP port 1434. Note: DAC for the default sql instance is listening "by default" on TCP/IP 1434. However, DAC is dynamically assigned and if for any reason DAC (for the default sql instance) is not listening on TCP/IP 1434 then sql browser is required for connections that do not explicitly define the DAC port. For example, Option 1 default sql instance, DAC is listening on TCP 11542: sqlcmd -S myserver, 11542 (-A switch and sql browser are not required) sqlcmd -S myserver -A (sql browser needs to be up and running or an error is raised) Option 2 (default behavior) default sql instance, DAC is listening on the default TCP 1434 sqlcmd -S myserver, 1434 (-A switch and sql browser are not required) sqlcmd -S myserver -A (sql browser is not required) For the two aforementioned options, whether the default sql instance is listeing on TCP 1433 or another port makes no difference. There is still one exception though. If the default sql instance is configured to listen on TCP 1434 (the port that is supposed to be used by the DAC) then things can be a bit confusing : Weird option: default sql instance is using TCP 1434 DAC is using another port (since it cannot use TCP 1434) assuming that the default sql instance is not having any problem, then the following sqlcmd -S myserver -A does NOT use DAC (although the -A switch is used)!!!! It simply uses a TCP connection. The last sentence of page 53 is: It is recommended that the Browser Service be set to start automatically on any machine on which SQL Server will be accessed using a network connection. Somehow, this is against the best practices. If there isnt a named instance installed then sql browser doesnt offer that much and for security reasons it should be turned off. * SQL Browser returns information for the default instance, but microsoft providers do not prompt/ask the sql browser for the port number when a connection to the default instance is attempted. * SQL Browser is not required for connecting to the DAC of the default sql instance as long as DAC is listening on the default TCP 1434 or the DAC port is known.

Note from the Author or Editor:
This error includes a minor technical mistake and a 'typo'. This sentence: Using the DAC to connect to a named instance or the default instance if it us not using TCP/IP port 1433 Should be: Using the DAC to connect to a named instance or the default instance if the DAC for the default instance is not using (the default) TCP/IP port 1434

Anonymous  Feb 21, 2011  Mar 30, 2012
Safari Books Online
78
Middle of page

"The CSN is then inserted—along with the transaction identifier, log sequence information, begin time, and other data..." 'begin time' should be 'commit time'

Note from the Author or Editor:
Change "begin time" to "commit time" Error corrected. Will be fixed in next printing.

Daniel Fortunov  Oct 17, 2010  Mar 30, 2012
PDF
Page 116
first paragraph, 4th line, 2nd sentence

Original: Of the 7 public targets, 3 are marked synchronous in the capabilities_desc column. These targets collect event data synchronously—much like actions—before control is returned to the code that caused the event to fire. The other **five** events, in comparison, are asynchronous, meaning ... Note: 7 public targets, 3 are synchronous, 4 are asynchronous It should be: The other four events, in comparison, are asynchronous, meaning....

Note from the Author or Editor:
This sentence: These targets collect event data synchronously—much like actions—before control is returned to the code that caused the event to fire. The other five events, in comparison, are asynchronous, meaning ... Should be: These targets collect event data synchronously—much like actions—before control is returned to the code that caused the event to fire. The other four events, in comparison, are asynchronous, meaning ...

Anonymous  Feb 25, 2011  Mar 30, 2012
Printed
Page 127
Third-to-last paragraph

This paragraph talks about dates but then gives an example which is a version number, not a date: "The created and modified date for both of these files is the date that the code for the current build was frozen. It should be the same date that you see when you run SELECT @@version. For SQL Server 2008, the RTM build, this is 10.0.1600.22."

Note from the Author or Editor:
Update this sentence for Service Pack 1: "The created and modified date for both of these files is the date that the code for the current build was frozen. It should be the same date that you see when you run SELECT @@version. For SQL Server 2008, Service Pack 1, this is Mar 29 2009" Error corrected. Will be fixed in next printing.

Daniel Fortunov  Oct 17, 2010  Mar 30, 2012
Printed, Safari Books Online
Page 143

The first bullet on this page "Add a new filegroup to a database." also contains "Modify an existing file in one of the following ways: ..." and the second bullet "Mark the file as OFFLINE." also contains "Modify an existing filegroup in one of the following ways: ..." These should be separate points in the list.

Note from the Author or Editor:
The first dark bullet on the page needs to be split into 2: ■ Add a new filegroup to a database. (Adding files to those filegroups must be done in a separate ALTER DATABASE command.) ■ Modify an existing file in one of the following ways: Also the second dark bullet needs to be split into 2: ■ Mark the file as OFFLINE. You should set a file to OFFLINE when the physical file has become corrupted and the file backup is available to use for restoring. (There is also an option to mark the whole database as OFFLINE, which I'll discuss shortly when I talk about database properties.) Marking a file as OFFLINE allows you to indicate that you don’t want SQL Server to recover that particular file when it is restarted. ■ Modify an existing filegroup in one of the following ways: Error corrected. Will be fixed in next printing.

Daniel Fortunov  Oct 17, 2010  Mar 30, 2012
Printed
Page 153
Middle of page

"RECOVERY_ PENDING" should be "RECOVERY_PENDING"

Note from the Author or Editor:
There is an extra space after the underscore in RECOVERY_ PENDING in the middle of the page. Remove the space. Error corrected. Will be fixed in next printing.

Daniel Fortunov  Oct 19, 2010  Mar 30, 2012
Printed
Page 158
Last bullet

TORN_PAGE_DETCTION should be TORN_PAGE_DETECTION

Note from the Author or Editor:
Error corrected. Will be fixed in next printing.

Kalen Delaney
O'Reilly Author 
Jan 06, 2011  Mar 30, 2012
Printed
Page 159

Paragraph 1: "suspect_ pages" should be "suspect_pages" and in italics. Paragraph 4: "TORN_PAGE_ DECECTION" should be "TORN_PAGE_DECECTION"

Note from the Author or Editor:
Paragraph 1, suspect _pages in the last line seems to have an extra space BEFORE the underscore which needs to be removed. Paragraph 4 The first mention of TORN_PAGE_DETECTION seems to have an extra space AFTER the second underscore which needs to be removed Error corrected. Will be fixed in next printing.

Daniel Fortunov  Oct 19, 2010  Mar 30, 2012
Printed
Page 166
Last paragraph

I wrote: Because tempdb is used for many internal operations in SQL Server 2008 than in previous versions... And it should have been: Because tempdb is used for many more internal operations in SQL Server 2008 than in previous versions...

Note from the Author or Editor:
Error corrected. Will be fixed in next printing.

Kalen Delaney
O'Reilly Author 
Jan 06, 2011  Mar 30, 2012
Printed
Page 173
Right after "Principals and Schemas"

I wrote: Prior to SQL Server 2005, there was a CREATE SCHEMA command, but it effectively did nothing because there was an implicit relationship between users and schemas that could be changed or removed. Technically, this is a typo, but leaving out the word NOT changes the meaning to make it a technical error also. I should have written: Prior to SQL Server 2005, there was a CREATE SCHEMA command, but it effectively did nothing because there was an implicit relationship between users and schemas that could _NOT_ be changed or removed.

Note from the Author or Editor:
Error corrected. Will be fixed in next printing.

Kalen Delaney
O'Reilly Author 
Jan 06, 2011  Mar 30, 2012
Printed, PDF
Page 173
1st paragraph, 3rd line after the listing

original: The login name sa has the user name dbo. This name is a *special login* that is used by the sa login, by all logins in the sysadmin role, and by whatever login is listed in sys.databases as the owner of the database. It could be: The dbo is a special database principal that is mapped to the login owning the database and it is also used by all logins in the sysadmin server role.

Note from the Author or Editor:
This sentence: The login name sa has the user name dbo. This name is a *special login* that is used by the sa login, by all logins in the sysadmin role, and by whatever login is listed in sys.databases as the owner of the database. Should be: The dbo is a special database principal that is mapped to the login owning the database and it is also used by all logins in the sysadmin server role.

Anonymous  Feb 28, 2011  Mar 30, 2012
Printed
Page 175
Detaching and Reattaching a Database

The description of detaching a database from pages 175-177 states "Detaching a database ensures that no incomplete transactions are in the database and that there are no dirty pages for this database in memory. If these conditions cannot be met, the detach operation fails ... one benefit of using the sp_detach_db procedure is that SQL Server records the fact that the database was shut down cleanly ... This can be a quick way to shrink a log file that has become much larger than you would like". However detaching a database does not in fact ensure this. The blog post here (http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/) provides a demonstration of a case where the database is detached despite containing open transactions (though it is true that both the SET SINGLE_USER and sp_detach_db statements raise an error the database is successfully detached)

Note from the Author or Editor:
While the reader's comments have some merit, any fix would be too complex for a simple update. This may be addressed in the next edition of the book.

Martin Smith  Feb 24, 2012 
Printed, PDF
Page 176
last paragraph

original: If your transaction log is rebuilt by attaching the database, using the FOR ATTACH_REBUILD_LOG breaks the log backup chain. You should consider making a full backup after performing this operation. A clarification is required: When the transaction log is rebuilt by attaching a database (FOR ATTACH OR ATTACH_REBUILD_LOG), the recovery model of the database is switched/changed to SIMPLE (side effect of rebuilding the log). A reference to log backup chain is unarguable in this case. A proper action, after the database is attached, would be a switch/change to the proper recovery model and perform a full database backup if the recovery model of the database was switched to FULL or BULK_LOGGED (the full backup will create a new log chain in this case).

Note from the Author or Editor:
These sentences: If your transaction log is rebuilt by attaching the database, using the FOR ATTACH_REBUILD_LOG breaks the log backup chain. You should consider making a full backup after performing this operation. Should be: If your transaction log is rebuilt by attaching the database, using the FOR ATTACH_REBUILD_LOG puts the database to SIMPLE recovery. If the database was originally in FULL or BULK_LOGGED recovery, it is recommended that you switch back to that original recovery model, and make a full backup after performing the ATTACH operation.

Anonymous  Feb 28, 2011  Mar 30, 2012
Printed
Page 179

It bullet point 2, "-eE:\ SQLData\Log\ERRORLOG" has a spurious space; should read: "-eE:\SQLData\Log\ERRORLOG" In bullet point 3, "physically move the files for to the new location" should read: "physically move the files to the new location"

Note from the Author or Editor:
Error corrected. Will be fixed in next printing.

Daniel Fortunov  Oct 23, 2010  Mar 30, 2012
Printed, PDF
Page 186
1st paragraph, 5th line

original: A transaction log can*not* be truncated prior to the point of the earliest transaction that is still open it could be: A transaction log can be truncated prior to the point of the earliest transaction that is still open or A transaction log cannot be truncated past the point of the earliest transaction that is still open

Note from the Author or Editor:
This sentence: A transaction log cannot be truncated prior to the point of the earliest transaction that is still open, Should be: A transaction log cannot be truncated past the point of the earliest transaction that is still open,

Anonymous  Mar 01, 2011  Mar 30, 2012
Printed, PDF
Page 187
last paragraph, listing of Active state for VLFs

original: (Unused space in the physical log is not part of any VLF.) it could be: the sentence in parenthesis is confusing and should be removed. The image/Figure 4.3 (page 188) should display VLF#5 over unused space). The sentence in parenthesis contradicts with the description of the Unused state of VLF {=One or more VLFs at the physical end of the log files might not have been used yet}. when a new database is created with a log file of 1 GB, there are 8 vlfs reported by DBCC LOGINFO, and the total/sum size of the vlfs is 1 GB. The log file is empty, unused, but still part of vlfs.

Note from the Author or Editor:
Page 187, remove the parenthetical statement: (Unused space in the physical log is not part of any VLF.) On page 188, Figure 4.3, the label VLF #5 should appear over the box marked "unused space"

Anonymous  Mar 01, 2011  Mar 30, 2012
Printed
Page 188
next to last line on page

FSeqNo value of 40 should be changed to 48, and it will be the highest value in the file.

Note from the Author or Editor:
Error corrected. Will be fixed in next printing.

Kalen Delaney
O'Reilly Author 
Jan 06, 2011  Mar 30, 2012
Printed
Page 188
Last paragraph

I wrote that the undocumented command DBCC LOGINFO takes no parameters. The fact is that it doesn't need a parameter, in which case it reports the VLFs for the current database, but you can specify a database name or database ID in parentheses. The following all return the same output: USE master; DBCC LOGINFO; DBCC LOGINFO(master); DBCC LOGINFO(1);

Kalen Delaney
O'Reilly Author 
Jan 06, 2011 
Printed, PDF
Page 188
2nd listing, desciption of Unused VLF

original: Unused One or more VLFs at the physical end of the log files might not have been used yet if not enough logged activity has taken place *or if earlier VLFs have been marked as reusable and then reused.* it could be: Unused One or more VLFs at the physical end of the log files might not have been used yet if not enough logged activity has taken place. If Reusable and Unused are mutually excluded concepts, then reusable is a VLF that has been used before and can be used again, unused is a VLF that hasnt been used yet. Based on observations, unused VLFs are "preferred" over reusable ones: if an unused VLF exists, it will be used (no matter if there are reusable available). Thus, unused VLFs exist only when not enough activity has taken place that extends/covers the "length", fills the size, of the physical log file. Plain example : VLF1 FSeqNo=40 Status=0 --reusable VLF2 FSeqNo=41 Status=0 --reusable VLF3 FSeqNo=42 Status=2 --active VLF4 FSeqNo=43 Status=2 --active VLF5 FSeqNo=0 Status=0 --unused VLF6 FSeqNo=0 Status=0 --unused When the end of VLF4 is reached, new log records will NOT wrap around to the start of the physical log file, even though VLF1 is reusable. New log records are written to the unused VLFs till the end of the logical log file is reached.

Note from the Author or Editor:
This sentence: Unused One or more VLFs at the physical end of the log files might not have been used yet if not enough logged activity has taken place or if earlier VLFs have been marked as reusable and then reused. To this: Unused One or more VLFs at the physical end of the log files might not have been used yet if not enough logged activity has taken place.

Anonymous  Mar 01, 2011  Mar 30, 2012
Printed
Page 197-202

The terms "recovery mode" and "recovery model" are used throughout this section. Are these interchangeable? Should one term be used consistently? The index also contains separate entries for these.

Note from the Author or Editor:
"recovery mode" should be changed to "recovery model" wherever it occurs in the book Error corrected. Will be fixed in next printing.

Daniel Fortunov  Oct 24, 2010  Mar 30, 2012
Printed
Page 199
1st Paragraph

"When you restore from a transaction log backup that includes index creations, the recovery operation is much faster because the index does not have to be rebuilt -- all the index pages have been captured as part of the database backup." Should this be "all the index pages have been captured as part of the *log* backup."?

Note from the Author or Editor:
Change "database backup" to "log backup" so the sentence reads: "-- all the index pages have been captured as part of the log backup" Error corrected. Will be fixed in next printing.

Daniel Fortunov  Oct 24, 2010  Mar 30, 2012
Printed, PDF
Page 221
last paragraph of Date and Time Data Types (before Character Data Types)

original: The ISO 8601 format is an international standard with unambiguous specifi cation. In addition this format is not affected by your session’s SET DATEFORMAT or SET LANGUAGE settings. Using this format, March 4, 1948, could be represented as 19480304 or 1948-03-04. Clarification: 19480304 or 1948-03-04 return different dates for the types datetime&smalldatetime based on the DATEFORMAT setting: SET DATEFORMAT ydm SELECT CAST('19480304' AS DATETIME), CAST('19480304' AS SMALLDATETIME) --> March 4 SELECT CAST('1948-03-04' AS DATETIME), CAST('1948-03-04' AS SMALLDATETIME) --> April 3 For the datetime2 (and date & datetimeoffset) datatype, the dateformat ydm is not supported: SET DATEFORMAT ydm SELECT CAST('19480304' AS DATETIME2), CAST('1948-03-04' AS DATETIME2) --> March 4 Thus, better use yyymmdd {not yyyy-mm-dd} when defining dates.

Note from the Author or Editor:
Remove this sentence: In addition this format is not affected by your session’s SET DATEFORMAT or SET LANGUAGE settings. After this sentence: Using this format, March 4, 1948, could be represented as 19480304 or 1948-03-04. Add this: However, since SQL Server's interpretation of 1948-03-04 can vary based on your SET DATEFORMAT settings, it is recommended that you use 19480304, or in general format: yyyymmdd.

Anonymous  Mar 02, 2011  Mar 30, 2012
Printed
Page 266
Chapter 5

Formatting of figure 5-10 is broken. Looking at the next figure, 5-11, it looks like the font within the figure should be fixed-width.

Note from the Author or Editor:
The contents inside the grey boxes is completely mis-formatted. Compare to figure 5-11. O'Reilly should contact author directly to get original copy of this figure.

Daniel Fortunov  Nov 21, 2010  Mar 30, 2012
Printed
Page 271
First large paragraph after first DBCC PAGE output

It states the following: "There are nine entries in the column offset array with the value (after byte-swapping) of hex 22, or decimal 18..." I believe the decimal equivalent of hex 22 is 34. Additionally, in the discussion of the 10th value of the variable column offset array, hex 23 is referred to as decimal 19, which I believe yields decimal 35 instead.

Note from the Author or Editor:
Yes, I did my conversions backwards in this paragraph. The first 9 entries are 22 hex or 34 decimal, the last entry is 23 hex or 35 decimal.

Riley Logan  Oct 20, 2011 
Printed
Page 274
Chapter 5

Spurious bullet point: The second bullet point should be removed as that sentence is the continuation of the first point.

Note from the Author or Editor:
The second bullet needs to be removed and the sentence should just be another paragraph for the first bullet, following the code example. Error corrected. Will be fixed in next printing.

Daniel Fortunov  Nov 21, 2010  Mar 30, 2012
Printed
Page 289
Chapter 5

The sentence ending with "which contain one bit per extent.)" contains a spurious ')'

Note from the Author or Editor:
Remove the ) after "extent." Error corrected. Will be fixed in next printing.

Daniel Fortunov  Nov 21, 2010  Mar 30, 2012
Printed
Page 304
Chapter 6

"The sys.dm_db_index_physical_stats function is one of the most useful functions to determine table structures. DMV can give you insight into..." The second sentence should have the word "This" at the beginning. Also, other parts of the book are careful to point out that the term DMV (Dynamic Management View) technically only covers a subset of the DMOs, some of which are functions. However, the heading and text in this section repeatedly refer to the dm_db_index_physical_stats function as a DMV.

Note from the Author or Editor:
The "This" should be added. Response to submitter for second point: The acronym DMV is used to stand for both Dynamic Management Views AND Functions. We do not use the term DMO as that means something else, but sometimes we spell out Dynamic Management Objects to refer to both Views and Functions. Error corrected. Will be fixed in next printing.

Daniel Fortunov  Nov 21, 2010  Mar 30, 2012
Printed, PDF
Page 321
1st paragraph, 4th line

original: *The index key part of the entry "always" indicates the minimum value that could be on the pointed-to page*. Note that they do not necessarily indicate the actual lowest value, just the lowest possible value for the page (as when the row with the lowest key value on a page is deleted, the index row in the level above is not updated). clarification required (assuming a single column clustered key) The index key part indicates the minimum value on the pointed-to page when the clustered index key is sorted in ASCending order. If the clustered index key is sorted in DESCending order, then the index key part indicates the maximum value on the pointed-to page. Since it is a clustered index, it is logical to assume that the index key part of a row in a non-leaf level holds the value of the first record (offset array position 0x0) on the pointed-to page at the time the clustered index was created. If the clustered key is ASCending, then the page record at position 0x0 (offset array) holds the minimum value of the key on the page. If the clustered key is DESCending sorted then the page record at position 0x0 (offset array) holds the maximum value of the key on the page.

Note from the Author or Editor:
Change this: The index key part of the entry always indicates the minimum value that could be on the pointed-to page. Note that they do not necessarily indicate the actual lowest value, just the lowest possible value for the page (as when the row with the lowest key value on a page is deleted, the index row in the level above is not updated). To this: The index key part of the entry indicates the first value that could be on the pointed-to page. If the index was built in ASC sequence, the first value will be the minimum value; if the index was built in DESC sequence, the first value will be the maximum value. Note that they do not necessarily indicate the actual first value, just the first possible value for the page (as when the row with the lowest or highest key value on a page is deleted, the index row in the level above is not updated).

Anonymous  Mar 04, 2011  Mar 30, 2012
Printed, PDF
Page 340
2nd paragraph of page, 1st paragraph of Indexes on Computed Columns, 5th line

original: Such a computed column can be an index key, included column, or part of a PRIMARY KEY or UNIQUE constraint. You cannot define a FOREIGN KEY, CHECK, or DEFAULT constraint on a computed column, and computed columns are always considered nullable unless you enclose the expression in the ISNULL function. clarification required: a foreign key or check constraint can be defined on a persisted computed column. Computed columns are not always null and persisted columns can be declared as NOT NULL. CREATE TABLE dbo.test_table ( id smallint not null, compcol1 AS (id), --> computed column not null compcol2 AS (CAST(id AS SMALLINT) + 10) PERSISTED NOT NULL -->also not null ) GO SELECT COLUMNPROPERTY(OBJECT_ID('dbo.test_table'), N'compcol1', 'AllowsNull' ), COLUMNPROPERTY(OBJECT_ID('dbo.test_table'), N'compcol2', 'AllowsNull' ) GO DROP TABLE dbo.test_table GO

Note from the Author or Editor:
Take out the two sentences given in the error report after 'original'.

Anonymous  Mar 05, 2011  Mar 30, 2012
Printed
Page 412
Third part of the table

The section of the table titled "Data-Dependent–Length Data Types" lists the storage requirements in reverse order. It should be 2+ when not sparse, and 4+ when sparse.

Note from the Author or Editor:
Yes, this is incorrect in the last section of table 7-7. When variable length column are not sparse, the need 2 extra bytes per column, but when they are sparse and contain a value (i.e. NOT NULL) then they need MORE space... 4 extra bytes.

Vlad Ignatov  Oct 25, 2011 
Printed
Page 418
2nd bullet

text says '8 entries of 0xa, indicating long data', should be '9 entries of 0xa....'

Note from the Author or Editor:
Reader's suggestion is correct. It's 9 entries of 0xa.

Anonymous  Aug 27, 2012 
Printed
Page 418
3rd bullet

not sure how to really make sense out of the cluster calculation (Number of columns -1)/30. Seems like this would end up with some rounding considerations that you may need to explain.

Note from the Author or Editor:
As with many of the other calcuations for row storage, if the value is a fraction, it will be rounded up. So (Number of columns -1)/30 should be CEILING(Number of columns -1)/30

Anonymous  Aug 27, 2012 
Printed
Page 422
3rd sub-bullet under 4th bullet

"The MaritalStatus is 1 byte, with the value of 0x0053, or 'S'" should be "The MaritalStatus is 2 bytes, with the value of 0x0053, or 'S'"

Note from the Author or Editor:
Yes, reader's suggested correction is right. Since marital status is Unicode, even a single character in 2008 needs 2 bytes. In SQL Server 2012, Unicode data can be compressed so it woud only need a single byte.

Anonymous  Aug 27, 2012 
Printed
Page 430
3rd paragraph

When talking about page compression's data dictionary, it says: "In general, SQL Server tries to keep no more than 300 entries in the dictionary". The actual upper limit is 255, so that SQL Server can use a single byte for the dictionary index entry.

Note from the Author or Editor:
Error corrected. Will be fixed in next printing.

Kalen Delaney
O'Reilly Author 
Jan 06, 2011  Mar 30, 2012
Printed
Page 437
Chapter 7

The final paragraph references Figure 7-15 where Figure 7-16 was intended.

Note from the Author or Editor:
In the last paragraph on page 437, change the reference to Figure 7-15 to reference 7-16. Error corrected. Will be fixed in next printing.

Daniel Fortunov  Nov 21, 2010  Mar 30, 2012
Printed
Page 469
Chapter 8

Paragraph 4 begins: "In additional to...". This should either be: "In addition to..." or "Additional to..."

Note from the Author or Editor:
Error corrected. Will be fixed in next printing.

Daniel Fortunov  Nov 21, 2010  Mar 30, 2012
Printed
Page 477
Chapter 8

"The basic idea behind index matching is to take predicates from a WHERE clause, join condition, or other limiting operation in a query and to convert that operation that can be performed against an index." This should be: "...and to convert that *to an* operation that can be performed against an index."

Note from the Author or Editor:
exactly as submitter reports it. Insert "to an" between "that" and "operation" Error corrected. Will be fixed in next printing.

Daniel Fortunov  Nov 21, 2010  Mar 30, 2012
PDF
Page 506
1st paragraph, 3rd line

original: that the row (Resource 1:69641:1) was locked with an X lock. the image 8-72 shows the resource 1:75676:1

Anonymous  Mar 09, 2011  Mar 30, 2012
Printed
Page 520
2nd figure

In figure 8-79, I cannot see the usage of index i2 like explained. It looks like figure 8-78.

Note from the Author or Editor:
Reader is correct, the figure is not the right one. The fix is complex and the section has been reworked for the next edition of the book.

Anonymous  Jul 23, 2012 
Printed
Page 534
Tip

It is said that a plan stub uses about 200 bytes. On page 530, last paragraph, and on page 563, last paragraph, it is 300 bytes.

Note from the Author or Editor:
The tip on page 534 has a typo. 200 bytes should be 300 bytes.

Anonymous  Jul 23, 2012 
Printed
Page 558
The paragraph after the table

It is said "...the OSQL interface uses the ODBC driver, which sets QUOTED_IDENTIFIER to OFF for every connection..." This is incorrect. The default when you connect with ODBC is that QUOTED_IDENTIFIER is ON. It's OSQL itself that actively turns off QUOTED_IDENTIFIER. (The same applies to SQLCMD, by the way.)

Note from the Author or Editor:
Thanks, Erland. You are correct. It is tool, either OSQL or SQLCMD that sets QUOTED_IDENTIFER to OFF.

Erland Sommarskog  Jul 09, 2011 
Printed
Page 566
Chapter 9

Near the end of the first paragraph (following the table): sys.dm_exec_ cached_plan_dependent_objects Contains an extra space and should read: sys.dm_exec_cached_plan_dependent_objects

Note from the Author or Editor:
Reader is correct, the name of the DMV as printed has an extra space. It should be: sys.dm_exec_cached_plan_dependent_objects Error corrected. Will be fixed in next printing.

Daniel Fortunov  Dec 07, 2010  Mar 30, 2012
Printed
Page 570
First paragraph

It is said: "Remember that SQL Server will cache the adhoc shell query that includes the actual parameter for each individual call to the stored procedure even though...." Shell queries for stored procedures? Earlier in the chapter, it is said that shell queries is created for the actual values when a adhoc statement is autoparameterised, so that the query text can be found again. There is little reason do this for stored procs. Run this repro from OSQL (the syntax is not accepted in SSMS): DBCC FREEPROCCACHE go CREATE TABLE onetable (a int NOT NULL PRIMARY KEY) go CREATE PROCEDURE insert_sp @a int AS INSERT onetable VALUES(@a) go {call insert_sp (9)} go {call insert_sp (19)} go {call insert_sp (99)} go SELECT * FROM sys.dm_exec_cached_plans go DROP PROCEDURE insert_sp DROP TABLE onetable You will see that there are only three entries in dm_exec_cached_plans. Here I have used ODBC-syntax to get RPC-calls. If you instead use plain EXEC statements, you will get more cache entries. But these are for the adhoc batches with the EXEC statements - still no shell queries. But that is how you call stored procedures from an application. Or at least no how you should call them.

Note from the Author or Editor:
Thanks again, Erland. This paragraph (and the following one) should not refer to stored procedures. It should only reference automatically parameterized queries, which are the only ones that have shell query.

Erland Sommarskog  Jul 09, 2011 
Printed
Page 638
Chapter 10

In paragraph 4 (under the SQL listing) this sentence is unclear: "Ironically, although this isolation level is intended to help avoid blocking, if there are any users in the database when the preceding command is executed, the ALTER statement blocks it." Should the "it" at the end be dropped?

Note from the Author or Editor:
The proposed change is correct. There is an extra 'it' at the end of the sentence. It should be: "Ironically, although this isolation level is intended to help avoid blocking, if there are any users in the database when the preceding command is executed, the ALTER statement blocks." Error corrected. Will be fixed in next printing.

Daniel Fortunov  Dec 07, 2010  Mar 30, 2012
Printed
Page 640
last paragraph

The last paragraph says, that the column snapshot_isolation_state has the possible values of 0 to 4 (five values), but according to http://msdn.microsoft.com/en-us/library/ms178534 it is just 0 to 3 (four values).

Note from the Author or Editor:
Typo: The last paragraph on the page should start with "The column snapshot_isolation_state has possible values of 0 to 3..."

Anonymous  Jul 23, 2012 
Printed
Page 647
Chapter 10

Row 5 of the table says this regarding Snapshot Isolation: "You cannot use ALTER DATABASE to change the database versioning state inside a user transaction." and this regarding RCSI: "As for SI, you can change the database versioning state inside a user transaction." This is contradictory. Should the "As for SI" preamble be removed?

Note from the Author or Editor:
Actually, the 'can' should be 'cannot' for RCSI, the same as for SI. But this whole line is a little bogus, because you cannot run _any_ ALTER DATABASE command inside a user transaction, whether you're changing the database versioning behavior or anything else. I think this whole line should be removed from Table 10-16. Error corrected. Will be fixed in next printing.

Daniel Fortunov  Dec 07, 2010  Mar 30, 2012
Printed
Page 657
Chapter 10

First paragraph of the "Controlling Locking" section contains: "Keep in mind that by setting an isolation level, you have an impact on the locks that held, the conflicts that cause blocking, and the duration of your locks." "...you have an impact on the locks that held..." should read: "...you have an impact on the locks that *are* held..."

Note from the Author or Editor:
Suggested change is correct. Sentence should be: "...you have an impact on the locks that are held..." Error corrected. Will be fixed in next printing.

Daniel Fortunov  Dec 07, 2010  Mar 30, 2012
Printed
Page 658
Chapter 10

The SQL listing on this page shows the syntax for four statements, the latter three of which contain the text: [WITH (locking hint) The closing bracket is missing; the hint syntax in these three instances should read: [WITH (locking hint)]

Note from the Author or Editor:
On page 658, the hint syntax used for the DELETE, UPDATE and INSERT needs a final square bracket: [WITH (locking hint)] Error corrected. Will be fixed in next printing.

Daniel Fortunov  Dec 07, 2010  Mar 30, 2012
Printed
Page 661
first paragraph after first code example

In the first paragraph after first code example, there is an "inline code example": SELECT TOP 1 * FROM <OrderTable> which is said to select the first unlocked row. I think the "inline code example" should be: SELECT TOP 1 * FROM <OrderTable> (READPAST);

Note from the Author or Editor:
Reader's suggestion is correct. The (READPAST) hint is necessary.

Anonymous  Jul 23, 2012 
Printed
Page 753
Rightmost column

The index entry for "VIA" incorrectly expands the acronym to *Visual* Interface Adapter (rather than *Virtual* Interface Adapter), and there is also another index entry for "Visual Interface Adapter" (separate from the "Virtual Interface Adapter", which also has an entry).

Note from the Author or Editor:
VIA is Virtual Interface Adapter and that should be the only expansion in the index. All pages referenced by Visual Interface Adapter should instead be listed under Virtual Interface Adapter. Error corrected. Will be fixed in next printing.

Daniel Fortunov  Oct 16, 2010  Mar 30, 2012