Recently I worked in a freelancing project where my main task was to optimize the database queries and optimize the database structures. While working on that project I have used the new feature of SQL Server 2005 Included Column Index which proved to very beneficial. So I thought it would be a good idea to write about this new feature.
In SQL Server 2005 the functionality of the nonclustered indexes have been extended by adding non key columns to the leaf level of the nonclustered index. By including non key columns we can create nonclustered indexes that cover more queries.
The main advantage of INCLUDED column are:
- 900 byte limitation of the index key can be exceeded. Any column that would push over the limit could be added as INCLUDED columns rather than key columns.
- Data types such as varchar(max), nvarchar(max), XML which are not allowed as key columns can be added as INCLUDED columns. However still the old data types such as text, ntext cannot be added as INCLUDED columns.
- The size of the index can be reduced substantially by adding only the lookup columns in the key and adding all other columns in the INCLUDED columns list.
The basic syntax is given below:
CREATE NONCLUSTERED INDEX [IX_MyIndex]
ON [dbo].[My_Table] (Title)
- Non Key columns can be included only in Non-Clustered Indexes.
- Column names cannot be specified in both the INCLUDE list and in the key column list.
- Non-key columns can be dropped from a table only after the non-key index is dropped first.
- For Included Column Index to exist there must be at least one key column defined with a maximum of 16 key columns and 1023 included columns.
Note: We should always avoid adding unnecessary columns. Adding too many index columns, key or non-key as they will affect negatively on performance.
Keep Learning and Keep coding!!