Clustered Index order

Question

I have a CRM system where I am reviewing the structure of the SQL Server 2017 database.

There is a History table with a clustered index on Date_Entered (datetime) and Time_Entered (varchar(8)). There are around 30 million rows (and counting).

When a customer record is opened, their most recent history is displayed in reverse date / time order.

With the index in ascending order, new rows are added at the end of the table. If I were to rebuild it in descending date / time order, I assume that displaying the records would be quicker (though it isn't slow anyway), but would that mean that new records would be inserted at the beginning, and therefore lead to constant page splits, or is that not the case?

Answer 1

You won't end up with constant page splits. You probably think that because you have the wrong mental image of clustered indexes and think that they are stored sorted in physical order so new rows must somehow get inserted physically at the beginning. This is not the case.

But - don't do that as you will end up with 99.x% logical fragmentation without page splits. The new physical pages will tend to be allocated from later in the file than previous ones whereas the logical order of the index would need them to be allocated from earlier (example).

Assuming inserts are in order of Date_Entered, Time_Entered then each new page will be filled up and then a new page allocated and filled up. There is no need for page splits that move rows between pages - just allocation of new pages. The only issue is that the physical page order will be likely a complete reversal of the logical index order until you rebuild or reorganize the index.

I assume that displaying the records would be quicker

No, it is not needed anyway as the leaf pages of the index are linked together in a doubly linked list and can be read in either forward or backwards direction.

Backward scans can't be parallelised but hopefully this isn't a concern for you.

In any event for displaying the customer record probably you are using a different index with leading column customerid making this largely irrelevant.