Microsoft® SQL Server® 2008 Internals

Heap Modification Internals

We’ve seen how SQL Server stores data in a heap. Now we’ll look at what SQL Server actually does internally when your heap data is modified. Modifying data in an index, which includes a table with a clustered index, is a completely separate topic and will be covered in detail in Chapter 6. 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 Chapter 6, you’ll see the benefits and tradeoffs ...