2

I have a table as listed below in SQL Server 2012. There is a clustered index on RequisitionID – but this column is not unique. There can be many ProductID for one RequisitionID.

CREATE TABLE [dbo].[RequisitionProducts](
    [RequisitionID] [int] NOT NULL,
    [ProductID] [int] NOT NULL,
    [Qty] [int] NOT NULL,
    [VendorID] [int] NOT NULL,
    [UnitAmount] [decimal](10, 2) NOT NULL,
 CONSTRAINT [pk_RequisitionProducts] PRIMARY KEY NONCLUSTERED 
    (
        [RequisitionID] ASC,
        [ProductID] ASC
    ) 
)

CREATE CLUSTERED INDEX [cidx_RequistionProducts] ON [dbo].[RequisitionProducts]
(
    [RequisitionID] ASC
) 
GO

I searched a lot and found that Clustered Index can be non-unique - but only on limited scenario. Only scenario mentioned appropriate is when there is a Range Search. In my case almost all queries will be based on RequisitionID only – and there is no range search required.

Should I add ProductID also to make the clustered index unique? What are the pros and cons?

2
  • A seek will still be used to limit rows to only the requested RequisitionID if that's the only key column. Adding ProductID will be beneficial to queries that specify both in predicates.
    – Dan Guzman
    Sep 14, 2016 at 2:08
  • 1
    Strictly speaking each key in a clustered index is unique. Under the covers SQL will add a 4-byte uniquifier to any duplicate keys to ensure its uniqueness. You won't be able to see that value, but it will be there, and it will take up storage space. If your search predicates include the ProductID, then by all means make it a part of the clustered index, otherwise, I'm not sure that it would be worth it.
    – Nic
    Sep 14, 2016 at 4:11

2 Answers 2

2

Should I add ProductID also to make the clustered index unique?

You should delete cidx_RequistionProducts and make the primary key clustered instead. The index that is created to support the primary key will do the job for you when filtering on RequisitionID.

What are the pros and cons?

I see no benefit of having two indexes when one is enough to get the job done. Removing the extra index will save you disk space and IO cost when updating the table.

2
  • 1
    Why should I make "primary key clustered" - I don't see any reasoning for that in your answer. Please explain.
    – LCJ
    Sep 14, 2016 at 12:12
  • @Lijo because then you don't need to create a second index. The index created for the primary key is enough if you declare the primary key to be clustered. That means you will only have to maintain the one clustered index when you modify the table. Sep 14, 2016 at 12:22
0

Thanks for the comment about uniquifier.

SQL Server internally adds uniquifier to make clustered index unique, if it is not so. So I will better add ProductID (which is int) myself into the clustered index. DBCC commands for testing this is present in Understanding and Examining the Uniquifier in SQL Server - Ken Simmons.

Usage scenario of this table is that it has frequent inserts. Hence I will introduce a surrogate key (identity column) and make it as primary key and clustered index. Ever-increasing clustering key – the Clustered Index Debate……….again!

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.