Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Index Scan vs Index Seek


Index Scan vs Index Seek

Author
Message
Ananth-431739
Ananth-431739
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 266
What is the difference between index seek and index scan?
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14984 Visits: 39008
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Grumpy DBA
Grumpy DBA
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5984 Visits: 1845
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]
Matt Miller (#4)
Matt Miller (#4)
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7664 Visits: 18097
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?
Ananth-431739
Ananth-431739
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 266
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!!!!!
Minaz Amin
Minaz Amin
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 1750
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"
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47412 Visits: 44399
Ananth (12/10/2007)
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!!!!!


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, MVP, M.Sc (Comp Sci)
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


AppSup_dba
AppSup_dba
SSC Veteran
SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)

Group: General Forum Members
Points: 214 Visits: 716
Is there any difference between table scan and index scan?

Cheers
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47412 Visits: 44399
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, MVP, M.Sc (Comp Sci)
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


AppSup_dba
AppSup_dba
SSC Veteran
SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)

Group: General Forum Members
Points: 214 Visits: 716
got the difference .. thankx...:-)

Cheers
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search