THE SQL Server Blog Spot on the Web

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

Paul Nielsen

www.SQLServerBible.com

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

 

CREATE INDEX 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: , ,

Comments

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

Syndication

News

news item test
Privacy Statement