I see a lot of advice that talks about the clustered index been the best index for use for range queries, that is queries with inequalities filters, queries that retrieve ranges of rows, as opposed to singleton queries, queries that retrieve single rows (including, unfortunately, a Technet article).
I suspect the reasoning behind this advice is the idea that the clustered index stores the data in order of the clustering key (ack) and hence it’s ‘logical’ that such a structure would be best for range scans as SQL can simply start at the beginning of the range and read sequentially to the end.
Question is, is that really the case?
Let’s do some experiments and find out.
CREATE TABLE TestingRangeQueries ( ID INT IDENTITY, SomeValue NUMERIC(7,2), Filler CHAR(500) DEFAULT '' ) -- 1 million rows INSERT INTO TestingRangeQueries (SomeValue) SELECT TOP (1000000) RAND(CAST(a.object_id AS BIGINT) + b.column_id*2511) FROM msdb.sys.columns a CROSS JOIN msdb.sys.columns b -- One cluster and two nonclustered indexes on the column that will be used for the range filter CREATE CLUSTERED INDEX idx_RangeQueries_Cluster ON TestingRangeQueries (ID) CREATE NONCLUSTERED INDEX idx_RangeQueries_NC1 ON TestingRangeQueries (ID) CREATE NONCLUSTERED INDEX idx_RangeQueries_NC2 ON TestingRangeQueries (ID) INCLUDE (SomeValue) GO
The query that I’ll be testing with will do a sum of the SomeValue column for a large range of ID values. That means that of the three indexes that I’m testing, one is clustered, one is a nonclustered that does not cover the query and the third is a covering nonclustered index.
SELECT SUM(SomeValue) FROM TestingRangeQueries WHERE ID BETWEEN 20000 and 200000 -- 180 001 rows, 18% of the table
I’m going to run the same range scan query three times, each with an index hint so that SQL will use the three different indexes, regardless of which one it thinks is best.
First up, the clustered index.
As expected, we get a clustered index seek (the predicate is SARGable) and a stream aggregate.
Table ‘TestingRangeQueries’. Scan count 1, logical reads 12023, physical reads 0.
SQL Server Execution Times:
CPU time = 94 ms, elapsed time = 110 ms.