Search code examples
sql-serverindexingstatisticssql-execution-plan

Included columns are used in Seek operator

Consider a table with two non clustered index, and query:

1 INDEX_1 on table (column1, column2, column3)
2 INDEX_2 on table (column1) INCLUDED (column2, column3)

    SELECT column3
    FROM   table
    WHERE  column1 = 100 columnn2 =  100

For some reason, SQL server uses INDEX_2. Execution plan for boths indexes are the same(besides object in Seek operator) Also, the logical reads times are the same for boths. How is it possible to perform seek operator with such conditions on INDEX_2, if index is not sorted by column2

Solution

  • How is it possible to perform seek operator with such conditions on INDEX_2, if index is not sorted by column2

    An Index Seek seeks to a location in an index, but then may scan from that point along the leaf-level pages, which are stored in a doubly-linked list following the index sort order.

    So it's really more of a Seek+Scan. You'll see the same plan when selecting from a clustered index, because an index with included columns is kind of a miniature clustered index.

    eg in Adventureworks

    SELECT SalesOrderID, *
FROM Sales.SalesOrderDetail
WHERE SalesOrderID = 43892
  AND ProductID = 758

    will give you an Index Seek that reads multiple rows:

    enter image description here

    Seeking to the first page that contains a row for that SalesOrderID, and then scanning until it locates all the rows with that ProductID. When the operator finds a page with a different SalesOrderID, it's done.

    But If (column1, column2, column3) is the clustered index key, the query would be a single-row Seek. All non-unique non-clustered indexes will have all the clustered index columns added as index key columns, and will physically be stored as a unique index.