THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Paul Nielsen

Stupid Index Seek Tricks - using Included Columns

Usually, we think of index seeks (a possible Query Execution Plan operation) as using the b-tree index to pick out a row and then quickly pass that row to the next operation. But the index seek has special powers: when the conditions are right, it can examine non-key columns and filter based on those values inside the index seek operation.

When this happens the index seek properties will have two predicates – a seek predicate (which details the b-tree portion of the seek), and a predicate (with details about the additional filter using non-key columns).

You can see this behavior with the following code:

USE AdventureWorks2008


DROP INDEX Production.WorkOrder.IX_WorkOrder_ProductID



  ON Production.WorkOrder (ProductID)

  INCLUDE (StartDate)


SELECT WorkOrderID, StartDate

  FROM Production.WorkOrder

  WHERE ProductID = 75

    AND StartDate = '2002-01-04'


Published Thursday, August 14, 2008 8:44 PM by Paul Nielsen
Filed under: , ,


No Comments
New Comments to this post are disabled

About Paul Nielsen

Paul Nielsen believes SQL is the romance language of data. As such he’s a hands-on database developer, Microsoft SQL Server MVP, trainer, and author of SQL Server Bible series (Wiley). As a data architect, he developed the concepts of Smart Database Design and Nordic – an open source O/R dbms for SQL Server. He lives in Colorado Springs.

This Blog



news item test
Privacy Statement