Composite clustered index vs non-unique clustered index. Which is better/worse in this case?
clustered-index, performance, sql-server
Solution
The answer is simple UNIQUE index is always better then NON-UNIQUE. There is some maths behind it but the greater uniqueness is the faster server can look up a record from index.
CLUSTERED index is great as they physically order the records on disk and it always a good idea to use CLUSTERED INDEX on UNIQUE keys.
CLUSTER INDEX with PRIMARY KEY give very good performance with large data. If your data is not high in column then it will not matter much.
Problem
I have a database where all tables include a `Site` column (`char(4)`) and a `PrimaryId` column (`int`). Currently the clustered index on all tables is the combination of these two columns. Many customers only have one site so in those cases I think it definitely makes sense to change the clustered index to only include the `PrimaryId`. In cases where there are multiple sites though, I'm wondering whether it would still be advantageous to only use the `PrimaryId` as the clustered index? Might having a smaller clustered index produce better performance than having a unique one? In case it's relevant, there are generally not going to be more than a few sites. 10 sites would be a lot.