• Article

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, then a bookmark lookup is being performed. In SQL Server 2008, the Key Lookup operator provides bookmark lookup functionality.

Clustered Index Seek is a logical and physical operator.

Clustered index seek operator iconGraphical 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