Learn the ins and out of updating
Server updates rows in multiple ways, automatically and invisibly choosing the fastest update strategy for a specific operation. In determining the strategy, SQL Server evaluates the number of affected rows, how it will access the rows (by a scan or an index retrieval, and through which index), and whether changes to the index keys will occur. In SQL Server 7.0, updates can happen in place or as a delete followed by an insert. (An in-place update is one where SQL Server changes the bytes in the row with no movement of data necessary.) In addition, the SQL Server 7.0 query processor or the storage engine can manage updates. The decision of whether the query processor or the storage engine controls the update is relevant to all data modification operations (not just updates).
Releases of SQL Server before 7.0 can perform either a direct or a deferred update. Many people mistakenly interpret direct to mean that the update is done in place. People then conclude that a deferred update means that the update isn't done in place, but requires the row to be moved to a new location. But deferred means that the update occurs in two passes. The first pass uses the transaction log as a holding area where SQL Server records all the changes it will make and marks them as NO-OP, meaning that no operation occurs at that time, but an entry is made in the log. The second pass then rereads the log and applies the changes. Also, earlier releases of SQL Server update every nonclustered index any time a row moves, to hold the row's new location. Because all changes—including changes to the nonclustered indexes—must be recorded twice, deferred operations are log-intensive. Deferred updates are by far the slowest kind of SQL Server 6.x update.
SQL Server 7.0 has no deferred updates. SQL Server performs all updates in a direct mode, without using the transaction log as an intermediate holding area. Direct doesn't necessarily mean that the update happens in place, however. And because of the way SQL Server 7.0 maintains nonclustered indexes, the overhead of moving rows is inexpensive, even if the table has multiple nonclustered indexes.
Moving Rows
What happens if a row in a table needs to move to a new location? With SQL Server 7.0, such a situation might arise when you update a row with variable-length columns to a larger size that no longer fits on its original page. Or, because SQL Server stores rows in order of the clustering key, a row might need to move when the clustered index column changes. For example, if you have a clustered index on lastname, a row with a lastname value of Abbot is stored near the beginning of the table. If you then update the lastname value to Zappa, this row will move to near the end of the table.
The leaf level of nonclustered indexes contains a row locator for every row in the table. If the table has a clustered index, the row locator in every nonclustered index is the clustering key for that row. So if—and only if—the clustered index key is updated, modifications are required in every nonclustered index. Keep this fact in mind when deciding which columns to build your clustered index on. Clustering on a nonvolatile column is a great idea. If a row moves because it no longer fits on the original page, it keeps the same row locator (in other words, the clustering key for the row stays the same), and no nonclustered indexes need modification.
If a table has no clustered index (it's stored as a heap), the row locator stored in the nonclustered index is the row's physical location. In SQL Server 7.0, if a row in a heap moves to a new page, the row leaves a forwarding pointer in the original location. The nonclustered indexes don't need changing; they still refer to the original location, and the pointer specifies the new location.
Let's look at an example. The code in Listing 1 creates a simple table with three columns. The table has no clustered index, so it's stored as a heap. After populating the table with five rows, which fill the page, the code updates one of the rows to make its third column much longer. The row no longer fits on the original page and needs to move. To find the address of the page it moved to, the code selects the first column from sysindexes. The value you supply to DBCC PAGE depends on the value that the first column returns. See the sidebar "Using DBCC PAGE," page 57, for a partial explanation of the DBCC PAGE command.
Here are the contents of a row that contains a forwarding pointer; you can see what appears in the slot where the row with a = 3 previously appeared:
Slot = 2 Offset = 0x1feb
Record Type = FORWARDING_STUB Record Attributes =
11ef3feb: 0000f904 00000100 00 .........
In these results, you have to byte-swap each set of four bytes. Each two digits make up one byte. So, after byte-swapping the first four bytes, you get 04 f9 00 00. The value of 4 in the first byte identifies it as a forwarding stub. The f9 00 00 in the next three bytes represent the page number the row moved to. Converting this hex value to decimal, you get 249. The next group of four bytes tells you that the row is at slot 0, and the page is on file 1. If you then use DBCC PAGE to look at page 249, you can see what the forwarded record looks like. (For a more complete description of using DBCC PAGE and the output it returns, refer to my book, Inside SQL Server 7.0, Microsoft Press, 1999).
Managing Forwarding Pointers
Forwarding pointers let you modify data in a heap without having to make drastic changes to the nonclustered indexes. If a forwarded row must move again, SQL Server updates the forwarding pointer to point to the new location, so you'll never have one forwarding pointer pointing to another. Also, if the forwarded row shrinks enough to fit in its original place, SQL Server eliminates the forwarding pointer, and the record moves back to its original position.
With SQL Server 7.0, when a forwarding pointer is created, it remains forever, with two exceptions. The first exception happens when a row shrinks enough to move back to its original location. The second exception happens when the entire database shrinks. When a file shrinks, SQL Server reassigns the row identifiers (RIDs), used as the row locators, so the shrink process never generates forwarded rows. If the shrinking process removes pages, any forwarded rows or stubs on those pages are effectively unforwarded because all the rows have new locations.
To see the total count of forwarded records in a table, you can enable trace flag 2509. Then execute the DBCC CHECKTABLE command, and you'll see the number of forwarded records in that table.
DBCC TRACEON (2509)
GO
DBCC CHECKTABLE (bigrows)
Prev. page
[1]
2
next page 
