Rick Caminiti dotCOM

The INCLUDE statement specifies the non-key columns to be added to the leaf level of the nonclustered index. The nonclustered index can be unique or non-unique.
Column names cannot be repeated in the INCLUDE list and cannot be used simultaneously as both key and non-key columns. Nonclustered indexes always contain the clustered index columns if a clustered index is defined on the table.

All data types are allowed except text, ntext, and image. The index must be created or rebuilt offline (ONLINE = OFF) if any one of the specified non-key columns are varchar(max), nvarchar(max), or varbinary(max) data types.

Computed columns that are deterministic and either precise or imprecise can be included columns. Computed columns derived from image, ntext, text, varchar(max), nvarchar(max), varbinary(max), and xml data types can be included in non-key columns as long as the computed column data types is allowable as an included column.

SYNTAX:
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
  ON ( column [ ASC | DESC ] [ ,...n ] )
  [ INCLUDE ( column_name [ ,...n ] ) ]


SAMPLE:
Lets say you have an ETL Job for your fact table that has lookups to validate all of the dimensional keys and get the surrogate keys. You may have something like

SELECT productID, EDWproductID
FROM EDWProduct
WHERE productID = ?

OR

SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N’98000′ and N’99999′;

It’s obvious that you’ll need indexes on the key fields ProductID and PostalCode, but what might not have been obvious is that it makes sense to include the non-key values into the index to improve performance. Granted, you don’t want to make a habit of creating indexes to meet every single query’s needs, but if these queries are being used in ETL jobs multiple times every day or are being used in reports every day etc, it would make sense to create these indexes to avoid touching the table at all. If these columns are included in the index, you read the index only and the query engine has no need to read the table.


CREATE NONCLUSTERED INDEX IX_Address_PostalCode
  ON Person.Address (PostalCode)
  INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);

CREATE NONCLUSTERED INDEX IX_EDWProduct
  ON EDWProduct (ProductID)
  INCLUDE (EDWProductID);
GO

For more information, see http://msdn.microsoft.com/en-us/library/ms188783.aspx