3

I was reviewing indexes on our SQL Server database today and noticed a relatively small (very old) table (3250 rows) that has a column of type varbinary(max) for image data.

The Primary Key is a Non-Clustered Index, and there is another index (on User ID and Is Removed) that IS Clustered.

Again, this is a very old table, and there's no one around anymore who was here when this table was created.

So, is there any reason for having it set up this way? And should I change it? And if I should, is there anything I should watch out for?

Narnian
  • 3,858
  • 1
  • 26
  • 29
  • 3
    Nothing dictates that the primary key needs to be clustered, except that this is the default behavior if you *don't* specify. There can be many reasons why you would want the primary key to be non-clustered and cluster on something else ... for example if you have a very high read:write ratio and most of your queries go against columns that aren't part of the primary key. I've had many cases where I had monotonically increasing clustered columns that weren't even eligible for primary keys... – Aaron Bertrand Jan 07 '13 at 22:04
  • @AaronBertrand So, should I go through my database and change the clustered indexes, primarily on relationship tables where the Relationship ID is never queried on? – Narnian Jan 07 '13 at 22:07
  • 2
    A very good, in-depth analysis can be found here: http://dba.stackexchange.com/questions/7741/when-should-a-primary-key-be-declared-non-clustered – Sebastian K Jan 07 '13 at 22:08

1 Answers1

5

Indexes are use case based. The PK is effectively an index on the PK (you enforce uniqueness in the index) and used where the PK is used in a query. You can easily imagine a case where the columns used in a query don't rely on the PK and use some other combination of columns.

Now imagine that this use case wasn't the only one, but was the primary one. In this case the designer could cluster on this use case and not on the ones that are used less frequently.

My suggestions to you are this:

  1. Don't make changes without understanding why things exist the way they are, as the original design may be there for a very good reasons - such as optimisation.
  2. Design your indexes and clustering with your system design and operational characteristics in mind and don't just blindly add things. You should do this with firm evidence and not just the 'finger in the air' option.

Trust me, I've learned this by being bitten by it. I try not change things without tests that supply evidence of the change both working and and not breaking other things. It's a crucial thing when working with legacy systems.

Preet Sangha
  • 64,563
  • 18
  • 145
  • 216