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.