August 28, 2008 at 1:42 am
What is covering Index and covering query ?
Is covering index is another kind of index like clustered and non-clustered index ? Under what circumstances they are preferable ?
:hehe:
August 28, 2008 at 2:05 am
It's not a different type of index.
An index is a covering index for a specific query if all of the columns that the query needs are included in the index definition. Examples are always good to explain, so... Assume a table with 4 columns, A, B, C, D
If we take a query
SELECT A, C FROM SomeTable WHERE A=5 AND B=0
An index on A, B, C would be considered covering for that query. So would an index A, B INCLUDE C. So would B, A INCLUDE C
An index on just A, B would not be covering, since the query requires C, but it's not in the index definition.
An index C,B,A would be covering, but not very useful because C is not in the where clause and to do an index seek for that query requires an index with A and B as the leading columns.
Does that help?
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
August 28, 2008 at 5:03 am
Under what circumstances they are preferable ?
It can help to improve the select query performance and avoid deadlocks.
August 28, 2008 at 7:17 am
I got asked this same question as an interview question. I was able to describe a covering index, but I've never heard of a "covering query" before. I'm going to go with "a query that has all the columns of a table", but that's just guesswork.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 28, 2008 at 9:07 am
I would understand a covering query to be the ideal type of query that not all developers use.
It would only returns the columns that are required, instead of
select * from table
it would be
select col1,col2,col99 from table
Regards
GermanDBA
Regards,
WilliamD
August 28, 2008 at 10:45 am
GSquared (8/28/2008)
I got asked this same question as an interview question. I was able to describe a covering index, but I've never heard of a "covering query" before. I'm going to go with "a query that has all the columns of a table", but that's just guesswork.
I sometimes use the term 'covered query' to indicate a query that has a covering index. It could be they meant the same thing. Don't know.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply