September 11, 2008 at 2:47 am
Hi,
I have a table with 3 columns.
Table Name MyTable
Columns are ID, Name, Address.
There are two scenario I want to draw.
1. I am creating an non-cluster index on ID, Name.
2. I am creating an non-cluster index on ID and covering Name.
For both the scenario I am not getting any difference in the execution plan.
So what is the purpose / exact utilization scope of the covering index?
Thanks in advance.
Regards
Arijit
September 11, 2008 at 2:52 am
Could you include you query? Is this the only query that will be executed or are there other queries?
- Andras
September 11, 2008 at 5:17 am
A covering index is an index that contains all the columns that a query references. An index may be covering for one query and not for another.
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
September 11, 2008 at 2:44 pm
You're misusing the word "covering" here. I think you mean "including".
A column that's "included" in an index is kept at the bottom level of the index, but not in the upper levels of it. That makes it good for "select" and less useful for "where" or "join".
Is "include" what you actually mean?
- 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
September 11, 2008 at 7:23 pm
A covering index just means that the query does not have to go to the table for any attribute values. The attributes can either be part of the index, or just part of the included attributes.
The more you are prepared, the less you need it.
September 11, 2008 at 10:10 pm
Thanks All,
It is really making sence to me. Thanks again for your grest advices.
Regards
Arijit
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply