SQL Server Indexes

SQL Server indexes are the structures associated with a SQL table or view that help in faster retrieval of the data from table/view. There are mainly two kinds of SQL Server Indexes. Clustered Index and Non-Clustered Index.

Clustered Index :

A clustered Indexes sort the data using based on their key values. Clustered Indexes can point to only one row since data rows can be sorted only in one order. More often than not, this row is usually the primary key (unless specified otherwise).

A Clustered Index is automatically created when you create a table with a primary key constraint or you configure a primary key constraint on an existing table (unless a clustered index already exists) in which case, your new key will be used for non-clustered index.

Non-Clustered Index :

A Non-Clustered Index stores data in a heap. A non-clustered index contains key values. Each key-value row has a pointer to the data row that contains the key value. This pointer is called a row-locater.

A row-locater points to a heap memory unless your table/view already has a clustered index in which case it points to the clustered index key.

How do the Indexes improve performance?

When you write a query to fetch data from the table, your query optimizer usually searches for the fastest possible way of fetching the data. There are two ways on how this happens:

Table Scan and Index optimization.

SELECT Name, Percentage from Students where Percentage > 35.

In table scan, the query optimizer searches every row in the table and extracts all the rows where the percentage column satisfies the condition. This is a long process since you are searching all the rows and each row will have multiple columns.

When the query optimizer uses an index, it searches the index key columns, finds the rows matching the query and extracts the rows. Generally, searching the index is a faster process as an index frequently contains very few columns per row and the rows are in sorted order.

Depending on the usage of the table and the columns it contains, you must find the right columns to apply your Indexes on so that your queries run much faster.

How to Create an Index :

Creating an Index using SQL is very simple. In the following example, I will be using a Student table with 3 columns. (Name, Percentage, Department).

a) Clustered Index

CREATE CLUSTERED INDEX IX_StudentTable_Name ON dbo.Student (Name);

b) Non-Clustered Index

CREATE NON-CLUSTERED INDEX IX_StudentTable_Name ON dbo.Student(Name asc, Department asc)

Hope you enjoyed the reading. Give me a clap👏 or follow for more of these. 😊