May 4, 2010 at 3:27 am
Hi,
how it will be helpful to the query by covering/include column indexes, what is the structure fro these type of indexes ( internal process)
thanks,
🙂
May 4, 2010 at 3:50 am
Covering index is an index encompassing every column on the table....
will have to check Include column index
For Include column index:
http://msdn.microsoft.com/en-us/library/ms190806.aspx
Raunak J
May 4, 2010 at 6:09 am
Whoa! A covering index is not a index that includes every column in the table. That's wrong.
A covering index is an index that provides all the columns needed by a particular query. Let's take this table:
CREATE TABLE dbo.a
(
id INT ,
val NVARCHAR(50) ,
Updatedate DATETIME
)
If I put a nonclustered index on the 'id' column and then run a query like this:
SELECT id
FROM dbo.a
WHERE id = 42
Then that index will be covering. But if I run this query:
SELECT id
,val
FROM dbo.a
WHERE id = 42
Then that index will not be covering. The engine will need to go to, in this case, the heap to retrieve the 'val' column. If there was a clustered index on the table, it would go there through a key lookup operation.
This is where the INCLUDE statement comes into play. If I create the index like this:
CREATE INDEX indexa ON dbo.a (id)
INCLUDE (val)
Then that same query is covering again. Now the key for the index is still the same, 'id', but the 'val' column is now included at the leaf level so that when the index is used to find the value for 'id=42' it will also find the 'val' column and can return it without additional work. Notice, the index is covering without including all the columns from the table.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply