Clustered index on random order column

Question

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.

Answer 1

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.

Answer 2

The answer is Not necessarily.

The purpose of the index is to assist in querying the table after insert, and you say that this index does that. The cost of this improvement are

1. That the index has additional overhead costs at insert- that is, that the new record has to be sorted among the existing records in terms of the clustered index values.

2. Maintenance of the index and related statistics to ensure that the improvement continues after the initial implementation. This could include rebuilding statistics and/or indexes periodically.

If the cost of the maintenance and/or insert overhead is greater than the benefit provided by the performance gain on selects using the table in the context it is operating, then the index should be rethought.

Answer 3

Thanks for the answers. I decided to add a surrogate key (as identity column) in my table due to the following reasons

1. My usage scenario is that the table has many inserts.
2. The surrogate key helps to keep the ever increasing order for clustered index.
3. Ever increasing order is important for avoiding fragmentation and page split as explained in Ever-increasing clustering key – the Clustered Index Debate……….again!

If the insert is too heavy (concurrently), the ever increasing order can create PAGELATCH_EX waits problem. Read PAGELATCH_EX waits and heavy inserts which mentions about hash partitioning with a computed column and Bit Reversion

Note: Insert Hot Spot problem can appear if workload involves many hundreds of concurrent threads inserting into the table.

Also read about Insert Hotspot in Knee-Jerk Wait Statistics : PAGELATCH - Paul Randal which says the following:

I’ve seen an insert hotspot like this crop up when someone’s tried to remove index fragmentation problems by changing a random GUID cluster key to an int or bigint identity cluster key, but fail to test the new table schema under production loads.

Latch wait can be analysed using DMV mentioned in Advanced SQL Server performance tuning - Paul Randal which uses sys.dm_os_waiting_tasks. Also refer Updated sys.dm_os_waiting_tasks script

PAGELATCH_EX - sqlskills identifies Insert Hot Spot and Page Split as two of the three possible reasons for PAGELATCH_EX.