Seek & Scan in SQL Server

Question

After googling i came to know that Index seek is better than scan.

How can I write the query that will yield to seek instead of scan. I am trying to find this in google but as of now no luck.

Any simple example with explanation will be appreciated.

Thanks

Answer 1

1. Search by the primary key column(s)
2. Search by column(s) with index(es) on them

An index is a data structure that improves the speed of data retrieval operations on a database table. Most dbs automatically create an index when a primary key is defined for a table. SQL Server creates an index for primary key (composite or otherwise) as a "clustered index", but it doesn't have to be the primary key - it can be other columns.

NOTE:

• LIKE '%'+ criteria +'%' will not use an index; LIKE criteria +'%' will

Related reading:

• SQL SERVER – Index Seek vs. Index Scan
• Index
• Which is better: Bookmark/Key Lookup or Index Scan

Answer 2

Extending rexem's feedback:

The clustered index idea for pkeys isn't arbitrary. It's simply a default to make the pkey clustered. And clustered means that values will be physically placed near each other on a Sql Server 8k page thus assuming that if you fetch one value by pkey, you will probably be interested in its neighbors. i don't think it's a good idea to do that for pkeys since they're usually unique but arbitrary identifiers. Better to cluster on more useful data. One clustered index per table btw.

In a nutshell: If you can filter your query on a clustered index column (that makes sense) then all the better.

Answer 3

An index seek is when SQL Server can use a binary search to quickly find the row. The rows in an index are sorted in a particular order, and your query has to specify enough information in the WHERE clause to allow SQL Server to make use of the sorted index.

An index scan is when SQL Server cannot use the sort order of the index, but can still use the index itself. This makes sense if the table rows are very large, but the index is relatively small. SQL Server will only have to read the smaller index from disk.

As a simple example, take a phonebook table:

id int identity primary key
lastname varchar(50)
phonenumber varchar(15)

Say that there is an index on (lastname). Then this query will result in an index seek:

select * from phonebook where lastname = 'leno'

This query will result in an index scan:

select * from phonebook where lastname like '%no'

The analogy with a real life phonebook is that you can't look up people whose name ends in 'no'. You have to browse the entire phonebook.