Is it needed to add additional column to make this clustered index unique?

Question

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?

score 2 · Answer 1 · answered Sep 14 '16 at 06:20

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.

score 0 · Accepted Answer · answered Sep 14 '16 at 13:12

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!

Is it needed to add additional column to make this clustered index unique?

2 Answers2