Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Index Seek vs Index Scan in SQL Server [closed]

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.

like image 843
Ram Avatar asked Nov 08 '16 12:11

Ram


People also ask

Which is better index scan or index seek?

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.

What is index seek in SQL Server?

The Index Seek operator uses the structure of a nonclustered index to efficiently find either single rows (singleton seek) or specific subsets of rows (range seek). (When SQL Server needs to read single rows or small subsets from a clustered index, it uses a different operator: Clustered Index Seek).

What is the difference between table scan and index scan?

Table scan means iterate over all table rows. Index scan means iterate over all index items, when item index meets search condition, table row is retrived through index. Usualy index scan is less expensive than a table scan because index is more flat than a table.

What does index scan mean?

An index scan occurs when the database manager accesses an index to narrow the set of qualifying rows (by scanning the rows in a specified range of the index) before accessing the base table; to order the output; or to retrieve the requested column data directly ( index-only access ).


2 Answers

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

like image 132
backtrack Avatar answered Sep 19 '22 15:09

backtrack


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

like image 20
mansi Avatar answered Sep 22 '22 15:09

mansi