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.
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.