sql server consulting logo
sql server consulting is our only business

Free SQL Server Consultation

Sometimes you only need the answer to a question or two. If 15 minutes of our time can help you solve a SQL Server problem, we are happy to help. No charge, no strings attached.

Call 877.201.3428 toll-free

Using SQL Server in the Enterprise - Archives

Sign up for your FREE subscription to this SQL Newsletter

Give this article a Google +1!

Using the INCLUDE Operator For Better Indexes

Issue 6 Number 11 November 2016

 

It has been a long time since Microsoft put the INCLUDE option into the Create Index command. Still I find that many developers and even DBA's don't use it much or not at all. I find that some don't know how or why to use it. Some don't even know it exists.

I personally think that the Included Column is one of the most useful improvements in post-2000 SQL Server. It can save your butt in many dire situations.

Later in this article I will provide a link to information about syntax and usage, but I am not going to take a deep dive into the technical details.

Covering Indexes

Before we can say much about Included Columns we have to say something about Covering Indexes since they are at the center of everything we are going to discuss here.

What is a Covering Index?

If an index contains all the columns mentioned in a specific query, then the index is said to 'cover' that query. That's because the query can get all the information it needs from the index and doesn't need to touch the table at all.

An index like this can turn a slow query to one that is lightning fast.That's why covering indexes are so important to good performance and that is why DBAs go to such great lengths to create them.

The Problem

This is all very cool, but it only works if the index contains absolutely ALL of the columns mentioned in the query, whether in the SELECT clause, the FROM clause, the JOIN clause, the WHERE clause, the Order By clauseor any other clause found in the query.

Before the INCLUDED option arrived in SQL 2005 there was a practical limit to how many additional columns could be included to make a query into a covering query.

Before SQL 2005 you had to be careful of the index size. Often you would not create a covering index because of the cost of the additional columns you had to add to the index.

These additional columns are called non-key columns or included columns. They take no part in the necessary business of the index. but they are necessary to match all the columns mentioned in the query,

The Solution

The INCLUDE option makes Included columns much less expensive due to the compact way included indexes are now stored on disk.

Using INCLUDE, it is possible to add many more non-key columns to your query and consequently create covering indexes with a minimal increase in cost.

How Minimal?

For Example: if you have seen the results of a Missing Index query you probably noticed that the Missing Index algorithm is made possible by the INCLUDE option. To create a covering index it allows many, many included columns, sometimes more than a dozen, with very little performance impact.

This would not have been possible before the INCLUDE operator arrived.

You should read this article before going on a Missing Index binge.

Here is a link to the Create Index command.

Afterward by the Author

I hope this information has been helpful to you. I would appreciate any feedback you can give me about this article or recommend future articles you would like to see. If you enjoyed reading this article, you might like to receive this monthly newsletter via Twitter. If so, follow me.

As always, if you have questions or comments give me a call toll-free at 1.877.201.3428 or send me email.

Kurt Survance, SQL Consulting, Inc.

Return to Top

Go to SQL Consulting Home

On this page:

INCLUDE Operator For Better Indexes

Elsewhere on this site:

 
Call SQL Consulting
 
mcse microsoft certified systems engineer
mcdba microsoft certified database administrator