See Also: Main_Page - Database Administration - Database Design - Indexing

In addition to index keys, each non-clustered index could also contain non-key columns, called "included columns". Unlike index keys, included columns are only stored at the leaf-level of a non-clustered index. Included columns cannot be added to clustered indexes.

Included columns can benefit your application because non-clustered indexes that have included columns can "cover" more queries. An index is called a "covering" index if it contains each column referenced by the search condition of a query. For example, let's suppose a query includes a WHERE clause examining marital status and occupation of a given customer. The non-clustered index which is defined on marital status and occupation columns is called a covering index for this query. If the index is covering then the query results can be obtained by seeking through an index as opposed to scanning the table, resulting in fewer input / output operations.

When to Use Included Columns

Included columns are useful because they can be built on data types which cannot be used as index keys. Furthermore, included columns are NOT counted towards the 900 byte limitation for index keys. So if you have two VARCHAR(500) columns which are commonly searched together you could build an index with one of them used as the key column and second one as the included column. You can include columns with any data type except TEXT, NTEXT and IMAGE. Computed columns can also be used as included columns.

Each index (whether clustered or non-clustered) can have up to 16 index keys; included columns are not counted towards this limit, although having indexes with this many keys would be a rare occurrence. SQL Server allows up-to 1023 included columns per non-clustered index. Regardless of how many included columns you add to the index it must have at least one key column.

Why Included Fields Improve Performance

Since included columns are only stored on the leaf level of an index, such indexes are typically leaner (and therefore more efficient) than indexes with numerous key columns. If you had previously created indexes that have numerous key columns consider re-engineering these with fewer keys and additional included columns. On the other hand, you should choose which columns are included in each index judiciously. SQL Server has to maintain indexes to reflect data changes resulting from INSERT, UPDATE and DELETE statements. Each key column and included column adds the overhead required for maintaining indexes.

For example, you could create the following index using included columns on AdventureWorksDW sample database:

CREATE INDEX IX_FactInternetSales_incl_columns
  ON dbo.FactInternetSales(   ProductKey)
  INCLUDE (   OrderDateKey,   DueDateKey,   ShipDateKey,   CustomerKey,   PromotionKey,   CurrencyKey,   SalesTerritoryKey,   SalesOrderNumber,   SalesOrderLineNumber,   RevisionNumber,   CarrierTrackingNumber,   CustomerPoNumber);

Let's suppose you created Analysis Services measure group based on FactInternetSales table. Next, you partitioned the measure group based on multiple attributes. While processing each partition in the measure group, Analysis Services will execute a query similar to the following:

SELECT   ProductKey,   OrderDateKey,   DueDateKey,   ShipDateKey,   CustomerKey,   PromotionKey,   CurrencyKey,   SalesTerritoryKey,   SalesOrderNumber,   SalesOrderLineNumber,   RevisionNumber,   OrderQuantity,   UnitPrice,   ExtendedAmount,   UnitPriceDiscountPct,   DiscountAmount,   ProductStandardCost,   TotalProductCost,   SalesAmount,   TaxAmt,   Freight,   CarrierTrackingNumber,   CustomerPONumber  
  FROM FactInternetSales 
  WHERE productKey = 336   
    AND DueDateKey BETWEEN 1 AND 1000
    AND ShipDateKey BETWEEN  1 AND 1000
    AND CustomerKey BETWEEN 1 AND 100000 
    AND PromotionKey BETWEEN 1 AND 1000
    AND CurrencyKey BETWEEN 1 AND 101
    AND SalesTerritoryKey BETWEEN 1 AND 101
    AND SalesOrderLineNumber BETWEEN 1 AND 101
    AND RevisionNumber BETWEEN 1 AND 101
    AND CarrierTrackingNumber BETWEEN 1 AND 101
    AND CustomerPoNumber BETWEEN 1 AND 101

This query would benefit from the index with included columns.

Graphical Execution Plans

With SQL Server 2005 you can save the query execution plan (actual or estimated) with .sqlplan extension. If you do this you can re-open the execution plan later and see the same graphical output.

Special Considerations for Data Warehouses

In a data warehousing environment you should consider dropping indexes prior to loading the data and subsequently re-building the indexes. You should do so for two reasons:

• When you load data, indexes get fragmented and become less effective. Rebuilding indexes after the data load ensures that when you process your cubes fragmentation in fact and dimension table indexes is minimal and these indexes offer the best performance.
• Each index also degrades the performance of INSERT, UPDATE and DELETE statements because SQL Server has to maintain indexes to reflect table data changes. Loading data into a table without indexes will work faster than loading the same data into a table with numerous indexes.

Once you create an index with included columns you cannot drop such columns from the table. Nor can you shrink their size, for example changing data type from VARCHAR(300) to VARCHAR(50) is not allowed. You can, however modify the column by increasing its length or change the null-ability of a column (whether the column allows NULL values or not).

More SQLServerPedia Articles on Indexes

How SQL Server Indexes Work

• Index Overview - the basics of how indexes work.
• Data Pages & Extents - about how indexes and data are stored on disk.
• Table and Record Structure - more about the mechanics of data storage.
• Effects of Data Modifications - the more indexes you have, the longer inserts/updates/delete take.
• Index Statistics - the basics of how statistics affect query execution.

Types of Indexes in SQL Server

• Included Columns - starting with SQL Server 2005, you can add fields to indexes with a smaller performance hit.
• Indexed Views - how to build indexes on top of views for more speed.
• XML Indexes Overview - how to index XML documents and fragments.
• XML Index Rules - some additional considerations and requirements for XML indexes.

Best Practices on How to Design Database Indexes

• Indexing Strategies - choosing between clustered/nonclustered, long/short, selectivity, and more.
• Best Practices for Creating Indexes - tips to start with when designing a new index strategy.
• Choosing Index Keys - how to pick which fields to use for a primary key.
• Index Selectivity and Column Order - how to pick which columns to put first, and how it affects different kinds of queries.
• Wide Index Performance - does adding more fields to an index cause it to run slower?
• Tuning Indexes - including articles on the Index Tuning Wizard and Database Tuning Advisor.

Maintaining Indexes for Top Performance

Indexes need regular maintenance in order to perform well.

• Index Maintenance Tutorial - video and script explaining how to defragment and rebuild your indexes automatically.
• Index Maintenance in SQL Server 2005 and Beyond - SQL 2005 introduced new syntax to ALTER INDEX.
• Index Maintenance Overview - analyzing fragmentation and statistics to get the best performance possible out of your indexes.