December 7, 2011 at 8:12 am
I'm having a major brain fail this a.m. I'm trying to explain the difference between a covering non-clustered index and a non-clustered index using INCLUDE to a non-DBA coworker and completely screwed the pooch. Now I have myself confused.
So here I am looking for help. Does anyone have a babblefish or something that knows how to give a non-technical, bare bones, explanation of how the two differ from each other (without using SQL Speak)?
December 7, 2011 at 8:24 am
The name 'covered index' means the index covers the select list of the query without needing to even hit the table.
A pure covered index means all covered columns exist in all index nodes and are factored into the index organization. If the columns are included columns, they exists in the leaf nodes and also don't affect the organization of the index.
The probability of survival is inversely proportional to the angle of arrival.
December 7, 2011 at 8:29 am
A covering index is not a particular type of index. An index is said to be covering for a query if that index contains all the columns that the query needs. Doesn't matter if they're in the key or include.
An index can't be said to be covering or not covering. It can only be said to be covering or not covering for a particular query
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
December 8, 2011 at 5:34 am
Ahha. No wonder I confused myself. I keep hearing the term "Covering Index" thrown around at SQL Saturdays as if an index is always covering. EDIT: And as if Covering Index was different from NC using INCLUDE (and I had just talked myself into saying they were the same as I tried to explain it to a coworker).
Thanks for the clarification.
December 8, 2011 at 9:29 am
Let's take an example to be absolutely clear.
CREATE INDEX idx_Test ON SomeTable (ColA);
SELECT ColA FROM SomeTable;
SELECT ColB FROM SomeTable WHERE ColA = 137;
The given index is covering for the first query (it has all the columns the query needs). It is seekable for query 2, but it's not covering (it doesn't have ColB anywhere).
To make it covering for query 2, I need to add the column ColB. Doesn't matter (for the purposes of making it covering) whether ColB is added in the key or in the include.
In fact, this index is covering for both of those queries
CREATE INDEX idx_Test2 ON SomeTable (ColB, ColA);
Query 2 can't seek on that, because ColA is not the left-most column, but it is covering.
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
December 8, 2011 at 11:04 am
That's perfect, Gail. Thank you again.
December 8, 2011 at 11:48 am
GilaMonster (12/8/2011)
Let's take an example to be absolutely clear.
CREATE INDEX idx_Test ON SomeTable (ColA);SELECT ColA FROM SomeTable;
SELECT ColB FROM SomeTable WHERE ColA = 137;
The given index is covering for the first query (it has all the columns the query needs). It is seekable for query 2, but it's not covering (it doesn't have ColB anywhere).
To make it covering for query 2, I need to add the column ColB. Doesn't matter (for the purposes of making it covering) whether ColB is added in the key or in the include.
In fact, this index is covering for both of those queries
CREATE INDEX idx_Test2 ON SomeTable (ColB, ColA);Query 2 can't seek on that, because ColA is not the left-most column, but it is covering.
So, for the feeble minded Jared, what is the difference in function by having a column in the key or the include?
Jared
EDIT: Fixed quote 😛
Jared
CE - Microsoft
December 8, 2011 at 12:01 pm
p-nut (12/8/2011)
So, for the feeble minded Jared, what is the difference in function by having a column in the key or the include?Jared
Gail feel free to correct me if I'm wrong...
Columns in the key end up enlarging the index because they are saved in a different level of the index page, while columns in the INCLUDE end up in a different page with a pointer that enables the index to technically be larger than it should be able to be.
BOL
You can add nonkey columns to the leaf level of the nonclustered index to by-pass existing index key limits, 900 bytes and 16 key columns, and execute fully covered, indexed, queries.
December 8, 2011 at 12:03 pm
Brandie Tarvin (12/8/2011)
Columns in the key end up enlarging the index because they are saved in a different level of the index page
Maybe, but I can't really understand what you're saying. Different level than what?
while columns in the INCLUDE end up in a different page with a pointer that enables the index to technically be larger than it should be able to be.
Nope. Not at all. (unless you're talking about BLOB data types)
Want to try again or want an explanation? (hint I'm pretty sure I covered this in my indexing articles here)
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
December 8, 2011 at 12:07 pm
Let me try again, but it's going to be tomorrow. I've got a work emergency.
December 8, 2011 at 12:16 pm
GilaMonster (12/8/2011)
(hint I'm pretty sure I covered this in my indexing articles here)
Maybe for the less patient you can point me to those articles?
Jared
Jared
CE - Microsoft
December 8, 2011 at 12:49 pm
Top 3 here: http://www.sqlservercentral.com/search/?q=Introduction+to+Indexes
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
December 8, 2011 at 12:50 pm
Thanks Gail!
Jared
Jared
CE - Microsoft
December 9, 2011 at 7:58 am
Someone correct me if I'm wrong: columns that are part of the index definition can be used to filter a query based on the WHERE clause. Columns that are merely included are "along for the ride" -- they can be returned with a query (eliminating the need for a bookmark lookup, if all the columns requested are included; i.e., the index is "covering" for the query), but they aren't used to filter a query by the WHERE clause.
Rich
December 9, 2011 at 8:02 am
rmechaber (12/9/2011)
Someone correct me if I'm wrong: columns that are part of the index definition can be used to filter a query based on the WHERE clause. Columns that are merely included are "along for the ride" -- they can be returned with a query (eliminating the need for a bookmark lookup, if all the columns requested are included; i.e., the index is "covering" for the query), but they aren't used to filter a query by the WHERE clause.Rich
They can be used to filter, they just can't be seeked (because they are not sorted).
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply