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 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 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 at 19:39

Clustered Index should be also Ever Increasing like identity column.

Why Field nvarchar(4).If it do not store multilingual data then you can make it varchar(4).Or of Field is always fixed length data of 4 then char will work ok.

Add identity column and make it Clustered Index.

You can create Unique Non Clustered Index on Date (date), Account (bigint), Field (nvarchar(4)). It will be more cost effective for Sql Optimizer than Clustered Index.

You can check similar example in Adventure Database where you sp_help each table like Sales.SalesHeader,Sales.Customer,Purchasing.Order

Clustered Index hold fewer rows per page as leaf page contain the data rows(all columns of tables).

Non clustered Index on other hand contain only Index Value(Date , Account , Field) and row locator (RID if the table is a heap or the CI key otherwise). So it contain more rows per page.

So Optimizer will find NCI more Cost Effective than than using CI.

  • Can anyone explain the reason behind downvote,so that I improve myself. – KumarHarsh Aug 2 at 11:28

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.