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.