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

views:

answers:

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

I just realized I have a clustered index on a Timestamp in descending 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.

gbn 2010-09-04 12:53:26

I thought it was just the opposite. My understanding was that it wouldn't matter as much for select/order by, because it can just use the index in reverse. But it would matter for inserts, because when filling up an individual page, it physically orders the information because it's a clustered index. I guess what I'm really interested in is how the page fills up, and whether the ascending or especially descending order affects how an individual page fills up, when items are inserted in ascending time order.

Triynko 2010-09-22 14:49:54

I was afraid that with a descending clustered index... every time I insert a new timestamp, it would have to shift the contents of the active page to order the new entry first (since it physically orders the rows descending).

Triynko 2010-09-22 14:53:54

The data is physically ordered on only create and rebuild. It is not physically maintained with INSERTS and UPDATES: it is *logically* maintained which is why regular index maintenance is required. If the ORDER BY is opposite to the INDEX sort then you can add work: I've seen IO double (or half) when sorts are aligned

gbn 2010-09-22 15:28:08

ansaurus

tags:

views:

answers:

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

related questions