• Adam Thurgar

Clustered index column in non-clustered indexes


Whilst recently reviewing performance issues on a large legal system, I noticed that every non-clustered index, explicitly included the column/s of the clustered index. This is unnecessary as SQL Server adds the columns of the clustered index into the non-clustered index and effectively 'hides' these columns. Therefore you don't need to include them in the definition. There is no real penalty for having these duplicates - although any updates to the clustered index columns would occur twice and the size of the non-clustered index is larger. Any index reorganisation or statistic updates may also take fractionally longer.


4 views0 comments

Recent Posts

See All

Review missing index findings before you implement

There are a number of queries widely available that will look at the DMVs and tell you what indexes you may be missing. This is a great help with performance tuning your SQL Server. But be cautious of

Autoclose and autoshrink - set them to off

By running a simple check of your database settings (use helpdb or query sys.databases) you may find databases that are set to autoclose and/or autoshrink. If so, you should change these settings as p

Insert query performance improvement

I was asked the other day was there any way to improve the performance of a large number of rows being inserted. My immediate thought was to drop all indexes, do the inserts then recreate all the inde