January 6, 2011 at 4:48 am
i want to know the exact diference between table scan and index scan.i read about 6 to 7 articles but they are all confusing..am not able to drive into a conclusion...plz help me out.
January 6, 2011 at 5:04 am
deepikamm (1/6/2011)
i want to know the exact diference between table scan and index scan.i read about 6 to 7 articles but they are all confusing..am not able to drive into a conclusion...plz help me out.
Simly explained (if I'm not mistaken, because I find some contradictory information on this):
A table scan is where the query engine scans every page of your table to find the rows. It doesn't use any index, so you should avoid this performance-wise if you only need a small subset of the rows.
An index scan is where the query scans the leaf level of the index to find the rows.
If you have a clustered index, an index scan is the same as a table scan. (http://blog.sqlauthority.com/2007/03/30/sql-server-index-seek-vs-index-scan-table-scan/)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 6, 2011 at 5:07 am
An additional resource that says table scan and index scan are not the same (as opposed of the article of Pinal Dave):
http://www.sql-server-performance.com/faq/graphical_query_pla_%20optimizer%20_p1.aspx
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 6, 2011 at 5:37 am
Koen (da-zero) (1/6/2011)
If you have a clustered index, an index scan is the same as a table scan.
If you have a clustered index, then a clustered index scan is equivalent to a table scan. Index scan implies nonclustered index. They're not exactly the same, but they're similar enough for most parties. Both (table scan and clustered index scan) read every single data page of a table. The clustered index scan will also read the upper levels of the clustered index)
You will only see the table scan operator when the base table is a heap (no clustered index)
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
January 6, 2011 at 5:41 am
If I understand correctly, one difference between a clustered index scan and a table scan is the data retrieved via a clustered scan is returned ordered, using the clustered index path, whereas a table scan simply scans the linked list of unordered pages and the rows are not returned in order. This would effect how the optimizer processes the rows later in the query plan.
January 6, 2011 at 5:44 am
Koen (da-zero) (1/6/2011)
An additional resource that says table scan and index scan are not the same (as opposed of the article of Pinal Dave):http://www.sql-server-performance.com/faq/graphical_query_pla_%20optimizer%20_p1.aspx
There's a fair bit of incorrect information in there, especially in the discussion of seeks vs scans and reasons for both.
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
January 6, 2011 at 5:45 am
ziller (1/6/2011)
one difference between a clustered index scan and a table scan is the data retrieved via a clustered scan is returned ordered, using the clustered index path, whereas a table scan simply scans the linked list of unordered pages and the rows are not returned in order.
A clustered index scan can return the data ordered by the clustering key, but it's also perfectly possible for it to return the data in no particular order.
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
January 6, 2011 at 6:07 am
GilaMonster (1/6/2011)
A clustered index scan can return the data ordered by the clustering key, but it's also perfectly possible for it to return the data in no particular order.
If the data is not part of the clustered key, right. I guess I'm thinking more that the data is retrieved (accessed) in SOME order, vs no order at all, even though that ordering may not be used.
January 6, 2011 at 6:14 am
ziller (1/6/2011)
If the data is not part of the clustered key, right.
You mean if SQL uses another index? Index scan rather than clustered index scan?
I guess I'm thinking more that the data is retrieved (accessed) in SOME order, vs no order at all, even though that ordering may not be used.
It can be returned in no order whatsoever. Parallelism or allocation order 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
January 6, 2011 at 6:38 am
GilaMonster (1/6/2011)
Koen (da-zero) (1/6/2011)
An additional resource that says table scan and index scan are not the same (as opposed of the article of Pinal Dave):http://www.sql-server-performance.com/faq/graphical_query_pla_%20optimizer%20_p1.aspx
There's a fair bit of incorrect information in there, especially in the discussion of seeks vs scans and reasons for both.
Never trust the internet, apparently.
Thanks for the explanation.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 6, 2011 at 6:46 am
GilaMonster (1/6/2011)
You mean if SQL uses another index? Index scan rather than clustered index scan?
No, I was thinking if the columns needed were not part of the index key, they would not be returned in any way sorted.
It can be returned in no order whatsoever. Parallelism or allocation order scan.
Isn't an allocation order scan the same as a table scan (ie, they both use the IAM to access the pages)? I hadn't even thought about parallelism...
January 6, 2011 at 6:54 am
ziller (1/6/2011)
No, I was thinking if the columns needed were not part of the index key, they would not be returned in any way sorted.
Wouldn't change a thing. If SQL does return the results sorted (which, to re-emphasise, it is not guaranteed to do), it would be by the index key of the index used, which columns it reads from the index is irrelevant.
Isn't an allocation order scan the same as a table scan (ie, they both use the IAM to access the pages)? I hadn't even thought about parallelism...
They do both use the IAM. The operator will still be a clustered index scan though, not 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
January 6, 2011 at 7:22 am
GilaMonster (1/6/2011)
Wouldn't change a thing. If SQL does return the results sorted (which, to re-emphasise, it is not guaranteed to do), it would be by the index key of the index used, which columns it reads from the index is irrelevant.
Right... No, I was thinking you meant the 'data returned in no particular order' was referring to columns that were not part of the key. Like if the clustered index was used to access the data, for whatever reason (ie, a where clause or join), but the programmer only wanted to return columns that were NOT part of the key, those would not be sorted. I wasn't aware that a Clustered Index Scan could return data to the next iterator NOT sorted by the clustered index key. Thanks...
January 6, 2011 at 9:47 pm
what is the conclusion guys?
January 6, 2011 at 10:50 pm
deepikamm (1/6/2011)
what is the conclusion guys?
About what? Your original question (which has been very well explained on this thread) or whether or not you can guarantee an ordered result set in the presence of a clustered index scan (which has also been very well explained on this thread)? 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply