48

Please explain the difference between Index Scan and Index Seek in MS SQL server with an sample example, since it will be helpful to know, what's the real use of it. Thanks in advance.

Ram
  • 527
  • 1
  • 4
  • 7
  • 4
    Possible duplicate of [SQL Server Plans : difference between Index Scan / Index Seek](http://stackoverflow.com/questions/1136524/sql-server-plans-difference-between-index-scan-index-seek) – dashdashzako Nov 08 '16 at 12:44

2 Answers2

55

Here is the text showplan (slightly edited for brevity) for this query using a scan:

|–Table Scan(OBJECT:([ORDERS]), WHERE:([ORDERKEY]=(2)))

The following figure illustrates the scan:

enter image description here

Here is the text showplan for the same query using a seek:

 |–Index Seek(OBJECT:([ORDERS].[OKEY_IDX]), SEEK:([ORDERKEY]=(2)) ORDERED FORWARD)

enter image description here

have a look on this SQL Server Plans : difference between Index Scan / Index Seek

Community
  • 1
  • 1
backtrack
  • 7,996
  • 5
  • 52
  • 99
  • So it looks to me like a seek actually stores the records some kind of order. Hense why it knows exactly where those values are in the table? Are they physically stores that way or do they have some kind of pointer to where the record is physically stored? – SnellyCat Jun 26 '17 at 08:02
  • 2
    A seek is an action done on an index and an index is what stores things. It's a bit like looking for information on "dog" in a book. You could start reading at the first page and keep reading until you found the word "dog". Or, you could look in index, find "dog" and get the page number with information about dogs. That's roughly how database indexes work: They are separately maintained, ordered records Except database indexes don't just use simple alphabetical order they have more clever structures like B-trees: https://en.wikipedia.org/wiki/B-tree – RedYeti Jan 07 '22 at 17:08
9

In simple words, An index scan or table scan is when SQL Server has to scan the data or index pages to find the appropriate records. A scan is the opposite of a seek, where a seek uses the index to pinpoint the records that are needed to satisfy the query.

your question is similar to the question which is already posted in stackoverflow get it from below link

#Index scan vs Index seek

you can also get information from below link

#Index scan vs Index seek in brief

Community
  • 1
  • 1
mansi
  • 837
  • 5
  • 12