Index

Indexes in SQL Server helps in faster retrieval of data and thus improving the performance of queries.

Indexes are associated with tables or views which makes the retrieval of rows from the table or views fast. Indexes basically contain keys which are built from one or more column in the table. These keys are stored in a B-tree structure that enables SQL Server to find the rows associated with the key values quickly and efficiently.

Let’s understand this with the help of an example. Suppose you are looking for a particular topic in a book. Now going through the entire book page by page in order to search for that topic is very time-consuming. Rather, we prefer going through the first few pages of the book that contains the indexes. The indexes are categorized into chapters and further subcategorized in topics. Now Indexes in SQL Server works in the same manner.

Indexes in SQL Server are divided into 2 types, based on how it is being created:

• Clustered Index
• Non-Clustered Index

Interesting thing to note here is that indexes are automatically created for Primary Key and Unique Key. Since there can be only one Primary Key per table, a clustered index is formed.

View indexes

In order to view all the indexes in a particular database, execute the following query

SELECT *
FROM sys.indexes

Clustered Index

Data gets sorted in the table itself based on the column on which clustered index is being applied. There can be one and only one clustered index per table.

Clustered Index

In the clustered index, the table data is present in the leaf nodes. Let's look at the image. Here, the clustered index is present at the RoleId. Hence, at the leaf node, you can see that the table data is present in the leaf node in the sorted order of the RoleId.

Creating a clustered index:

-- creating a clustered index of RoleId column of Roles table --
CREATE CLUSTERED INDEX idx_RoleId_Roles ON Roles(RoleId)

Dropping a clustered index:

DROP INDEX idx_RoleId_Roles ON Roles

If we already have a Primary Key in the table in which we want to create a clustered index, we first need to drop the primary key constraint (since a clustered index is automatically formed for a primary key) and then create the index. In other words, if you want to create a clustered index on any other column, then the existing clustered index needs to be dropped.

ALTER TABLE Roles DROP CONSTRAINT pk_RoleId 

Non-Clustered Index

The rows in the table are not sorted based on the index. Instead, it will occupy a separate memory where the index is sorted. There can be more than one non-clustered index per table.

In the case of a nonclustered index, the leaf node does not contain the actual data. Instead, it contains a pointer to the actual data present in the table. Let's suppose that in the image below, there is a nonclustered index present in the RoleName column

Non-Clustered Index

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. The pointer from an index row in a nonclustered 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.

Creating a non-clustered index:

CREATE NONCLUSTERED INDEX nidx_RoleName_Roles ON Roles(RoleName)

Both clustered and nonclustered indexes can be unique which means that 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.