Covering Index help

Question

Covering Index help

PearlJammer1

SSCrazy Eights

Points: 8477
More actions
March 28, 2013 at 5:24 am

#275688

Is there a difference between a 'covering' index and an index with 'included' columns - or is that exactly what a covering index is ? (An index with included columns)
I am confused because in my new job i have been looking at indexes and when i expand the index tree on a table the index has a name of say:
ix_TableName_ABC_Covering1
When i click on the properties it has 2 column names listed that the index uses but NO columns listed in the included Columns page on the 'Select a page' window on the left.
Does The fact that it has 2 columns listed in the general tab make this a 'covering' index - i thought they had to be listed in the 'included columns' tab to make the index 'covering'
Can somebody put me straight on this please !!!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135255 More actions · Answer 1

so far for naming conventions 😉

convering index as a concept means a query ( or part of it ) can be served directly by the index itself, without having the need to go to the data pages ( in heap or clustered index ) to fetch extra data for that object.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Abu Dina SSChampion Points: 14155 More actions · Answer 2

My understanding of this is as follows:

INCLUDED columns in the index go in the SELECT part of the query whilst the actual column that make up the index are used in the JOIN part of the query. So to answer your question, the difference between INCLUDED and COVERING kind of depends on the query it is used with. An index is covering if the query that uses it doesn't need to retrieve any further data pages.

---------------------------------------------------------

It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn

PearlJammer1 SSCrazy Eights Points: 8477 More actions · Answer 3

Ok so if i am creating an index from a script that recommends 'Equity', 'Inequity' and 'Included' - do i put the 'included' columns it recommends into the 'included columns' section or do i had the column names into the main index section on the 'general' page ??

Johan Bijnens SSC Guru Points: 135255 More actions · Answer 4

an index sonsists of two parts :

a keys section and an included-columns section.

Books online shows how many columns or bytes each can have.

http://msdn.microsoft.com/en-us/library/ms143432.aspx ( see "Columns per index key")

This ref contains some guidelines for creating indexes with included columns:

http://msdn.microsoft.com/en-us/library/ms190806.aspx