If a row moves because it no longer fits on the original page, it still has the same row locator (i.e., the clustering key for the row stays the same), and SQL Server doesn't have to modify any nonclustered indexes. As I've mentioned before, for a table that has a clustered index, the bookmark information in the leaf-level nonclustered index rows is the clustered-index key value. If the table is a heap, the bookmarks in the nonclustered-index leaf rows are the physical locations of the rows. In SQL Server 2000 and 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 from there, the forwarding pointer directs SQL Server to the new location.
Forwarding pointers let you modify data in a heap without worrying about SQL Server having to make drastic changes to the nonclustered indexes. If SQL Server has to move a forwarded row again, it updates the original forwarding pointer to point to the new location. That way, you don't end up with a forwarding pointer pointing to another forwarding pointer. Furthermore, if the forwarded row shrinks enough to fit in its original place, SQL Server eliminates the forwarding pointer and moves the record back to that place.
Although SQL Server can manage individual rows efficiently by using forwarding pointers, you still need to be concerned about their overhead. When performing a scan of the entire table, SQL Server follows each forwarding pointer as it accesses a row, then returns to the row after the one that contains the pointer. This process results in two additional page accesses for each forwarded row in the table.
Running the script in Listing 1 lets you see this overhead for yourself. The script first creates a copy of the Northwind database's Customers table, called BigCustomers, then gives the CompanyName column a much longer maximum length. The sp_spaceused procedure shows that the table has 91 rows and 4 data pages (32K of data space). When you set STATISTICS IO on and select all the rows from the table, only four logical reads are required, as you'd expect. However, the script then updates the CompanyName column to make the data fill all 800 bytes. This update increases the size of each row, so most of the enlarged rows need to move to a new page. After the update, the script again runs sp_spaceused, which shows that the table now takes up 13 pages (104K of data space). SQL Server added 9 pages to handle the increased size of the 91 rows.
Finally, the script again selects all the rows from the updated table. Here, you see the real penalty of forwarding pointers. Although the table has only 13 pages, a SELECT of all the rows on those pages requires 153 page reads—almost two reads for every row in the table. Note that you can encounter large numbers of forwarded rows not just from a batch update, as with Listing 1's script, but also when you've updated many individual rows over time, as is likely in a production system.
This problem of needing an enormous number of extra page reads is a concern only when SQL Server is performing scans of most, or all, rows in a table during one operation. If you're accessing the rows individually, as when you have a very selective WHERE clause, the extra overhead is often unnoticeable. You can avoid the increase in reads by building a clustered index on your table because SQL Server uses forwarding pointers only in heap (nonclustered) tables. If you can't have a clustered index, the next best solution is to fix the column length and not allow any change in size.
A future version of SQL Server might include some mechanism for performing a physical reorganization of the data in a heap, which would get rid of forwarding pointers. Note that forwarding pointers exist only in heaps, but DBCC INDEXDEFRAG doesn't work on heap tables. So, you can defragment a nonclustered index but not the heap table itself. With current SQL Server releases, when a forwarding pointer is created, it stays in place forever—with two exceptions. The first exception is the case I mentioned, in which a row shrinks enough to return to its original location. The second exception happens when the entire database shrinks. SQL Server reassigns the bookmarks when it shrinks a file. The shrinking process never generates forwarding pointers. For pages that are removed during the shrinking process, any forwarded rows they contain are effectively "unforwarded."
In SQL Server 2000 and 7.0, updating a row in place is the rule rather than the exception. Unless the row can't stay in the same location because of one of the reasons I mentioned, the row will remain in its place, and no other changes to any indexes are necessary. For more details about the internal mechanics of updating rows, see "SQL Server 7.0 Update Strategies," March 2000, InstantDoc ID 8031. All the information in that article is also relevant to SQL Server 2000.
Recommendations
You can do several things to obtain optimal indexing for data-modification operations. First, it's a good idea to have a clustered index key, which should be on a column (or group of columns) that rarely changes. Second, test all the query types you'll be running to make sure that the indexes that support your retrieval operations don't impose too high a cost on your modification operations. Third, create nonclustered indexes only if they're absolutely necessary to help SQL Server find the rows you need to retrieve or modify. If you have several indexes that all give good performance for retrieval of the same data, keep only one of them—preferably one that also can help your data-modification operations.
This discussion has focused on the internal manipulation necessary for modifying just one row. If one operation modifies multiple rows, you need to be aware of some other side effects. Most of the special considerations you need to take into account when modifying multiple rows within a query are the same for all three data-modification operations (inserts, updates, and deletes). But I'll save that discussion for next time.