Indexes can be helpful for a variety of queries that contain SELECT, UPDATE, DELETE, or MERGE statements. Well-designed indexes can reduce disk I/O operations and consume fewer system resources therefore improving query performance. How indexes are used by the Query Optimizer When you try to enforce a PRIMARY KEY constraint on an existing table and a clustered index already exists on that table, SQL Server enforces the primary key using a nonclustered index.įor more information, see Create primary keys and Create unique constraints. If you configure a PRIMARY KEY, Database Engine automatically creates a clustered index, unless a clustered index already exists. For example, when you create a table with a UNIQUE constraint, Database Engine automatically creates a nonclustered index. Indexes are automatically created when PRIMARY KEY and UNIQUE constraints are defined on table columns. See Indexes for additional types of special purpose indexes. Indexes are automatically maintained for a table or view whenever the table data is modified. For more information, see Create unique indexes. Otherwise, the index is not unique and multiple rows can share the same key value. This means no two rows can have the same value for the index key. For details about index key limits see Maximum capacity specifications for SQL Server.īoth clustered and nonclustered indexes can be unique. For more information, see Create indexes with included columns. You can add nonkey columns to the leaf level of the nonclustered index to by-pass existing index key limits, and execute fully covered, indexed, queries. For a clustered table, the row locator is the clustered index key. For a heap, a row locator is a pointer to the row. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. The pointer from an index row in a nonclustered index to a data row is called a row locator. A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value. Nonclustered indexes have a structure separate from the data rows. If a table has no clustered index, its data rows are stored in an unordered structure called a heap.

When a table has a clustered index, the table is called a clustered table. The only time the data rows in a table are stored in sorted order is when the table contains a clustered index.There can be only one clustered index per table, because the data rows themselves can be stored in only one order. These are the columns included in the index definition. Clustered indexes sort and store the data rows in the table or view based on their key values.For more information, see the SQL Server and Azure SQL index architecture and design guide.Ī table or view can contain the following types of indexes: This does not apply to columnstore indexes or in-memory data stores. In rowstore indexes, SQL Server implements a B+ tree. SQL Server documentation uses the term B-tree generally in reference to indexes.

Keyclusterer