July 1, 2011 at 4:54 am
Hi all
Indexes have been discussed a numerous time both in this forum and outside this forum but somehow I am not able to figure them out.
Few querries:
1. Say I have a table with 3 int columns a , b & x and a unique clustered index on a.
I write a query like
select a,b,x from #t1 where b=2
The query plan shows that its using a "clustered index scan" to get the result.
Why is the optimiser using the index here? what help does it do? Will it ultimately not be similar to a table scan?
2. A clustered index holds the data and a non-clustered index holds pointer to the data.
what does this mean? In the above example,(1), if i write a query
select a,b from #t1 where a=2, the clustered index table only has info about the column a, to get the value for b , it has to go to the actual table #t1 and find . So the clustered index should have
index key and a pointer to the data row.
The non-clustered index should also have the same right ?
3. What is the difference between Index scan and seek?Will add more info on this .
July 1, 2011 at 6:45 am
There will likely be much better answers to follow, but I'll attempt this one.
1. There is very little difference between a clustered index scan and a table scan. A clustered index scan is just a table scan on a table that has a clustered index. For any given table, you will only get a table scan (for a heap table without a clustered index) or a clustered index scan (if one exists).
2. A clustered index is the default sort order of your data, ordering it by your clustered key. You can only have 1 of them. Your clustered index basically IS the table, as opposed to other indexes which use the clustered key, the index fields and pointers back to the table.
3. The difference between a scan and a seek is that a scan has to read all the rows of a table or index and a seek uses the index data to pick out only the rows you want.
Here is some additional reading:
http://blog.sqlauthority.com/2009/08/24/sql-server-index-seek-vs-index-scan-diffefence-and-usage-a-simple-note/[/url]
http://sqlblog.com/blogs/paul_white/archive/2011/02/17/so-is-it-a-seek-or-a-scan.aspx
July 1, 2011 at 9:12 am
koustav_1982 (7/1/2011)
Why is the optimiser using the index here? what help does it do? Will it ultimately not be similar to a table scan?
A clustered index scan is a table scan. (see next question)
2. A clustered index holds the data and a non-clustered index holds pointer to the data.
So the clustered index should have index key and a pointer to the data row.
The non-clustered index should also have the same right ?
No.
The clustered index IS the table. It's not a separate structure, it's the actual table's data rows with an index structure on top. Hence it can't have a pointer to the data row as it contains the entire data row
3. What is the difference between Index scan and seek?Will add more info on this .
Think of a telephone directory. If you want Mr R Brown, you can go straight to the right entry, because the telephone directory is set up that way. That's a seek.
A scan would be reading every single entry in the telephone directory from beginning to end.
Take a read through this series (3 parts)
http://www.sqlservercentral.com/articles/Indexing/68439/
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
July 1, 2011 at 10:35 pm
# 1. In your example, the optimizer uses the nonclustered index because it is a "covering" index containing all the columns needed to satisfy your query. Although not ordered by column , the nonclustered index contains fewer columns than the clustered index, meaning it takes less I/O to read it from start to finish. The optimizer recognizes that this is a less expensive solution than scanning the entire clustered index for that query. If you added a column to your query that didn't exist in the nonclustered index, you would probably see a table scan as a result.
# 2. Asked and answered.
# 3. Gail is right as usual. But going a little further... an index seek may still result in the "scanning" of a large number of rows. Using Gail's example: If you wanted all the names in the telephone directory following "Brown, an index would let you quickly find your starting point where the "Browns" end. But from then on, it would have to read the rest of the rows in the index.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 4, 2011 at 4:18 am
The Dixie Flatline (7/1/2011)
# 1. In your example, the optimizer uses the nonclustered index because it is a "covering" index containing all the columns needed to satisfy your query. Although not ordered by column , the nonclustered index contains fewer columns than the clustered index, meaning it takes less I/O to read it from start to finish.
Sorry , it did not use a nonclustered index as the table did not have one.
July 4, 2011 at 4:27 am
Think of a telephone directory. If you want Mr R Brown, you can go straight to the right entry, because the telephone directory is set up that way. That's a seek.
A scan would be reading every single entry in the telephone directory from beginning to end.
Take a read through this series (3 parts)
I will read through that series.
Regarding A Distionary please let me know if my below understanding is right:
1.The index at the end , in the last few pages of a Dictionary serves as the nonclustered index.
2.The clustered index is the Dictionary itself
3.Even for a Index Seek, the query has to do scan through the B Tree Structure to get to Brown.
Same applies for a Telephone Directory
July 4, 2011 at 5:23 am
koustav_1982 (7/4/2011)
1.The index at the end , in the last few pages of a Dictionary serves as the nonclustered index.
Yes , its called biblography(indexing)
koustav_1982 (7/4/2011)
2.The clustered index is the Dictionary itself
yes, the original data.
koustav_1982 (7/4/2011)
3.Even for a Index Seek, the query has to do scan through the B Tree Structure to get to Brown.
yes but in clus index case also, there will be B-tree search.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
July 4, 2011 at 8:34 am
koustav_1982 (7/4/2011)
.Even for a Index Seek, the query has to do scan through the B Tree Structure to get to Brown.
It's not a scan. It doesn't start at A and read all the way to Z. With an index you get a b-tree traversal. Please see my series of articles on indexing.
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
July 7, 2011 at 1:02 pm
koustav_1982 (7/4/2011)
The Dixie Flatline (7/1/2011)
# 1. In your example, the optimizer uses the nonclustered index because it is a "covering" index containing all the columns needed to satisfy your query. Although not ordered by column , the nonclustered index contains fewer columns than the clustered index, meaning it takes less I/O to read it from start to finish.Sorry , it did not use a nonclustered index as the table did not have one.
My error, I misread your post.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply