SQL index is considered as one of the most important factors in the SQL Server performance tuning field. It helps in speeding up the queries by providing swift access to the requested data, called index seek operation, instead of scanning the whole table to retrieve a few records. It works similar to the book’s index that helps in identifying the location of each unique word, by providing the page where you can find that word, rather than spending the whole weekdays reading the book to check a specific subject or identifying that word. In other words, the existence of that index will save time and resources.
An index is a set of keys made up of single or multiple columns in a table or view. They are stored in a structure (B-tree) that helps SQL Server users quickly and efficiently find the rows or rows associated with the key values.
Types of SQL Server Indexes
There are mainly two types of indexes in SQL Server:
1. Clustered
Clustered indexes use key values for sorting and storing data rows in tables or view. They are included in the index definition. It always stores the index value in a B-tree structure where the actual data is stored in the leaf node. Since the data rows are stored in one direction, each table can only have a single clustered index.
A table stored the rows in sorted order only when the table has a clustered index. We can refer to a clustered table as one that has a clustered index. If a table doesn't have a clustered index, its rows are stored in a heap, which is an unordered structure.
The main benefit of a clustered index is the data is physically sorted by the clustered key value in your storage system, and searching for a range of values will be fast. Its main disadvantage is the Last Page Insert Latch Contention that inserts data only at the end of the clustered index.
Clustered Index Design Guidelines
Consider creating the clustered index on columns having one or more of the below attributes:
Identity columns
Used regularly to sort or group data, this will improve query performance significantly since the index is already sorted.
Sequential columns (for example EmployeeID BETWEEN 100 and 150)
2. Non-Clustered
The structure of non-clustered indexes is similar to the clustered index except that the actual data is not contained in the leaf nodes. A non-clustered index has the non-clustered index key values, and each key-value entry contains a reference to the actual data. Depending on how the table data is stored, it could point to a data value in the clustered index or a heap structure. If a row locator is a pointer to the row, it is a heap structure. If a row locator is the clustered index key, it is a clustered table.
The main benefit of the non-clustered index is to speed up query performance. Its main disadvantage is the extra overhead needed to maintain the index during DML operations.
Clustered vs Non Clustered Index
CLUSTERED COLUMN INDEX | NON-CLUSTERED COLUMN INDEX |
Clustered Column Index does physical sorting of all rows. | Non Clustered Column Index does not does physical sorting. |
Only Single Clustered column Index can be created. | Multiple non clustered column index can be created. |
Clustered Column Index does not consume additional space in the database. | Non Clustered Column Index consumes space in the database. It is required to store index separately. |
Clustered Column Index are faster. | Non Clustered Column Index are slower than Clustered Column Index. |
A Clustered Column Index can improve performance of data retrieval. | It should be created on columns used in frequently in joins , where and order by clause. |
Creating an Index in SQL Server
We can create an index in the SQL Server using the following syntax:
CREATE [UNIQUE | CLUSTERED | NONCLUSTERED] INDEX index_name
ON table_name column_name; If you want to create multiple index columns, use the following syntax:
CREATE INDEX index_name
ON table_name (column1, column2 ...); Here, index_name is a name of an index, table_name represents the name of the table on which the index is created, and column_name is the column's name on which it is applied.
When we should create the Index?
We can create an index in the following cases:
When a column has a wide range of values
When the column does not have a large number of null values
When single or multiple columns used together in a where or join clause
When we should avoid the indexes?
We can avoid an index in the following cases:
When a table is small
When the columns aren't used as a query condition
When the column is constantly updated
Advantage of Indexes
Searching for Records
The index can help in searching and finding that specific row quickly which ultimately improves the performance of the query. SELECT, UPDATE, and DELETE can get benefitted from the Indexes.
Sorting Rows
Indexes help in sorting the column used in the Index. Thus, it is helpful for executing the ORDER BY clause which will sort the data in ascending and descending order. Grouping Record
Indexes help in the group by the operation when the column used in the group by clause is indexed.
Maintaining a unique column
Columns that require unique values such as primary key should have a unique index applied. If a column is set as the primary key then a unique index will be automatically applied.
Disadvantage of Indexes
Additional Disk Space Required
Clustered Column Index does not require additional disk space but Non-Clustered Column Index requires additional space as it is stored as a separate object in the database.
Insert, Update and Delete Operation become Slower
Whenever there is an insert, update, and delete operation performed on the main table then indexes are also updated which cause increases the execution time of the DML command.
The Tech Platform
Comentários