Paint the Town Red with Non-Clustered Indexes

Image courtesy of

I remember from my Sybase days that SQL Server tables could support up to 255 indexes. One clustered index and 254 non-clustered indexes. In Microsoft SQL Server that changed to just 249 non-clustered indexes. Where did those extra 5 potential indexes go? As a rough rule of thumb, we would always try for ten or less indexes per table for an OLTP environment so what’s the big deal? Well, now SQL Server 2008 supports up to 999 non-clustered indexes per table. Why?

Normally, we have to be careful about non-clustered indexes. They speed up look-up time when we only need a subset of the data. But they can slow down updates if we have too many. The analogy we use is that a non-clustered index is like the index at the back of a reference book. We can quickly look up information without reading the whole book. The clustered index is more like the table of contents. It’s the logical order the data is stored in. Because the actual data is also stored in the clustered index, the correct analogy for this type of index is the book’s table of contents plus the pages themselves i.e. the whole book.  So the clustered index is really the table itself.

As we update data in the table, SQL Server has to keep each index up to date with the new data. So while many indexes may speed up overall read time, they may slow down updates. The exception to the rule is the Data Warehouse which is typically a large historical database that is read-only during the day. Since no updates are being made, we can go to town on the index front. Create as many as we want. SQL Server does not have to update them since no updates are allowed. They do take up more space which may be less of a worry in these days of cheap storage . If we have to trade storage for performance usually we go for the faster option.

With the Data Warehouse, the classic architecture is the daily load cycle. Every night, we load the day’s activity from the operational databases into the warehouse. Before we do this, we usually drop all the indexes anyway then load the new data and rebuild the indexes. This usually is the fastest option.  Data Partitioning can also speed up this process if designed correctly.

In SQL Server 2008, sparse columns and wide tables are supported. A sparse column is where a majority of values in a column are going to be NULL and a table that supports these sparse columns is called a wide table. You are actually allowed up to 30,000 columns now in a wide table. You can also create filtered indexes on sparse columns so that only the non-NULL values are saved in the index. An example of this is where a Product table supports many products but only a small number of products are TV sets. You might have a column for TV Size but the majority of the values of this column will be NULL. Only the TV set products would have a non-NULL entry. So defining it as a sparse column and creating a filtered index will enable SQL Server to optimize access to this column and also save space. Now this actually is a violation of the normalization rules so Dr. Codd would not be amused but the feature is there if you need it. This is the real reason Microsoft increased the supported number of indexes.

So some customers must be really going to town for Microsoft to expand support from 249 to 999 indexes per table. In fact, they must be painting the town red and leaving everything in Las Vegas!

In this article

Join the Conversation