SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Covering Index vs Include Index


Covering Index vs Include Index

Author
Message
tt-615680
tt-615680
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6227 Visits: 1382

I’m looking intocreating indexes and I want to know which types of indexes are better and inwhich situations to use them INCLUDE and COVERING?

Thank you inadvance!


Sean Lange
Sean Lange
SSC Guru
SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)

Group: General Forum Members
Points: 235115 Visits: 19110
tt-615680 - Friday, February 23, 2018 4:03 AM

I’m looking intocreating indexes and I want to know which types of indexes are better and inwhich situations to use them INCLUDE and COVERING?

Thank you inadvance!



This is entirely too vague for anything resembling a cohesive response. What you are asking is a very big topic. I would suggest you start reading up on indexes in depth so you better understand how they work. Once you have that knowledge you will know what types of indexes would be appropriate in the situations you are asking about. Lucky for you there is a spectacular series of articles right here on SSC that discusses indexes from the basics to a pretty deep level. http://www.sqlservercentral.com/stairway/72399/

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (354K reputation)SSC Guru (354K reputation)SSC Guru (354K reputation)SSC Guru (354K reputation)SSC Guru (354K reputation)SSC Guru (354K reputation)SSC Guru (354K reputation)SSC Guru (354K reputation)

Group: General Forum Members
Points: 354960 Visits: 41694
tt-615680 - Friday, February 23, 2018 4:03 AM

I’m looking intocreating indexes and I want to know which types of indexes are better and inwhich situations to use them INCLUDE and COVERING?

Thank you inadvance!



Also, the INCLUDE clause in CREATE INDEX is one way of creating a covering index. The other is to define all the columns as part of the index but then you are limited to either 16 columns or 900 bytes, which ever happens first. As Sean said above, you really need to learn more about indexes.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Steve Jones
Steve Jones
SSC Guru
SSC Guru (540K reputation)SSC Guru (540K reputation)SSC Guru (540K reputation)SSC Guru (540K reputation)SSC Guru (540K reputation)SSC Guru (540K reputation)SSC Guru (540K reputation)SSC Guru (540K reputation)

Group: Administrators
Points: 540934 Visits: 20703
Sean is correct, you have asked a vague question.

A covering index includes all the information needed for a query. This means the columns in the SELECT list, as well as join/where/on/having clauses exist in the index. Therefore the query doesn't need to access the clustered index or heap for data. Any index can be covering, depending on the query.

An INCLUDE adds columns to the index that are stored with the index, but aren't used as part of the searching. For example, if I had ProductID and Product Name in the index, I can use those in WHERE clauses or other filters. However, if UnitPrice is INCLUDEd, this is only useful if the query uses the column in the SELECT list. If we searched on WHERE UnitPrice > 10, this index isn't used. Think of this as other information added to the index that is returned to the user, but not used to run the query filters.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Subramaniam Chandrasekar
Subramaniam Chandrasekar
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3080 Visits: 531
tt-615680 - Friday, February 23, 2018 4:03 AM

I’m looking intocreating indexes and I want to know which types of indexes are better and inwhich situations to use them INCLUDE and COVERING?

Thank you inadvance!


Should need more information to comment.

COVERING INDEX - An index that contains all information required to resolve the query is known as a “Covering Index”; it completely covers the query

INCLUDE COLUMNS - You will normally use the INCLUDE to add one or more columns to the leaf level of a non-clustered index, so that, you can "cover" your queries.

So you can understand the differences here.

Please go thru the similar topic posted here,

https://www.sqlservercentral.com/Forums/Topic509318-360-1.aspx

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search