Microsoft SQL Server 2012 Internals

Data modification internals

You’ve seen how SQL Server stores data and index information; now you can look at what SQL Server actually does internally when your data is modified. You’ve seen how clustered indexes define logical order to your data and how a heap is nothing more than a collection of unordered pages. You’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. Also, as a rule of thumb, you should always have a clustered index on a table. This section reviews how SQL Server deals with the existence of indexes when processing data modification statements.

More info

The SQL Customer Advisory Team published a white paper in mid-2007 ...