In this post, I will explain about SQL Index and types of SQL Indexes.
Indexes are used to quickly locate data without having to search every row in a database table. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records. An index is a copy of selected columns of data from a table that can be searched very efficiently that also includes a low-level disk block address or direct link to the complete row of data it was copied from.
A table can contain the following types of indexes:
Clustered Index
- Clustered indexes sort and store the data rows in the table based on their key values. These are the columns included in the index definition. There can be only one Clustered index per table because the data rows themselves can be sorted in only one order.
- The only time the data rows in a table are stored in sorted order is when the table contains a Clustered index. When a table has a Clustered index, the table is called a clustered table. If a table has no Clustered index, its data rows are stored in an unordered structure called a heap.
Non-Clustered Index
- Non-Clustered indexes have a structure separate from the data rows. A Non-Clustered index contains the Non-Clustered index key values and each key-value entry has a pointer to the data row that contains the key value.
- The pointer from an index row in a Non-Clustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the Clustered index key.
- You can add non-key columns to the leaf level of the Non-Clustered index to by-pass existing index key limits, 900 bytes and 16 key columns, and execute fully covered, indexed, queries.
Both Clustered and Non-Clustered indexes can be unique. This means no two rows can have the same value for the index key. Otherwise, the index is not unique and multiple rows can share the same key value. Indexes are automatically maintained for a table whenever the table data is modified. Both types of the index will improve performance when select data with fields that use the index but will slow down the update and insert operations. Because of the slower insert and update, Clustered indexes should be set on a field that is normally incremental like Id or Timestamp.