Understanding SQL Server Indexes
SQL Server Indexes are similar to book indexes that we use to search any content in that book. They are very useful in faster data retrieval or data access. We create index on columns of tables or views. An index is made up of a set of pages (index nodes) that are organized in a B-tree structure. A B-tree structure has three levels.
- Root Level
- Intermediate Level
- leaf Level.
Clustered index leaf-level pages contain the data in the table whereas Non Clustered index leaf-level pages contain the key value and a pointer to the data row in the clustered index or heap.
Read More
- How to use Resumable Online Index Rebuild Operation?
- Understanding Online Index Rebuild Operation
- SQL Server Interview Questions & Answers on Indexes
If your table does not have SQL Server indexes and you want to access data from the table then database engine will take more time and performance because full table scan will be performed to get that data. However, if your table has an index on column, SQL Server can seek directly on the value and retrieve the rows.
Index Types
There are multiple types of indexes in SQL Server. Below is the list of such indexes.
Clustered Index
- Clustered indexes sort and store the data rows in the table or view based on their key values. 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.
Nonclustered Index
- Nonclustered indexes have the same B-tree structure as clustered indexes.
- The data rows of the underlying table are not sorted and stored in order based on their nonclustered keys.
- The leaf layer of a nonclustered index is made up of index pages instead of data pages.
- You can create upto 999 nonclustered indexes on a table.
Memory-optimized nonclustered indexes
- For memory-optimized nonclustered indexes, memory consumption is a function of the row count and the size of the index key columns
Hash Index
- With a hash index, data is accessed through an in-memory hash table. Hash indexes consume a fixed amount of memory, which is a function of the bucket count.
Unique Index
- A unique index ensures that the index key contains no duplicate values and therefore every row in the table or view is in some way unique. Uniqueness can be a property of both clustered and nonclustered indexes.
Columnstore Index
- An in-memory columnstore index stores and manages data by using column-based data storage and column-based query processing.
- Columnstore indexes work well for data warehousing workloads that primarily perform bulk loads and read-only queries.
- Use the columnstore index to achieve up to 10x query performance gains over traditional row-oriented storage, and up to 7x data compression over the uncompressed data size.
Index with included columns
- A nonclustered index that is extended to include nonkey columns in addition to the key columns.
Index on computed columns
- An index on a column that is derived from the value of one or more other columns, or certain deterministic inputs.
Filtered Index
- An optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.
Spatial Index
- A spatial index provides the ability to perform certain operations more efficiently on spatial objects (spatial data) in a column of the geometry data type. The spatial index reduces the number of objects on which relatively costly spatial operations need to be applied.
XML Index
- A shredded, and persisted, representation of the XML binary large objects (BLOBs) in the xml data type column.
Full-text Index
- A special type of token-based functional index that is built and maintained by the Microsoft Full-Text Engine for SQL Server. It provides efficient support for sophisticated word searches in character string data.
How SQL Server Indexes are used by Query Optimizer
When a query is executed, the query optimizer evaluates each available method for retrieving the data and selects the most efficient method. The method may be a table scan, or may be scanning one or more indexes if they exist. When performing a table scan, the query optimizer reads all the rows in the table, and extracts the rows that meet the criteria of the query. A table scan generates many disk I/O operations and can be resource intensive. However, a table scan could be the most efficient method if, for example, the result set of the query is a high percentage of rows from the table.
When the query optimizer uses an index, it searches the index key columns, finds the storage location of the rows needed by the query and extracts the matching rows from that location. Generally, searching the index is much faster than searching the table because unlike a table, an index frequently contains very few columns per row and the rows are in sorted order.
Your task is to design and create SQL Server indexes that are best suited to your environment so that the query optimizer has a selection of efficient indexes from which to select. SQL Server provides the Database Engine Tuning Advisor to help with the analysis of your database environment and in the selection of appropriate indexes.
I hope you like this article. Please follow our Facebook page and Twitter handle to get latest updates.
- How to Fix SQL Error 1005: A Comprehensive Guide - April 9, 2023
- How to Fix SQL Server Error 207 – Invalid Column Name - April 9, 2023
- How to Fix SQL Error 1045: Resolving Access Denied Issues - April 8, 2023
