Systems Engineering and RDBMS

Included Columns in SQL Server 2005

Posted by decipherinfosys on May 17, 2007

This is one of the key new features that have been introduced in SQL Server 2005.  A non-clustered index can now be extended to include non-key columns in addition to the index key columns.  So, what are the benefits?

  1. One can include those columns that are defined with data-types (varchar(max), nvarchar(max) or XML) that are not allowed as index key columns.
  2. They are not considered when calculating the index key size.
  3. The 900 byte size limit for the indexes does not apply to these column additions to the index.
  4. The beautiful thing of adding the include columns is that it does not effect the characteristics of the original columns in the index i.e. if say I want to define SSN (Social Security Number) as the unique index in a table but also want to include the first_name and last_name as the included columns in order to provide for a covered index for a query so as to avoid the look-up on the data pages so that everything can be obtained from the index itself.  The uniquess constraint on SSN is still valid in that case even though there can be more than one person with the same first_name + last_name combination.
  5. This also helps a lot in keeping the number of indexes on the tables low.  In the above example, in the absence of included columns, one would have defined 2 indexes – one on SSN (uniqueness enforcement) and another on SSN + first_name + last_name (for the covered index). With included columns, only one index can service both these needs.  Lesser the indexes, lesser the impact on the DML statements (updates, deletes and inserts) since they do not need to keep the indexes updated.

Sorry, the comment form is closed at this time.

 
%d bloggers like this: