Errata

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.

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.

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

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

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.

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.

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.

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

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.

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

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.

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

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

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

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.

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.

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

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

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.

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.

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.

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.