Why do we need Index?
Indexes are
used by queries to find data from tables quickly. Indexes are created on tables
and views. The existence of the right index, can drastically improve the
performance of the query.
Without index
the query engine checks every row in the table from beginning to end. This is
called table scan which is bad for performance.
What are the different types of Indexes
available in SQL server?
- Clustered
- Non-clustered
- Unique
- Filtered
- XML
- Full Text
- Spatial
- Columnstore
- Index with included columns
- Index on computed columns
Clustered
Index
- Clustered index physically order the data on the disk and hence doesn’t require additional disk space.
- Accessing data using a clustered index is faster as data is physically stored in index order.
- A table can have only one clustered index.
- The Leaf node of the clustered index contains the data pages.
- The overhead of maintaining a clustered index, especially in a frequently updated table, can lead to poor performance.
- 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.
- Primary key constraint creates clustered indexes automatically if no clustered index already exists on the table.
- A table can have only one clustered index; however the index can contain multiple columns. This is called a composite clustered index.
NonClustered
Index
- The data is stored in one place and the index in another place. The index will have pointers to the storage location of the data.
- A table can have more than one Non-Clustered index as the index is stored separately from the actual data.
- Quicker for insert and update operations than a clustered index.
- Additional storage space is required, as Non-Clustered index is stored separately from the table.
- The leaf node of a Non-Clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
- A non clustered index can contain multiple columns. This is called a composite Non-Clustered index.
Unique
Index
- A Unique index is used to enforce uniqueness of key values in the index
- Uniqueness in the property of an index, and both clustered and Non-Clustered index can be unique.
- By default, a primary key constraint creates a unique clustered index where as a unique constraint creates a unique Non-Clustered index. These defaults can be changed.
- There are no difference between unique constraint and a unique index. When you add a unique constraint, a unique index gets created automatically.
- A unique constraint or a unique index cannot be created on a existing table, if the existing table contain duplicate values in the key column.
Filtered Index
- A filtered index is an optimized Non-Clustered index especially suited to cover queries that select from a well-defined subset of data.
- Filtered index are Non-Clustered indexes that have a addition of a WHERE clause.
- Only records with certain values will be added to the index, resulting in a much smaller and efficient index
- Reduced index maintenance cost
- Reduced index storage cost.
