SQL Server index seek nonclustered uses a non-clustered index to perform search operation. The statement below illustrate a non-clustered index seek operator.
USE [AdventureWorks2012] SELECT Address.AddressID, Address.StateProvinceID FROM Person.Address WHERE Address.StateProvinceID = 10
ToolTip shown in above figure that, non-clustered index IX_Address_StateProvinceID has been used for seek operation.
Note: It depends if SQL Server query optimizer will be able find all the required data in the index and depends on the query and index. In case SQL Server query optimizer is not able to find all required data in a non-clustered index, it might have to look up additional details in clustered key. This operation requires additional I/O and slightly hurts overall performance. We will explore more on this in future posts.