This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

SQL Server Indexes Part 1

The fifteenth part of the SQL Server Programming Fundamentals tutorial describes indexes. Indexes provide a lookup facility for a table, allowing rows to be found more quickly and without the need to scan the data in every row when querying a table.

Non-Clustered Indexes

Non-clustered indexes are held separately from the data in a table and do not cause any changes to the ordering of the physical data. You can therefore create multiple non-clustered indexes on a single table.

In a non-clustered index, the leaf nodes of the b-tree structure contain pointers to the rows of data they represent, rather than the data itself. The pointer is either a reference to a location in the table's clustered index or, when the table is not clustered, to the table row. This adds an extra lookup to the process when reading from an index, lowering the performance of queries. A further reduction in speed can occur because adjacent rows in the index are less likely to be in the same data pages increasing the required disk access.

  • Non-clustered indexes are ideal for tables that will be updated rarely and queried often. If there are large volumes of data in the table, the querying speed can be drastically improved.
  • Non-clustered indexes are useful where a clustered index would be preferred but one already exists for the table.
  • If a table is updated often and queried less so, you should carefully consider the benefits and drawbacks of adding such an index.
  • Non-clustered indexes provide further performance gains when they cover the query's returned columns. Coverage means that all of the columns in the query are also included in the index. In this case, the query engine can obtain its results entirely from the index without visiting the table data.
  • When creating a non-clustered index, you should not add a large number of columns just to achieve coverage of the returned data. The columns in the index should be those that are likely to be included in a WHERE clause only. To achieve additional coverage, you should consider using indexes with included columns, described later in this article.
  • If a non-clustered index is included in a clustered table, the columns from the clustered index are automatically included in the non-clustered index as they are the pointer that will ultimately find the data. You can obtain coverage of some queries from this combined set of columns.

Unique Indexes

A unique index may be clustered or non-clustered. This type of index prevents duplication of data in the combined set of defined columns. If you attempt to create duplicate data in the defined index columns the command will fail. A unique index is created automatically when adding a unique constraint.

  • When creating an index where the data will be unique, always specify a unique index. This allows additional efficiency in query execution plans.
  • There is no significant difference between unique indexes and unique constraints. If you wish to force uniqueness, you should use a constraint, as the purpose will be clearer to other developers and database administrators.

Indexes with Included Columns

Indexes are limited to a maximum of sixteen key columns with a combined size of nine hundred bytes. To help obtain coverage of queries without using a large number of columns, you can use included columns. Indexes with included columns are a new feature of SQL Server 2005.

Included columns are columns from the underlying table that are added to the index but that are not used to optimise queries. They are not included in the maximum number or size of key columns and can include data types that are not normally permitted in an index. During a query, once a row in the index has been identified as a result to be returned, the extra columns may provide coverage so that reading the physical table data is unnecessary.

This type of index can improve query performance. However, you should avoid adding too many columns or very large items as this can lower performance and can result in the creation of very large indexes.

5 November 2008