I have run into an interesting dilemma working on optimizing a table in a Microsoft SQL database.

Table details: The primary key is 3 parts (Date (date), Account (bigint), Field (nvarchar(4)).

The table itself is somewhat large, at 72 million records. Most of that is because of the daily load (there are around 250k records per day).

The table is primarily being joined on Date and Account, though it is sometimes filtered on Field.

I believe the clustered index on the primary key is the best way to go, but I'm not entirely sure. I know in general its better to have narrow CIs than wide CIs, and the table will only be joined on Date and Account.

Given this: is a narrower CI on Date and Account ever a good idea, or is CI on primary key optimal.

As an additional question: if the third part of the primary key was almost never filtered on, would that change the answer? In a scenario like this are there circumstances where narrow is better than unique.

  • 5
    Don't forget that if you create non-unique clustered index, SQL Server will add 4 byte hidden column to make it unique, so it almost nulls advantage of removing Field column from index key and the values are still stored in leafs. – Piotr Jul 31 '19 at 19:05
  • Very good point, I hadn't considered it that way. So then yeah there is barely any advantage to dropping field. As a related hypothetical: if field was a nvarchar(max) would that make a narrow index more worthwhile? – Josh Kraushaar Jul 31 '19 at 19:07
  • 5
    Also keep in mind that a wider clustering key affects the size of all your nonclustered indexes. – Queue Mann Jul 31 '19 at 19:39

Your Answer

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

Browse other questions tagged or ask your own question.