I have an existing table named “Orders” in our system. OrderID is the primary key in this table – it is a clustered index. I have a new table designed as shown below for “OrderCompanyDetails”. It has a
1-to-1 relationship with Orders table. In the new table OrderID is kept as the clustered primary key.
Data gets inserted into the new table only when the Order is approved. So the OrderID getting inserted into the new table may not be in sequence. The OrderID 10 may get inserted before OrderID 5, depending on which order is approved first.
Having the clustered index on OrderID helps my queries. But the clustered index is on a column which gets data in a random sequence. Is it a bad index design? If yes, should I add a new meaningless identity column with name OrderCompanyDetailID and make that as clustered index?
CREATE TABLE [dbo].[Orders] ( [OrderID] [int] IDENTITY(1,1) NOT NULL, [OrderType] [char](3) NOT NULL, [StatusCD] [char](10) NOT NULL, [CreatedOnDate] [datetime] NOT NULL CONSTRAINT [DF__Orders__CreatedOn] DEFAULT (getdate()), CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED ( [OrderID] ASC ) ) CREATE TABLE [dbo].[OrderCompanyDetails]( [OrderID] [int] NOT NULL, [POCompanyCD] [char](4) NULL, [VendorNo] [varchar](9) NULL, [CreatedOnDate] [datetime] NOT NULL CONSTRAINT [DF_OrderCompanyDetails_CreatedOn] DEFAULT (getdate()), CONSTRAINT [PK_OrderCompanyDetails] PRIMARY KEY CLUSTERED ( [OrderID] ASC ) )
Recently read article Ever-increasing clustering key – the Clustered Index Debate……….again!. There is a comment in that
Remember – narrow, static, unique, ever increasing – and usually a surrogate key instead of a natural key.