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.

CIScan

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

CIScanStatisticsIoON

CIScanStatisticsTimeOn

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

CISeek

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

CISeekStatisticsIoON

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