Inside SQL Server 2005: The Storage Eninge

Error Log for Inside SQL Server 2005: The Storage Engine

Updated: 15 February 2008

Chapter 1

· Page 2, last paragraph:

"When you first put the disc into your CD drive or execute the xxx file,…”

The xxx should have been replaced after I found the name of the file. This sentence should say something like this (from BOL):

“Insert the SQL Server 2005 DVD into your DVD drive. If the autorun feature on your DVD drive does not launch the installation program, navigate to the root of the DVD and launch splash.hta. If installing from a network share, navigate to the network folder and launch splash.hta.”

· Page 8, second paragraph:

"The [SQL Server service] account must be in the local Administrators group if you're installing SQL Server on Windows 2000 or Windows 2003".

This is definitely incorrect. The Windows account used by the SQL Server service does NOT need to be a local Administrator. There are a number of rights that need to be granted to whatever domain account you are using, and SQL Server setup assigns these to the selected account. If later, you want to change the Windows account that the SQL Server service is using, you must make sure the new account has all the privileges

Full details can be found in the Books Online:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/309b9dac-0b3a-4617-85ef-c4519ce9d014.htm

· Page 11, third paragraph from the bottom:

"For a Japanese sort order, you can specify kana insensitivity, which means that katakana characters are always unequal to hiragana characters."

If kana-insensitive were chosen (by not checking Kana Sensitive), then it would mean that katakana and hiragana characters are treated as equivalent. (i.e. the unequal should be equal)

· Page 28, paragraph 3:

"…Companion CD for this book…”

Originally we were planning on a CD, as in my previous books, but we have moved all the scripts and additional material to a companion web site, which is described in the book’s Introduction.

Chapter 2

· Page 42, second paragraph:

"Each worker thread can use at least half a megabyte of memory on a 32-bit system and at least 2 gigabytes (GB) on a 64-bit system."

Oops. This should say 2 MB on a 64-bit system. Since the note about 64-bit systems was a late addition, added in response to one of my Tech Reviewers at Microsoft, the change itself did not go through further Tech Review, and I totally blew it just typing one wrong character. I wrote GB instead of MB, and then the final MS Press editor expanded that into the full word gigabytes.

· Page 48, near the top:

There is a note from the Tech Editor to me that should have been removed by the Copy Editor. (Several people have reported this one! … It’s pretty amusing.)

· Page 57, paragraph 2:

"It is recommended that you enable this option on a 62-bit system"

I guess this is a cross between 32- and 64bit. J

It should be 64-bit system.

· Page 83, paragraph 3, first word:

"Thep default trace output file …"

Silly typo. I can’t imagine how it wasn’t caught, at least by a spell checker. Should be: “The default trace output file …”

Chapter 4

· Page 89, near the bottom:

"The created and modified date for both of these files [for the mssqlsystemresource database] is the day I installed this SQL Server instance."

This is incorrect. The created and modified date is the date that the code for the current service pack was frozen. It should be the same date you see when you run SELECT @@version. For SQL Server 2005, Service Pack 1, this is Apr 14 2006.

· Page 111, next to last line:

" There are several large indexes in tempdb…"

This should be “There are several large indexes in AdventureWorks…”

· Page 136, third bullet:

"…(which can implement the updates as a delete followed by an update)."

This should be "..(which can implement the updates as a delete followed by an insert)".

· Page 142, third paragraph and bulleted list:

SQL Server 2005 always checks the sys schema first for any object access.

This was true in some of the early CTP releases, but is not true in the released product for SQL Server 2005. SQL Server will never check the sys schema unless you specifically reference it.

· Page 142, third paragraph:

The table name described in the paragraph is not the same as the table name in the example. The text refers to mytable and the example uses table1.

· On Page 146 Step 2:

The -e parameter is listed twice.

The code was intended to show that you could specify your own locations for the master data and log files, and for the errorlog. I tried to just modify another example, but I forgot to remove the previous value for the location of the errorlog. The following is the correct list for the startup parameters:

-dE:\SQLData\master.mdf;
-lE:\SQLData\mastlog.ldf;
-eE:\ SQLData\LOG\ERRORLOG;

Chapter 5

· Page 154, last paragraph:

"When a log file is first created, it always has between 4 and 16 VLFs"

When first researching this chapter I was given the information above. However, I have recently found out that for “trivial” log file sizes 1MB or less, SQL Server 2005 will just create as many minimally sized log files as it can fit. A minimum VLF size is 31 * 8KB. So a half MB log file will have 2 VLFs. Keep in mind that if you don’t specify a size for your database files, SQL Server will use the size of the data and log files in the model database.

· Page 155, first bullet:

"Figure 5-3 contains 04 active VLFs"

The figure shows only 2 active VLFs, #3 and #4.

· Page 157, first line:

“If one of the VLFs in multiple physical log files is available for reuse when a new VLF is needed, SQL Server will add new VLFs to each physical log file in a round-robin fashion'”

One little letter completely changed the meaning here. If there are any VLFs available, no new ones need to be created. The ‘one’ should be ‘none’:

“If none of the VLFs …”

· Page 158, second to last paragraph:

"If that column has a value of 0, the database is in autotruncate mode."

In the final version of SQL Server 2005, a database in autotruncate mode returns NULL for the value in last_log_backup_lsn. In addition, prefacing the view name with master is not necessary. Here is a query to list all databases in autotruncate mode:

SELECT db_name(database_id)

FROM sys.database_recovery_status

WHERE last_log_backup_lsn is NULL

· Page 171, second paragraph:

"Damaged pages can be detected when …. The database is dropped."

This is quite misleading, because when a database is dropped, all pages from that database in the suspect_pages table are also dropped.

Chapter 6

· Page 178, last paragraph:

"You must be in the master database to access the sysdatabases compatibility view directly.”

This was true in some of the beta versions of SQL Server 2005, but it is not true in the final release. You can be in any database and select from sys.databases.

· Page 192, last paragraph, third and fourth lines:

"So a hex value of 0x270F corresponds to a decimal value of 9999 and a bit string of 10011100001111.”

“So a hex value of 0x270F corresponds to a decimal value of 9999 and a bit string of 0010011100001111.”

The text explains that strings of bits are stored for binary data types, and since the given example of 0x270F represents 2 bytes or 16 bits, then the corresponding bit string would include the two leading 0 bits as part of the 16-bit string.

· Page 196, second to last paragraph:

"When it [ANSI_NULLS] is set to false, comparisons of non-Unicode values to a null value evaluate to TRUE if both values are NULL.”

The word non-Unicode is not required.

· Page 208, first paragraph, 4th sentence:

“For varchar data that stored on row-overflow pages, and for text data,…”

Correction:
“For varchar data that is stored on row-overflow pages, and for text data,…”

· Page 219, figure 6-10:

In the diagram, in the exploded view for the row, there are row offset numbers labeled (e.g., 0, 2, 4, 9, 13, 16, 22, 24). The offset 2 indicator is misplaced; it should be moved slightly to the right, over the ‘16’ byte.

The label “Position to find number of columns” should instead be “Offset to find number of columns” since the “Number of columns” element is at offset 22 (decimal). The term offset is the more accurate term, according to the definition/explanation given on page 217 for the terms offset and position.

The label for the data in Col 4 is given as (‘cccc ‘) with lowercase c’s. The INSERT statement shown on page 218 inserts four uppercase C’s.

· Page 210, Table 6-4:

indexed should be indexID (10^th row in the table)

In addition, the caption for the table should really be something like “Information reported in the Page Header section”. Not all of this information is actually contained as separate elements in the page header for a page on disk. For example, “Metadata: ObjectId", "Metadata:PartitionId", and "indexId" are not contained in the page header in SQL Server 2005. The page header contains the allocation unit ID from which the other values can be determined internally.

· Page 215, last line on the page:

Bit 7 is used in SQL Server 2005 to indicate a GHOST RECORD.

· Page 216, Table 6-6:

Status Bits B should say “…(not used in SQL Server 2005)”

· Page 220, First line in the fourth paragraph:

“The first byte is Status Bits A, and its value (0x10) indicates that only bit 3 is on so the row has no variable length columns. “

It is true that there are no variable length columns, because that would be indicated by bit 5, which is not on. However, the value 0x10 indicates that only bit 4 is on, and that bit indicates that a NULL bitmap exists. The absence of bit 5 indicates that there are no variable length columns.

· Page 220, second and third lines in the fourth paragraph:

“The third and fourth bytes (1,600) indicate the length of the fixed-length fields,…”

This was the work of an overzealous editor. I am not referring to the number one thousand six hundred, but to the two-byte string represented by the hex values 16 and 00. This line should be:

“The third and fourth bytes (1600) indicate the length of the fixed-length fields,…”

· Page 220, fifth and sixth lines in the fourth paragraph:

“To know where in the row between 4 and 22 each column is actually located, we need to know the offset of each column.”

Replace with:

“To know where in the row between offsets 4 and 22 each column is actually located, we need to know the offset of each column.”

· Page 222, middle of fourth paragraph:

“Instead, the row itself holds the ending position of each variable-length column within that row in a part of the row called the Row Offset Array.”

Replace with:

“Instead, the row itself holds the ending position of each variable-length column within that row in a part of the row called the Column Offset Array.”

· Page 223, Figure 6-11:

The label “Position to find number of columns” should instead be “Offset to find number of columns” since the “Number of columns” element is at offset 9 (decimal).

· Page 231, Last line:

The following code is incorrect

UPDATE HasText

SET col2 = REPLICATE('Z', 250)

The text column is col3, so the code should be

UPDATE HasText

SET col3 = REPLICATE('Z', 250)

· Page 235, bottom of page

Several user defined types are listed (tid, id, empid) in my output. UDTs will show up when you select from sys.types and will be different in each database. In addition, my output does not show the XML datatype. To get a list of just the system types, you can run the following query:

SELECT system_type_id, name

FROM sys.types

WHERE is_user_defined = 0

ORDER BY system_type_id;

· Page 237, last bullet in second bulleted list:

"SQL Server 2000 allows you to defined FOREIGN KEY CONSTRAINTS...”

While this is true, the paragraph should have referred to SQL Server 2005.

· Page 240, second bullet:

“The object_id column in the constraint views is the object ID of the constraint, and the parent_column_id column of the constraint views is the object ID…".

This bullet is discussing the commonalities of all four constraint catalog views. All four of the views have a parent_object_id, but not necessarily a parent_column_id. The reference above should be to parent_object_id.

· Page 240, third bullet:

“A table-level constraint or any PRIMARY KEY/UNIQUE constraint (even if column level) always has 0 in the parent_column_id column".

Since the paragraph is discussing sys.check_constraints and sys.default_constraints, my reference to PRIMARY KEY/UNIQUE constraints is meaningless. In fact, the view sys.key_constraints that has information about these constraints does not even have a parent_column_id column.

· Page 243, middle paragraph:

‘You can add only one column for each ALTER TABLE statement.’

This contradicts what I said at the top of the previous page, where I summarize the ALTER TABLE options. The second bullet says you can add one or more new columns in a single ALTER TABLE, and that is true. The statement on page 243 is wrong.

· Page 243, middle paragraph:

'If the new column doesn't allow NULLs and isn't an identity column, the new column must have a default constraint defined'.

There are two exceptions to this statement: when the table is empty or when the new column is timestamp or rowversion, no default is required.

Chapter 7

· Page 254, bottom of page, syntax for CREATE INDEX:

The options following the WITH keyword must be enclosed within a set of parentheses, which is missing in the provided syntax.

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name
ON table_name (column_name [ASC | DESC][,...n])

[WITH
([FILLFACTOR = fillfactor]
[[,] [PAD_INDEX] = { ON | OFF }]
[[,] DROP_EXISTING = { ON | OFF }]
[[,] IGNORE_DUP_KEY = { ON | OFF }]
[[,] SORT_IN_TEMPDB = { ON | OFF }]
[[,] STATISTICS_NORECOMPUTE = { ON | OFF }]
[[,] ALLOW_ROW_LOCKS = { ON | OFF }]
[[,] ALLOW_PAGE_LOCKS = { ON | OFF }]
[[,] MAXDOP = max_degree_of_parallelism]
[[,] ONLINE = { ON | OFF }]
)]

· Page 256, first paragraph:

When talking about IGNORE_DUP_KEY the book mentions "The nonunique row will be discarded, and all other rows will be inserted or updated".

The words "or updated" should be removed as IGNORE_DUP_KEY applies only to INSERT operations.

· Page 254, fourth line from bottom:

The following sentence is discussing the STATISTICS_NORECOMPUTE option

“Setting this option to OFF overrides an ON value for the AUTO_UPDATE_STATISTICS database option.”

We’ve got sort of a double-negative situation here. The correct wording should be:

“Setting this option to ON overrides an ON value for the AUTO_UPDATE_STATISTICS database option.”

· Page 264, figure 7-3:

The bytes at offsets 22-23 are shown as 0400, which indicates four columns, but the value should be 0500 because the uniqueifier is counted as a column. The byte at offset 24 is shown as f4 but it should be e8 (binary 11100100), which indicates that column 3 (of the 5 columns) is NULL. The correct row is actually shown as slot 1 in Figure 7-4.

· Page 265, figure 7-4:

At the bottom of the figure, the label “Nvar” is used to refer to the two bytes indicating the number of variable length columns. This should be changed to “VarCount” to be consistent with Table 6-6 (on page 216) and the usage of “VarCount” in Figure 7-7 (on page 272).

· Page 265, starting at sixth line from the bottom:

“These bytes are at offsets 33-34 in these rows with the duplicate keys and have the value of 1. The next 2 bytes (offsets 35-36) indicate the position where the first variable-length column ends. In both these rows, the value is 0x29, which converts to 41 decimal. The last 4 bytes (offsets 37-40) are the actual uniqueifier.”

The figure is correct but the text is wrong. It should be replaced by the following:

“These bytes are at offsets 25-26 in these rows with the duplicate keys and have the value of 1. The next 2 bytes (offsets 27-28) indicate the position where the first variable-length column ends. In both these rows, the value is 0x21, which converts to 33 decimal. The last 4 bytes (offsets 29-32) are the actual uniqueifier.”

· Page 267, last line:

DBCC PAGE (AdventureWorks,1,21090, 1);

The DBCC PAGE command above will show you the contents of a leaf page, as DBCC IND indicates that 21090 is at level 0. If I wanted to use DBCC PAGE to see the root page explored in Figure 7-5 I would need to use this command:

DBCC PAGE (AdventureWorks,1,21738, 1);

· Page 272, Figure 7-7

The enlarged section of the row in the lower part of the figure shows bytes 0 to 22 with an incorrect value for the NULL bitmap. The NULL bitmap is shown as 00 when it should be fc, as it is in the upper section of the figure, where the entire row is shown shaded.

Also in the enlarged section the four bytes for offset 13 to offset 16 are shown as 38323220 but should instead be 31313332 (corresponding to the string ‘1132’). The upper shaded row shows the correct bytes.

· Page 280, fourth paragraph:

"Technically, the option ARITHABORT must also be set to on, but in SQL Server 2005, setting ANSI_WARNINGS to ON automatically sets ARITHABORT to ON, so you do not need to set it separately."

This is not really an error, but merely a caveat. The above applies only to databases that are in compatibility level 90.

· Page 282, third paragraph:

"You cannot create indexes on any computed columns in system tables"

Remove that sentence. You can’t even see system tables, much less build any indexes on them.

· Page 282, at the bottom:

"If any of your SET options does not have the correct value when you create the table, you get this message when you try to create the index".

The important part of the sentence is "when you create the table". The error message shown (Msg 1934) is returned when the table is created with the right ANSI_NULLS values and the index is created with a wrong one. If the table is created with a wrong setting, and you try to then create an index on a computed column in the table, you will get error 1935, similar to the following:

Msg 1935, Level 16, State 1, Line 1

Cannot create index. Object 't1' was created with the following SET options off: 'ANSI_NULLS'.

· Page 284, almost the exact middle of the page:

"…indexes on Discount would have to dropped also…"

This is just a minor typo: it should be ‘would have to be dropped’, but I need to list typos here so anyone else thinking of reporting them can see which ones have already been posted, and also so that Microsoft Press can see them here, and correct them when they do a new print run of the book.

· Page 286, the middle of the page, right after the code to create the view:

"Note the WITH SCHEMABINDING clause and the specification of the schema name (dbo)…

The schema name used in the example is Sales, not dbo.

· Page 290, first line:

““My second table, TransactionArchiveHistory, is created using the TransactionsPS1 partitioning scheme.”

This should be replaced with:

“My second table, TransactionArchiveHistory, is created using the TransactionsPS2 partitioning scheme.”

· Page 290, first paragraph after Figure 7-8, last sentence:

“Below, I’ll describe the most relevant columns of each of the these views:”

This should be replaced with:

“Below, I’ll describe the most relevant columns of each of these views:”

· Page 290, bullet at bottom of page:

“Sys.data_spaces has a primary key called data_space_id, which is either a partition ID or a filegroup ID, and there is one row for each filegroup and one row for each partition scheme.”

This should be replaced with:

“Sys.data_spaces has a primary key called data_space_id, which is either a partition scheme ID or a filegroup ID, and there is one row for each filegroup and one row for each partition scheme.”

· Page 291, last part of last bullet in middle of page:

(discussing sys.partition_range_values) “…its function_id column can be joined with sys.partition_functions, and its boundary_id column can join with either partition_id in sys.partitions or with destination_id in sys.destination_data_spaces.”

This should be replaced with:

“… its function_id column can be joined with sys.partition_functions, and its boundary_id column can join with either partition_number in sys.partitions or with destination_id in sys.destination_data_spaces.

· Page 317, almost at the bottom of the page:

The example refers to a database called ‘AdventureWords’, which was an intended misspelling, but then the text says

'There is no such database as AdventureWorks'.

The text should say 'There is no such database as AdventureWords'.

· Page 320, middle of last full paragraph:

“ … and an out-of-order page is one that has a lower page number that the previous page."

This is a minor typo; it should say: “ … and an out-of-order page is one that has a lower page number than the previous page."

Chapter 8

· Page 335 and 336:

“Your choice of isolation level determines which of these [following] behaviors is allowed.”

"…you can decide which of the four behaviors described previously to allow by setting an appropriate isolation level using the command SET TRANSACTION ISOLATION LEVEL <isolation_level>."

In fact, only 3 of the four behaviors are directly controllable by your ISOLATION LEVEL setting; lost updates require a bit more effort to achieve, fortunately. In fact, on page 336 at the beginning of the section on Uncommitted Read, I say “In Uncommitted Read isolation, all the behaviors described previous except lost updates are possible.”

· Page 337, first paragraph of section on Repeatable Read:

“In other words, issuing the same query twice within a transaction will not pick up any changes to data values made by another user's transaction”

This sentence is misleading. In Repeatable Read isolation, once the data is read, no changes can be made by any other user’s transaction. As the book says in the first paragraph in page 338, in Repeatable Read isolation: 'No other user can modify the data visited by your transaction as long as your transaction is open'.

· Page 354, third paragraph of section on Lock Ownership:

“A lock with a req_ownertype value of CURSOR…”

This should be “A lock with a request_owner_type value of CURSOR…”

· Page 357, second bullet, describing ‘request _status:

“Request_status can be one of three values: GRANTED, CONVERT, or WAIT”

This should be “Request_status can be one of three values: “GRANT, CONVERT or WAIT”

· Page 378, Figure 8-5:

The figure refers to the publishers and authors tables, which is not how the figure is described when I discuss it in the last paragraph on page 377. The figure should refer to the Product and PurchaseOrderDetail tables.

· Page 381, second paragraph, middle of paragraph:

“Once one process is killed, its transaction is aborted, and its locks are rolled back…”

This should be: “Once one process is killed, its transaction is aborted, and its locks are released…”

· Page 386, first bullet on page, in the last sentence:

“The database will not be in the state ALLOW_SNAPSHOT_ISOLATION.”

This should be: “The database will now be in the state ALLOW_SNAPSHOT_ISOLATION.”

· Page 389, Table 8-13, Time 4, Transaction 1:

The table name seems to have been truncated.

Production.ProductInvent should be Production.ProductInventory

· Page 400, just after the middle of the page:

Right after a SELECT from the view sys.dm_tran_current_transaction, the next paragraph says:

“Note that although this transaction has an XSN because it will generate versions, it is not running in SI, so the is_snapshot value is 0.”

This should say “…so the transaction_is_snapshot value is 0” (A related view sys.dm_tran_active_snapshot_database_transactions has a column called is_snapshot, but the view sys.dm_tran_current_transaction does not.)

· Page 410, Step 1.

-- In a new query window, execute the following batch to lock one row in the titles table

USE AdventureWorks;

BEGIN TRAN;

UPDATE HumanResources.Department
SET ModifiedDate = getdate();
WHERE DepartmentID = 1;

There are two errors in this one step:

1) The comment in the first line should refer to the HumanResources.Department table, not the titles table.

2) There should not be a semicolon after getdate( ).