Using an index when leading column is not in the predicate 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.
e.g.
CREATE TABLE x (a int, b int, c int, CONSTRAINT y PRIMARY KEY CLUSTERED (a ASC, b ASC))

SELECT * FROM x WHERE b = 1

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
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 &amp; 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 &quot;find all words in the dictionary where the second letter is 'a'&quot;. You can't answer that with a single seek. Your second query is like asking for &quot;words starting 'aa'&quot; 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 (&gt; a decade or two), as does SQLite, IIRC both call the operation a &quot;skip scan&quot;. 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.