Sample Header Ad - 728x90

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

2 votes
2 answers
102 views
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](https://technet.microsoft.com/en-us/library/ms191311(v=sql.105).aspx) . 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?
Asked by LCJ (900 rep)
Sep 14, 2016, 01:56 AM
Last activity: Sep 14, 2016, 01:12 PM