May 31, 2007 at 4:07 am
Hi
The training book 70-431 says about non-clustered index: "Unlike a clustered index, in a non-clustered index the leaf level contains a pointer to the data instead of the actual data."
Next page: "If the query needs to return data from only columns within an index, it does not need to access the data pages of the actual table."
Sounds like a conflict, then a covered index can be also only non-clustered. What now is a covered index? Should there be defined columns by include?
Thanks, Jan
May 31, 2007 at 4:15 am
Jan
A covering index is a covering index with respect to a particular query. So if your table contains columns a, b, c, d and f, and you have a non-clustered index on a, b and c, and your query is SELECT b, c FROM MyTable WHERE a = 10 then the results of this query can be returned from the index alone, without going to the table itself. The index is said to be a covering index for that query.
Included columns are new in SQL Server 2005, but covering indexes were around in SQL Server 2000 and before, so you don't need to define any included columns.
Hope that helps
John
May 31, 2007 at 5:05 am
So then a non-clustered index holds data, unlike in the book explained?
May 31, 2007 at 6:04 am
Jan
Yes, but only the data in the indexed columns. Going back to my example, if you wanted SELECT d, e, f FROM MyTable WHERE a = 10, the index would contain only pointers to the rows where a = 10, and you would need to go to the table itself to get the data from columns d, e and f.
John
June 4, 2007 at 9:10 am
it's like the index in a non-fiction book
say you want to find out about replication, you look in the index and go right to the page instead of looking at every page
June 4, 2007 at 9:36 am
That's helpful, but it's not a perfect analogy. The very fact that you have to go to the page to get the data means that it isn't a covering index for the query you have.
Suppose you have a street atlas, at the back of which is an index of streets. The index lists street name, town, page and grid reference. Now suppose you just need to find out which towns have a street called High Street. You simply go to the index and read off all the towns listed next to High Street. You don't need to go to any of the map pages.
John
June 6, 2007 at 2:49 pm
It seems to me that the analogies are more difficult to understand than what they are meant to help explain. It seems the disconnect is simple, someone is mistakingly thinking that there's a difference between the data in the "data pages" and the data in the index. There isn't, it's all just data. An index (non-clustered) only contains part of the data in the table rather than all of the data in the table. So if I have the following table:
| First Name | Last Name | Phone |
| John | Smith | 555-555-1212 |
| Joe | Franks | 555-555-1212 |
| Sue | Jones | 555-555-1212 |
| Michelle | White | 555-555-1212 |
| Jan | Black | 555-555-1212 |
| Mike | Todd |
A non-clustered index on the last name and phone number would contain:
| Smith | 555-555-1212 |
| Franks | 555-555-1212 |
| Jones | 555-555-1212 |
| White | 555-555-1212 |
| Black | 555-555-1212 |
| Todd | 555-555-1212 |
As you can see the data is the same in the index as it is in the table with respect to the columns being indexed. Any query which only needs the last name and phone number would be "covered" by the non-clustered index. Meaning SQL Server could use the data retrieved from the index to satisfy the query rather that follow the pointers in the index back to the heap or clustered index to get the remaining data.
June 7, 2007 at 6:59 am
yeah - very simply a "covered" index contains all the data required by the query so that the index satisfies the query without reference to the table. Covered indexes work with specified column lists, if your query is select * then the best you can do is make the index cover the where clause ( and order/group ) then the index will use the pointer to get the entire row(s) from the table.
Kalem Delaney and Kimberly Tripp have excellent articles ( and courses ) on this subject, check out their web sites.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
June 14, 2007 at 2:10 pm
... never good practice to use a SELECT * anyway. Do you know how many applications have broken because the developer used an SELECT * and someone came along and changed the table structure?
The practice I've always used is to be very discreet with what I select out of tables.
Thanks Colin...
Kurt
DBA
RHWI, Inc
Poughkeepsie, NY
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply