Are there any circumstances when an index could be used when not all the index columns are included in the predicate? In my case the leading column in the index is unique and is part of the unique primary key so I am wondering if SQL can still use the index, as it strikes me it has all the information to know its unique.




This does not use the clustered index (it uses some other NC index). Of course if I specify the leading column it does.

 SELECT * FROM x WHERE a = 1 AND b = 1
  • 4
    Could you please add that other NC index DDL? I think it might be useful to better answer your question.
    – Ronaldo
    Oct 27 '20 at 18:57

In my case the leading column in the index is unique ... it strikes me it has all the information to know its unique.

It does not. That primary key does not guarantee that either a or b are unique, just that all combinations of a & b are. There could be many rows for which b = 1 is true, maybe all of them, maybe none of them. When you search for a specific combination of a and b then it can use the index to do a simple seek.

With that table definition, your first query is like asking "find all words in the dictionary where the second letter is 'a'". You can't answer that with a single seek. Your second query is like asking for "words starting 'aa'" which is easy to answer with that index.

Note though that some database systems are able to perform a skip-search to speed up the first query, which would help here, essentially looking for a=1 and b=1 then a=2 and b=1 then a=3 and b=1 and so on (it isn't quite this but as near to as makes little odds). If operation is supported it may be used if the datatype of a and the selectivity indicated by the index stats suggest it might be appropriate. No version of SQL server supports this operation though. Oracle does unless you have a really old version (> a decade or two), as does SQLite, IIRC both call the operation a "skip scan". Further note that in every circumstance where a skip-scan is better than a full index scan, having an index on the second column to use would be more efficient, often significantly so, meaning that while the feature might make some queries better in databases that are not optimised for them, if you expect such queries you should optimise your table(s) for them by having the extra index even if your DBMS supports skip-searches.

it uses some other NC index

To explain why that particular index is selected and used instead of any other, we would need to know how that index and all the other indexes are defined, and perhaps see the query plan. Also you don't say how the index is used - I assume it was scanned rather than being used for seeks.

  • Db2 also supports skip-scan.
    – Lennart
    Oct 28 '20 at 9:44

Just to make it clear:

SQL Server can scan the index, but not seek.

We should avoid using terminology such as "using" an index. There's a big difference between an index seek and an index scan. But sometimes a non-clustered index scan is better than a table scan (for a clustered table we use the term "clustered index scan" instead of "table scan"). I.e., scan the index and just "jump down to the data" for the rows that matches.

Since your index is a clustered index, an index scan is the same thing as a table scan.

  • An index scan on its own is pretty much always better than a table scan as index entries are going to be smaller than full rows except in very odd circumstances so fewer pages need to be read. Another thing to consider is partial index scans, often seen in SELECT TOP <num> ..., SELECT MAX(SingleColumn) ..., and SELECT MIN(SingleColumn) ... queries/sub-queries which are essentially seeks to the head or tail of the index. Oct 28 '20 at 12:35

I can't see it being used for a predicated query.

It may be used for a scan, however, if the estimated cost were lower than the alternative. For a scan the cost is largely governed by IO. So if two access paths (i.e. indexes) both provide enough information to support the query the optimizer is likely to prefer the one which is narrower.

The clustered index in the question has three columns. I'll define one with two, still with 'b' as the trailing column. I choose 'c' as the leading column simply to distinguish it better from the clustered index. The result is the same if 'a' is the leading column in this new index.

create index IX1 on x(c, b);

A query which can be satisfied from either the clustered index or this new one:

select b from x;

On my box (SQL Server 2017 and 2019) the plan is a scan of IX1.


This is because the order that you specify your columns in the index defines the order of which columns the index will sort the data on first. An index on column (a, b) means the B-Tree that SQL uses to store the indexed data in will first sort by column a then by column b. In other words, column a affects where the data for column b will be stored in an index on column (a, b) because first the data is sorted on column a and then within each node of the tree that contains column a there will be the correlating data of column b, sorted. (See this more details: https://stackoverflow.com/questions/795031/how-do-composite-indexes-work#:~:text=Composite%20indexes%20work%20just%20like,%2C%20then%20b%2C%20then%20c.&text=etc.,by%20the%20second%20one%20etc.)

Long story short, to find column b efficiently you need column a. Now if you defined your index columns in the opposite order such that your index was on (b, a) then that index could be used to efficiently seek out column b even when it's the only column in the predicate of your query.

As Ronaldo commented, knowing what the definition of your nonclustered index that's used is would help us understand why that was chosen over a clustered index scan, but my guess without seeing it is if it's a "nonclustered index seek" operation then that nonclustered index starts with column b or whatever other columns it starts with are also being used as predicates in your query? Regardless, SQL Server thought the nonclustered index was more efficient than doing a clustered index scan.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.