Clustered Index Seek Trivia in SQL Server

Looking for comprehensive, deep-dive training on SQL Server Performance Tuning? Explore SQLMaestros All-In-One Performance Tuning Bundle. 5 courses at a highly discounted price. bit.ly/sqlmaestrosallinone
The bundled content focuses on internals, troubleshooting, query tuning, performance optimization, tips, tricks, and loads of content from the real world.

[@simondungeon1218]

Great, hope we get more informational videos like this.

[@davidegrasselli9689]

The reason is related to index statistic. SQL Server store the first column of a composite index in the statistic histogram. So if in the query the first column is missing the query optimizer is not able to perform the better operation (seek) and instead performs a scan

[@shubhamsingh8044]

Since the first column in the index key defines the primary ordering of the data, a query filtering or sorting on the second column may not benefit from a clustered index seek because the index is primarily ordered by the first column. The query optimizer may choose alternative access methods, such as a full index scan or a table scan, depending on factors such as data distribution and cardinality.

[@jagannathan7083]

On multicolumn index - Optimizer considered left most index to execute the query

[@gladiator2003]

Where is the members only section on CZcams located for this channel? Thanks!

[@robh115]

The reason why it does an index scan is because SS can’t seek on the second column of a composite index. However, it can seek on the first column of composite index.