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!