Index Scan vs Index Seek

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » General » Index Scan vs Index Seek

13 posts, Page 1 of 2

1 2 »»

Index Scan vs Index Seek

Rate Topic

Display Mode

Topic Options

Author

Message

Ananth-431739

Posted Friday, December 07, 2007 5:44 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, August 08, 2012 8:50 AM
Points: 71, Visits: 213

What is the difference between index seek and index scan?

Post #430635

Lowell

Posted Friday, December 07, 2007 6:35 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 11,631, Visits: 27,699

an index seek is better...an index is stored in order. so if a query tries to look for say, PROD_ID=7, it knows to use the index to SEEK the PROD_ID of 7, so it's the fastest method of all.

an INDEX SEEK might be something like it's looking for PRODUCTNAME="BANANAS", and knows that following the index to find(seek) to get the leaf of information that is related to what it is looking for is the next best method.

if there was a different index on PRODUCTNAME, it might use that instead, but it will try to use the clustered index(in this example on my primary key PROD_ID) most of the time.

in this example, dropping the clustered index and recreating it to have two columns, PROD_ID and PRODUCTNAME might increase performance if there's alot of queries that include the product name

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #430651

Grumpy DBA

Posted Friday, December 07, 2007 6:51 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, May 13, 2013 7:38 AM
Points: 4,149, Visits: 611

Kathi Kellenberger has a really good article on index basics, which illustrates the difference between an index seek and an index scan. Take a look:

[url=http://www.sqlteam.com/article/sql-server-indexes-the-basics][/url]

Post #430660

Matt Miller (#4)

Posted Friday, December 07, 2007 6:51 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:20 PM
Points: 6,998, Visits: 13,947

A seek means that you are looking for specific value(s) and the index provides you with the best way to do it. This also applies to specific ranges of data.As Lowell described it's very fast.

A scan indicates that the entire table/index is read in. A scan isn't always bad, because it it might just mean that the data is organized the way your query needs it (meaning - there's no data to exclude, and the data is organized the way the query optimizer needs it). It might however also mean that there IS a criteria, but that can't be satisfied using SEEKS. Because of the less specific nature of the operation, it tends to be a little slower, and thus often described as "not as good". It may however be the best operation specific to what you ask for.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Post #430661

Ananth-431739

Posted Monday, December 10, 2007 4:25 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, August 08, 2012 8:50 AM
Points: 71, Visits: 213

whether index scan reads the data from index or table? if it reads from the table then there is no advantage of this index scan because , it is reading the data in the normal manner that is from the table and performance will not be improved......
If i'm misleading, correct me!!!!!

Post #431248

Minaz

Posted Monday, December 10, 2007 5:31 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, April 21, 2013 10:57 PM
Points: 535, Visits: 1,423

Read Indexes from SQL Server BOL.
Links :
http://msdn2.microsoft.com/en-us/library/aa174541(SQL.80).aspx

"More Green More Oxygen !! Plant a tree today"

Post #431264

GilaMonster

Posted Monday, December 10, 2007 6:12 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:56 AM
Points: 37,709, Visits: 29,964

Ananth (12/10/2007)

An index scan is a complete scan of all the pages in a non-clustered index.
A clustered index scan is a complete scan of all pages in a clustered index (ie, the table itself)
Neither scan uses the b-tree structure of the index, but just reads the leaf pages in order, using each page's reference to the next in the chain.

An index seek is a seek through the b-tree structure of a non-clustered index, from the root down to the leaf.
A clustered index seek is a seek through the b-tree structure of a clustered index, from the root down to the leaf.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #431277

AppSup_dba

Posted Wednesday, May 05, 2010 1:27 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 6:11 AM
Points: 125, Visits: 499

Is there any difference between table scan and index scan?

Cheers,
Ankur

Post #915870

GilaMonster

Posted Wednesday, May 05, 2010 1:44 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:56 AM
Points: 37,709, Visits: 29,964

Yes.

A table scan (only present when you don't have a clustered index) is a scan of the data pages of the table.
An index scan reads the leaf pages of the index. If it's a nonclustered index, it'll be a lot fewer pages than the table.
A clustered index scan (only present if there's a clustered index) reads the leaf pages of the clustered index, which are the data pages of the table. It's virtually the same same as a table scan. There are some minor differences as to how SQL does the scan, but it is the entire table, like with a table scan.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #915876

AppSup_dba

Posted Wednesday, May 05, 2010 1:55 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 6:11 AM
Points: 125, Visits: 499

got the difference .. thankx... Smile

Cheers,
Ankur

Post #915879

« Prev Topic | Next Topic »

13 posts, Page 1 of 2

1 2 »»

Permissions