Microsoft® SQL Server® 2008 Internals

Data Modification Internals

We’ve seen how SQL Server stores data and index information. Now we look at what SQL Server actually does internally when your data is modified. We’ve seen how clustered indexes define logical order to your data and how a heap is nothing more than a collection of unordered pages. We’ve seen how nonclustered indexes are structures stored separately from the data and how that data is a copy of the actual table’s data, defined by the index definition. And, as a rule of thumb, you should always have a clustered index on a table. The SQL Customer Advisory Team published a white paper in mid-2007 that compares various table structures and essentially supports this view; see http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/clusivsh.mspx ...