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
Last activity: Sep 14, 2016, 01:12 PM