For insert-performance consideration, should a clustered index on a timestamp be ascending or descending?_问答_开发者

For insert-performance consideration, should a clustered index on a timestamp be ascending or descending?

开发者 https://www.devze.com 2023-01-13 18:34 出处：网络

I just realized I have a clustered index on a Timestamp in descending 开发者_Python百科order.I\'m thinking about switching it to ascending, so that as new, ever-increasing timestamps are inserted, the

I just realized I have a clustered index on a Timestamp in descending 开发者_Python百科order. I'm thinking about switching it to ascending, so that as new, ever-increasing timestamps are inserted, they are added to the end of the table. As it stands now, I suspect it has to add rows to the beginning of the table, and I wonder how SQL Server handles that.

Can it efficiently allocate new pages at the beginning of the table, and efficiently insert new rows into those pages, or would it be better filling up pages in the order of the timestamps and allocating new pages at the end with an ascending clustered index.

It's actually the same whether you add at start or end.

Page fills up, page splits, new page is allocated...

The new page may or not be contiguous whether it's at the start or the end.. which is why you run ALTER INDEX etc regularly.

The ASC/DEC order of the clustered index will matter more for SELECT/ORDER BY in practice... although I've noticed this less in SQL Server 2005 and above.