SQL Server indexes with included columns are a type of index that allows additional columns to be included in the index, beyond the columns that make up the key of the index. These included columns can be used to improve query performance by allowing the database engine to retrieve the needed data directly from the index, rather than having to look up the data in the underlying table.

An included column can be added to a non-clustered index, a clustered index, or a non-clustered columnstore index. Included columns can be added to a non-clustered index using the INCLUDE clause of the CREATE INDEX or ALTER INDEX statement.

Included columns can also be used to overcome the 900 bytes limit of the index key, which is a limitation of the non-clustered index key size.

It's important to note that including too many columns in an index can lead to increased index size and reduced performance during data modification operations such as INSERT, UPDATE, and DELETE. Therefore, it's important to carefully consider which columns to include in an index, and to monitor the performance of the indexes over time to ensure they are still providing the desired performance gains.

Example

Example of a SQL Server index with included columns:

CREATE NONCLUSTERED INDEX ix_CustomerOrders
ON dbo.Orders (CustomerID)
INCLUDE (OrderID, OrderDate);

In this example, we are creating a nonclustered index called ix_CustomerOrders on the CustomerID column in the Orders table. The INCLUDE clause is used to specify additional columns to be included in the index leaf level, but not as part of the actual index key. In this case, we have included OrderID and OrderDate.

By including these columns in the index, SQL Server can avoid having to look up the values of these columns in the underlying table when the index is used to satisfy a query. This can improve query performance, especially when the included columns are frequently used in queries but not part of the index key.