Clustered Index Seek Showplan Operator

The Clustered Index Seek operator uses the seeking ability of indexes to retrieve rows from a clustered index. The Argument column contains the name of the clustered index being used and the SEEK:() predicate. The storage engine uses the index to process only those rows that satisfy this SEEK:() predicate. It can also include a WHERE:() predicate where the storage engine evaluates against all rows that satisfy the SEEK:() predicate, but this is optional and does not use indexes to complete this process.

If the Argument column contains the ORDERED clause, the query processor has determined that the rows must be returned in the order in which the clustered index has sorted them. If the ORDERED clause is not present, the storage engine searches the index in the optimal way, without necessarily sorting the output. Allowing the output to retain its ordering can be less efficient than producing nonsorted output.

When the keyword LOOKUP appears, a bookmark lookup is being performed. In SQL Server 2005 Service Pack 2, the Key Lookup operator provides bookmark lookup functionality.

Clustered Index Seek is a logical and physical operator.

Graphical execution plan icon

Examples

The following example deletes a row from a table that has a clustered index. The output of the execution plan shows that the query optimizer uses the Clustered Index Seek operator to retrieve the specified rows.

USE AdventureWorks;
GO
SET NOCOUNT ON;
GO
SET SHOWPLAN_ALL ON;
GO
SELECT Name
FROM Production.UnitMeasure
WHERE UnitMeasureCode BETWEEN 'Each' AND 'Inch';
GO
SET SHOWPLAN_ALL OFF;

The execution plan output of the Clustered Index Seek operator appears below.

PhysicalOp 
-----------------------------------------------------------------------
Clustered Index Seek

Argument
-----------------------------------------------------------------------
OBJECT:([AdventureWorks].[Production].[UnitMeasure].[PK_UnitMeasure_UnitMeasureCode]), 
SEEK:([AdventureWorks].[Production].[UnitMeasure].[UnitMeasureCode] >= 
    CONVERT_IMPLICIT(nvarchar(4000),[@1],0) AND 
    [AdventureWorks].[Production].[UnitMeasure].[UnitMeasureCode] 
<= CONVERT_IMPLICIT(nvarchar(4000),[@2],0)) ORDERED FORWARD

See Also

Tasks

How to: Display an Actual Execution Plan

Reference

Clustered Index Scan Showplan Operator
Key Lookup Showplan Operator

Concepts

Logical and Physical Operators Reference
Clustered Index Structures
Displaying Execution Plans by Using the Showplan SET Options (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
12 December 2006	New content: Added sentence about the Key Lookup operator.
17 July 2006	New content: Added the Examples section.