I recently asked a question about the best indexing strategy for a table that must record events, in order, against a GUID. The GUID is a nuisance but it's unavoidable because it's an FK to elsewhere.
In terms of the queries I want to do they are essentially of the form SELECT TOP N SomeColumn FROM t WHERE GuidColumn = 'someguid' ORDER BY AutoIncInt DESC
I've done some experimentation and a good tradeoff approach seems to be:
- Create the
AutoIncInt
column on the table as the unique clustering key - Create a nonunique nonclustered index on
GuidColumn
, andINCLUDE SomeColumn
At first I had the nonclustering index on SomeGuid ASC, AutoIncInt DESC
, thinking it would help the ORDER BY AutoIncInt DESC
part of the query but the plan remained the same when I removed it – no sort operation was needed and the query just hit the NC index, did a top and a select; it seems that SQLServer is somehow smart enough to know, even when AutoIncInt is not cited in the nonclustering index at all, there's a way to easily get the data out in order / easily calculate which last N rows are needed for the TOP
How does this mechanism work in practice? Does the nonclustering index have an implicit order thanks to referring to the clustering index, such that SQLS can quickly and easily deduce which N index nodes I want the SomeColumn from?
Best Answer
Each leaf node row in a SQL Server b-tree index uniquely identifies a single row in the table by a unique key. In a non-clustered index, this unique key consists of the declared key columns plus the row locator. The row locator is the clustered index key (including uniqueifier for when the CI key value is not unique). With a heap (no clustered index on the table), the row locator is the physical location of the row (file,page,slot).
The
DBCC PAGE
output of this demo script shows bothGuidColumn
andAutoIncInt
as index keys even thoughAutoIncInt
was not specified:.Results:
This index architecture has positive performance implications for your query because the
AutoIncInt
column is not only implicitly included in the non-clustered index as the row locator, it is also part of the unique key and thus ordered. This allows the plan for your query to use an index seek for theGuidColumn
WHERE
predicate plus a backwards scan for theORDER BY...DESC
clause.Below is an excerpt from the actual query plan shows a backwards scan of the index. The resultant non-clustered index will be the same regardless of whether
AutoIncInt
is explicitly specified as a key column.