SQL SCANS AND SEEKS

http://itknowledgeexchange.techtarget.com/sql-server/back-to-basics-whats-the-difference-between-a-scan-and-a-seek/

There are a few basic operations which SQL will perform when looking for the data that you need. Here they are listed in the order of worst to best.

Table Scan OR Clustered Index Scan (Essentially Same)
Index Scan
Clustered Index Seek
Index Seek


The basic rule to follow is Scans are bad, Seeks are good.

When SQL Server does a scan it loads the object which it wants to read from disk into memory, then reads through that object from top to bottom looking for the records that it needs.

When SQL Server does a seek it knows where in the index that the data is going to be, so it loads up the index from disk, goes directly to the part of the index that it needs and reads to where the data that it needs ends. This is obviously a must more efficient operation than a scan, as SQL already knows where the data is that it is looking for.

When SQL Server is looking for your data probably one of the largest things which will make SQL Server switch from a seek to a scan is when some of the columns are you looking for are not included in the index you want it to use. Most often this will have SQL Server fall back to doing a clustered index scan, since the Clustered index contains all the columns in the table. This is one of the biggest reasons (in my opinion at least) that we now have the ability to INCUDE columns in an index, without adding those columns to the indexed columns of the index. By including the additional columns in the index we increase the size of the index, but we allow SQL Server to read the index, without having to go back to the clustered index, or to the table it self to get these values.

We’ll look at this more shortly when we look at execution plans.