Inside Microsoft® SQL Server™ 2005, Fourth Edition

Data Modification Internals

We’ve now seen how SQL Server stores data and index information. Now we’ll look at what SQL Server actually does internally when your data is modified. We’ve seen how clustered indexes control space usage in SQL Server. As a rule of thumb, you should always have a clustered index on a table. There are some cases in which you might be better off with a heap, such as when the most important factor is the speed of INSERT operations, but until you do thorough testing to establish that you have one of these cases, it’s better to have a clustered index than to have no organization to your data at all. In Inside Microsoft SQL Server 2005: Query Tuning and Optimization, I’ll examine the benefits and tradeoffs of clustered and ...