indexsql serversql-server-2008
When creating an index one can determine if an index column is sorted using ascending or descending order. BOL says that
[ ASC | DESC ]
Determines the ascending or descending sort direction for the particular index column. The default is ASC.
What is the primary reason for choosing between ASC or DESC?
If not an indexed view...
You'd need a third table that contains DateCreated and a key on an IDENTITY and a SubTable column that contains 1 or 2 only. And an index on DateCreated
This 2-column key becomes the key of the other 2 tables too. You use a CHECK constraint on SubTable to ensure an IDENTITY value is used in one table only.
Each bit of the UNION is a JOIN using Table3. Then the index spans all DateCreated values as expected.
The indexes you have now are per table: so SQL Server needs to sort the 2 sets of data.
For a non partitioned table I get the following plan
There is a single seek predicate on Seek Keys[1]: Prefix: DeviceId, SensorId = (3819, 53), Start: Date < 1339225010.
Meaning that SQL Server can perform an equality seek on the first two columns and then begin a range seek starting at 1339225010 and ordered FORWARD (as the index is defined with [Date] DESC)
The TOP operator will stop requesting more rows from the seek after the first row is emitted.
When I create the partition scheme and function
CREATE PARTITION FUNCTION PF (int)
AS RANGE LEFT FOR VALUES (1000, 1339225009 ,1339225010 , 1339225011);
GO
CREATE PARTITION SCHEME [MyPartitioningScheme]
AS PARTITION PF
ALL TO ([PRIMARY] );
And populate the table with the following data
INSERT INTO [dbo].[SensorValues]
/*500 rows matching date and SensorId, DeviceId predicate*/
SELECT TOP (500) 3819,53,1, ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM master..spt_values
UNION ALL
/*700 rows matching date but not SensorId, DeviceId predicate*/
SELECT TOP (700) 3819,52,1, ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM master..spt_values
UNION ALL
/*1100 rows matching SensorId, DeviceId predicate but not date */
SELECT TOP (1100) 3819,53,1, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) + 1339225011
FROM master..spt_values
The plan on SQL Server 2008 looks as follows.
The actual number of rows emitted from the seek is 500. The plan shows seek predicates
Seek Keys[1]: Start: PtnId1000 <= 2, End: PtnId1000 >= 1,
Seek Keys[2]: Prefix: DeviceId, SensorId = (3819, 53), Start: Date < 1339225010
Indicating it is using the skip scan approach described here
the query optimizer is extended so that a seek or scan operation with one condition can be done on PartitionID (as the logical leading column) and possibly other index key columns, and then a second-level seek, with a different condition, can be done on one or more additional columns, for each distinct value that meets the qualification for the first-level seek operation.
This plan is a serial plan and so for the specific query you have it seems that if SQL Server ensured that it processed the partitions in descending order of date that the original plan with the TOP would still work and it could stop processing after the first matching row was found rather than continuing on and outputting the remaining 499 matches.
In fact the plan on 2005 looks like it does take that approach
I'm not sure if it is straight forward to get the same plan on 2008 or maybe it would need an OUTER APPLY on sys.partition_range_values to simulate it.
Best Answer
Since indexes can be scanned in both directions most of the time there isn't much reason for choosing DESC. Some TOP queries could be helped by the DESC on some of the columns of a covering index but only testing will show if this helps.
One other difference is that the forward order scan can be run in parallel but the reverse order scan is always single threaded.