Clustered index with datetime/Id

Naomi 7,361 Reputation points
2022-02-08T20:41:22.323+00:00

Hi everybody,

Since I'm here today I think I'll ask another question. I have a huge audit table to which all processes always add rows (never delete or update) with default datetime and bigint identity column. We're making a clustered index on datetime, id columns in that order.

My question is - given the fact that this is highly used table (lots and lots of inserts), should that clustered index be on datetime, Id columns both in ASC order? We do use this table for ad-hoc selects very often and obviously we're interested in most recent dates (say, last 1 day at most). When I was testing queries I found them to be quite slow having index in ASC order but the INSERTs must outweigh the performance since we're adding about 100K+ or more rows per day.

Thanks in advance.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,654 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,551 questions
0 comments No comments
{count} votes

Accepted answer
  1. Vladimir Moldovanenko 251 Reputation points
    2022-02-08T21:01:53.117+00:00

    @Naomi

    I would do this

    BEGIN TRANSACTION  
      
    CREATE TABLE dbo.Tbl  
    (  
        ID bigint NOT NULL IDENTITY(1, 1)  
        ,DT datetime2(3) NOT NULL  
        ,CONSTRAINT PK_ID PRIMARY KEY CLUSTERED (ID)  
        WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100, OPTIMIZE_FOR_SEQUENTIAL_KEY = ON) ON [PRIMARY]  
    )  
      
    CREATE NONCLUSTERED INDEX IX_Tbl_DT ON dbo.Tbl (DT ASC)  
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100)  
      
    ROLLBACK  
    

    Use SQL 2019, see

    https://techcommunity.microsoft.com/t5/sql-server-blog/behind-the-scenes-on-optimize-for-sequential-key/ba-p/806888

    I prefer sequence object to identity

    Thanks

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 100.8K Reputation points MVP
    2022-02-08T22:39:32.51+00:00

    If you need to know the order the rows were inserted in, it is better to rely on the id column I think. This is because time can go backwards in modern computers. That is, a row B inserted after row A, may still have an earlier timestamp, because CPUs may not be in perfect sync.

    1 person found this answer helpful.