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

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

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
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement