0

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 and will (almost never) change.

0

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.

  • Hi @detzu, thanks for your answer. We do have a lot of problems with query performance at the moment, relating to these indexes. The NaturalKey will never change. – Dirk Boer May 16 '18 at 5:30
  • 1
    The natural key will never change, correct. But don't use it for database operations! Instead detzu is suggesting creating a surrogate key which is just a simple field only for use in the database. In doing so you will seperate database keys from business keys (a very useful distinction) AND gain huge performance bonuses in your tables and indexes. – blobbles May 16 '18 at 6:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.