SQL Server Clustered index scan vs Clustered index seek

Dear Friends,

Yesterday and day before we have covered Clustered Index Seek and Clustered index Scan. In today’s post, let us draw a comparison between SQL Server clustered index scan vs clustered index seek.

Let us first reproduce CI Scan by running SELECT statement on Person.Address table.

USE [AdventureWorks2012]

SELECT * FROM Person.Address

CIScan

And now turn on STATISTICS IO and STATISTICS TIME one by one then run same query.

USE [AdventureWorks2012]

SET STATISTICS IO ON

SELECT * FROM Person.Address

CIScanStatisticsIoON

USE [AdventureWorks2012]

SET STATISTICS TIME ON

SELECT * FROM Person.Address

CIScanStatisticsTimeOn

We will now run the same query that we used yesterday to get CI seek operator first for illustration.

USE [AdventureWorks2012]

SELECT Address.AddressID,Address.AddressLine1,Address.City,Address.PostalCode
FROM Person.Address
WHERE Address.AddressID = 14918

CISeek

Time to turn on STATISTICS IO and STATISTICS TIME one by one followed by running same query.

USE [AdventureWorks2012]

SET STATISTICS IO ON

SELECT Address.AddressID,Address.AddressLine1,Address.City,Address.PostalCode
FROM Person.Address
WHERE Address.AddressID = 14918

CISeekStatisticsIoON

USE [AdventureWorks2012]

SET STATISTICS TIME ON

SELECT Address.AddressID,Address.AddressLine1,Address.City,Address.PostalCode
FROM Person.Address
WHERE Address.AddressID = 14918

CISeekStatisticsTimeOn

By looking at the output(s), we can very well understand both logical reads as well as elapsed time is reduced for CI seek operation and is better of the two operators in question.

Note: This is just an illustration and results may vary under different circumstances as per environment.

Happy learning!

Regards,

Kanchan

Like us on FaceBookJoin the fastest growing SQL Server group on FaceBookFollow me on TwitterFollow me on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

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. Required fields are marked *