Clustered composite primary key sort order on the date column

Question

We have a sales table that contains about 88.5m rows of data and has a composite primary key listed below, it also has a few additional columns with informational data:

1. RevenueCentreID int(Asc)
2. DateOfSale smaldatetime(Asc)
3. SaleItemID int(Asc)
4. SaleTypeID, int(Asc)

All four the above columns also has its own un-clustered index sorted in ascending order.

I recently ran some test against the table to see how I can improve on the performances of indexes etc. I came across something strange and was hoping someone could explain the results I got from the tests.

I set up profiler to watch the database in question. I then ran the following lines of script from management studio.

1. Select * From Sales Where DateOfSale = '20040610'and RevenueCentreID = 419
2. Select * From Sales Where DateOfSale = '20090301'and RevenueCentreID = 419
3. Select * From Sales Where (DateOfSale between '20040601' and '20040610') and RevenueCentreID = 419
4. Select * From Sales Where (DateOfSale between '20090301' and '20090310') and RevenueCentreID = 419

I got the following results from profiler: Result columns: Event Class, CPU, Reads, Duration 1. SQL:BatchCompleted 0 7 0
2. SQL:BatchCompleted 0 17 111
3. SQL:BatchCompleted 0 36 394
4. SQL:BatchCompleted 0 38 1042

As you can see from the results the duration and reads of the queries where the date was recent was significantly longer than earlier dates. I thought this was due to my composite key being sorted ascending and therefore SQL Server will check the earlier dates first.

However I tried to find some stuff online regarding index ordering and everything I have found speaks of it only being useful for sorting (Order By in queries). If this is true why are these results like this for an ascending index, surely if I sort it in descending order the earlier dates would get checked first? We obviously use the more recent dates more often than four year old sales data.