Let us look at Clustered Index Seek operator today. The statement below shows an example of SQL Server Index Seek (clustered) and of course database is our dearest AdventureWorks2012.
WHERE Address.AddressID = 14918
An index seek does not scan entire index, instead it navigates the B-tree structure to find one or more records quickly. An Index Seek be it clustered or non-clustered (we will cover in future post) takes place when SQL Server query optimizer able to locate an appropriate index to fetch required records that is, it sends an instruction to SQL engine to look up values based on the index keys.
A clustered index stores key structure as well as the data itself. In our example, we have a clustered index seek operation against Person.Address table and index used here is PK_Address_AddressID. ToolTip shows Ordered property is true indicating data is ordered by SQL Server query optimizer.
In tomorrow’s post we will have a comparison on how SQL Server Clustered index Seek is more effective than scans.