Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Index Scan vs Index Seek Expand / Collapse
Author
Message
Posted Friday, December 7, 2007 5:44 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 12:37 AM
Points: 71, Visits: 225
What is the difference between index seek and index scan?
Post #430635
Posted Friday, December 7, 2007 6:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 1:42 PM
Points: 12,905, Visits: 32,157
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
Posted Friday, December 7, 2007 6:51 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 7:54 AM
Points: 4,690, Visits: 636
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
Posted Friday, December 7, 2007 6:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:46 PM
Points: 7,104, Visits: 15,425
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
Posted Monday, December 10, 2007 4:25 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 12:37 AM
Points: 71, Visits: 225
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
Posted Monday, December 10, 2007 5:31 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 3:34 AM
Points: 549, Visits: 1,554
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
Posted Monday, December 10, 2007 6:12 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:38 AM
Points: 40,157, Visits: 36,543
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 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
Posted Wednesday, May 5, 2010 1:27 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 12:00 AM
Points: 210, Visits: 650
Is there any difference between table scan and index scan?

Cheers
Post #915870
Posted Wednesday, May 5, 2010 1:44 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:38 AM
Points: 40,157, Visits: 36,543
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
Posted Wednesday, May 5, 2010 1:55 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 12:00 AM
Points: 210, Visits: 650
got the difference .. thankx...

Cheers
Post #915879
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse