Creating Indexes with the include keyword query
-
Before I start let me insert the "I'm not a DBA.. Please be patient." disclaimer in.
Thank you.
Now I've been given a set of indexes to add to one the dbs my department manage. They are generated by some optimisation tool. Looking at the indexes they seem like strange choices.
For example:
CREATE INDEX missing_index_2598 ON [mydb].[dbo].[mytable] ([few_distinct_records]) INCLUDE ([many_distinct_records])
Here the selectivity is low (approx 10 / 53000) and most of the 53k are one of those unique values (i.e. "") . What I don't fully understand is how the INCLUDEd field affects the index.
In this case what I think happens is that on each leaf (in this case 10 leaf blocks?) there will be lookup information for the data in the fields that have been INCLUDEd in the index definition. So with this particular index it will give a slight improvement finding the [many_distinct_records] field but no benefit (other than that of the index) over a table scan.
Is this correct or am I misunderstanding the INCLUDE reserved word? I just can't see any benefit to this.
-
With included values a query will also return those values when it looks up on the index. I.e. if you have Col a indexed, with Col b as an included column. If you have
select Col b from T where a=@var
it will not have to go to the table itself to get the result set.
-
To extend your example then I'm assuming it would have to go the the table for
select Col b, Col c from T where a=@var
where Col c wasn't included but Col b was?
-
Correct.
-
Thanks Anders.
As suspected the indexes I've been given are poor choices even with the included fields.
-
Yeah those suggestins are right from the Database Engine Tuning Advisor. All I can say about those is "trust but verify". And 90% of them don't verify
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply