Sql-server – Clustered index on random order column

clustered-indexindexsql server

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
    )
)

UPDATE

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.

Best Answer

The recommendation for clustered indexes is that they are ever-increasing or ever-decreasing, but that doesn't mean they have to be. GUIDs aren't increasing or decreasing unless you are using sequential GUIDs. Most people don't use sequential GUIDs.

If you are worried about the performance hit from page splits, then lower your fill factor to accommodate more inserts before a page split is needed. This is what is advised for GUIDs too if the GUID is the clustered index (I'm not saying it should be the clustered index, I'm saying if it is). Be careful how low you lower it though as it will impact read performance, which probably is important to you since you mentioned the clustered index on OrderID helps your queries.

Related Question