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
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.
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.
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
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.
On multicolumn index - Optimizer considered left most index to execute the query
Where is the members only section on UA-cam located for this channel? Thanks!
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.
Great, hope we get more informational videos like this.