The Clustered Index Is Vital To Your Database Design

This is post #9 supporting  Tim Ford’s (b|t) initiative on #iwanttohelp, #entrylevel. Read about it here.

You get one clustered index per table.

That bears repeating, you get one clustered index per table.

Choosing a clustered index is an extremely important and fundamental aspect of all your SQL Server work. The one clustered index that you get determines how the data in your table is stored. Because the clustered index determines how your data is stored, it also determines how your data is retrieved. Much of SQL Server is engineered around the clustered index because it is such a foundational object for the rest of all behavior. Without a clustered index, the data in your table is stored in what is called a heap. It is essentially a pile, a heap, of data, stored without internal structure. The data in a heap can be found fairly quickly when only a single value is needed, but as soon as you start to retrieve ranges of data, more than one row, heaps become extremely problematic. Meanwhile, the clustered index stores all your data at the bottom of a B+Tree, structured storage, that is used to retrieve this data through the key, or keys, that you give your clustered index. Further, each non-clustered index also uses the key value from your clustered index as it’s means of retrieving data from the clustered index.

All this means that the vast majority of your tables should have a clustered index. Yes, there are exceptions, but, exceptions should be exceptional.

The standard advice, and it’s not wrong, is to make the clustered key as narrow as possible. Next, clustered indexes are designed to support monotonically increasing values extremely well. Clustered indexes must be unique. You either have to create them on a unique value, or, internally you’ll get a UNIQUIFIER applied to the key to ensure that each value is unique. All this taken together means that they work nicely on IDENTITY columns. Because of this advice, you’ll frequently see all the clustered indexes in a database on the IDENTITY columns. However, this is a bit of a trap. You need to remember that the clustered index is not just about storage, but about retrieval. If the most common access path to your data is through, for example, a foreign key, that value might be a better choice for the clustered key. If you always retrieve the data through a date range, that might be a better choice for the clustered key. Keep in mind the standard advice for clustered keys; narrow, monotonically increase, unique. However, also remember, they are where your data is stored. It is frequently better to sacrifice one or more of the standard practices in order to be able to use your clustered index to retrieve the data in most of your queries.

Think through where and how to apply your clustered indexes, and don’t get stuck into using them in a single manner, everywhere, within your system. You get one clustered index on a table. Be sure you put it somewhere that it will work well and somewhere that it will be well used, both.

6 thoughts on “The Clustered Index Is Vital To Your Database Design

  • Right on. As a DBA, I’ve had a very difficult time getting software developers to follow my recommendations on Clustered Indexes, mainly that you need one, a good one, that meets the criteria you listed. Many of them seem to love heaps for no rational reason other than maybe it’s “too much trouble” to create a good clustered index. I’m baffled by the resistance to this best practice, since there is so much good documentation available on this subject.

  • I’m not sure why people push back on it either. I do think that there’s over emphasis on fragmentation, so people try to avoid that. Also, everyone thinks that their system is 98% inserts and having a clustered index will slow things down. Yet, most systems, not all of course, but the overwhelming majority, are read heavy. It’s hard to convince people otherwise.

  • Rob

    I always think that the key values for the clustered index should have 4 characteristics; 2 for performance and 2 to reduce fragmentation.
    1. narrow.
    2. unique.
    3. status (key values do not get updated).
    4. append (new rows are added to at the end of the table because the index key values put them there).

    While some tables may perform better than an integer Identity column, if you don’t want to do some analysis and testing of different keys, you should at least add an identity column for the key.

OK, fine, but what do you think?

This site uses Akismet to reduce spam. Learn how your comment data is processed.