When all you indexes have included all the other columns, do you still have the penalty for a wide clustered index?

by Dirk Boer   Last Updated May 16, 2018 05:06 AM

So I hear sometimes that I should not use a clustered index on a wide column, mainly because it is being referenced in all other indexes.

When all my other indexes on this table include all other columns - is there still this (extra) penalty?

(This table has no external references on the clustered index by other tables)

For reference my table looks a bit like this:

PartOfNaturalKey1    int
PartOfNaturalKey2    varchar(10)
PartOfNaturalKey3    varchar(20)
PartOfNaturalKey4    int
Value                varchar(10)
LastChanged          datetime2(7)
Invalid              bit

The Natural Key is unique.



Answers 1


If you have in every index all columns and you have a PK , and another 2 indexes , one on Value and one on LastChanged , you really multiplied the table by 4. This will make the table really slow for all operations.

Add an ID column , make it PK and build indexes as you need based on the statements that run on the table.

Don't create an index on Invalid, it's a bit column , it's usually not useful .

Try to keep a rule that all indexes should be smaller in size than the table.

detzu
detzu
May 16, 2018 04:50 AM

Related Questions


Create Index Specifying Key List - TSQL

Updated September 24, 2017 10:06 AM


Rebuild Very Large Primary Key Index

Updated September 02, 2017 23:06 PM

Clustered Index Help, where did i go wrong?

Updated August 13, 2015 18:02 PM