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.