Why would the Primary Key NOT BE a Clustered Index and another Index BE Clustered?

Question

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?

Answer 1

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.