SQL 102 – Indexes With Included Columns

I’ve shown you how to create indexes, CLUSTERED versus NONCLUSTERED indexes, today I want to build on that.

Let’s say you have a lookup query that takes names, and returns an ID for that name, you can actually build your query in a way where it would look up that data using just an index.  You can do that by including non-key columns in the index.

CREATE NONCLUSTERED INDEX ix_tableName__columnName1_columnName2
ON tableName (columnName1)
INCLUDE (columnName2)

Basically what you’re doing is adding the non-key columns  to the leaf level of the nonclustered index.  In this example columnName1 is actually used to order columnName2.  So you’d search through an ordered list of columnName1, once you have a match, you’d return the value from columnName2.  This can produce insanely quick results even from very large tables.

There are a few things to note when using INCLUDE lists.

  • The nonclustered index can be unique or non-unique.
  • Column names cannot be repeated in the INCLUDE list.
  • Columns 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.
  • There’s a whole world of trouble when using INCLUDE with computed columns, see Creating Indexes on Computed Columns for more info.

For more information, see Index with Included Columns.

For information on creating an XML index, see CREATE XML INDEX (Transact-SQL).

If you have any questions, let me know!  I’m here to help you learn everything you want about Microsoft SQL!

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

Leave a comment

Your email address will not be published. Required fields are marked *