SQL Server Index Seek Nonclustered

Hi Geeks,

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

NCISeek

NCISeekToolTip

   

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.

Happy learning!

Regards,

Kanchan

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook | Follow me on TwitterFollow me on FaceBook

   

About Kanchan Bhattacharyya

Kanchan is an astute IT professional, a seasoned SQL Database Administrator with 13+ years of industry experience. A calculated risk taker with deep technical knowledge and has gained proficiency in database consulting and solutions across different environments. Kanchan holds MBA degree in IT and an International Executive MBA in Project Management. He is very passionate about SQL Server and holds MCSE (Data Platform), MCSA – SQL 2012, MCITP – SQL 2008 certifications. Currently he is focusing on cloud and latest releases of SQL Server. When not working, Kanchan likes to spend his time reading on new technical developments specifically on SQL Server and other related technologies.

View all posts by Kanchan Bhattacharyya →

Leave a Reply

Your email address will not be published.