Importance of Indexing in SQL Server and Different Types of Indexes in SQL Server

What is Indexing in SQL Server?

Indexing reminds us of the books we read where a list of chapters would be indexed using page numbers at the end of the book, so that the reader can quickly locate the chapter and start reading. Now imagine how difficult and time consuming it would be to locate a chapter page by page, but if the book is indexed, it will make the searching process much faster.

Similarly, querying for a row in a table with lots of data can be time consuming and will affect the performance of the query itself. But not when the table has been indexed judiciously with proper indexes.

You may like this: What is a Stored Procedure and why you should use it.

The Downside of Indexing

Indexing comes with a small cost. Your DML or Data Manipulation Language commands (or statements) like Insert, Update and Delete will get affected on a table with indexes. Since every time a user manipulates data in the table, the SQL engine will update the indexes and this will slow down the process. So remember, too many indexes on the same table can ruin the entire data manipulation process.

Nevertheless, when you’re serving multiple users on a multiple platform across the web, retrieving data quickly and efficiently from a huge collection of data pool becomes a priority. Manipulating the online data becomes secondary and fetching them instantly brings lot of satisfaction to the users.

Types of Indexes in SQL Server

There are 2 different types of Indexes in Sql Server.

• Clustered Index
• NonClustered Index

1) You can define only one Clustered Index on a table, since the order of the rows in the table is the same as the indexes. This helps improving the performance of queries using Order By and Group By, since the data rows are already sorted in the order of the indexes.

2) Only one index per table ensures uniqueness, which means SQL will fetch data much quicker, as there are no duplicate indexes.

3) When you set a Column as PRIMARY KEY, a unique index (which is clustered by default), is automatically created associated with the column. At any point of time, you can convert the clustered index to a NonClustered index.

4) It also improves the performance of a query using JOINS. A Foreign key column in a JOIN should have a clustered index defined.

5) Create this index when a column is used in a query for ranging. Ranging typically means queries using operators like ‘>’, ‘<’, ‘>=’ and ‘<=’.

6) Ususlly, a column with SalesOrder_ID or an Employee_ID always remains unique in a table. So these are the type of columns typically have a Primary Key, just to make sure that it hold unique values. Always choose a column for “Clustered Index” which has very limited chance of manipulations or changes.

Grouping or Clustering helps in organizing the data in such a way that they become easily and quickly accessible without doing too much reading and searching.

Create and Drop a Clustered index using T-Sql

CREATE TABLE dbo.Employee 
    (EmpID INT NOT NULL,
        EmpName VARCHAR(50) NOT NULL,
	    Designation VARCHAR(50) NULL,
        Department VARCHAR(50) NULL, 
        JoiningDate DATETIME NULL
    )
    
CREATE CLUSTERED INDEX xlEmpID_Clustered ON dbo.Employee (EmpID)

DROP INDEX xlEmpID_Clustered ON dbo.Employee

👉 Do you know there are LEFT() and RIGHT() functions in SQL Server? Check out this article.

1) If you have already defined a clustered index on a table, other indexes on the same table will be NonClustered. You can define multiple NonClustered indexes on a single table. Columns with repeated values are best suited for defining nonclustered indexes.

2) Prior to SQL Server 2008, you could define 249 nonclustered indexes with just 1 clustered index on a single table. Since SQL Server 2008 we are able to define 999 nonclustered indexes, with 1 clustered index.

3) Unlike clustered indexes, NonClustered indexes are created outside the table on different data pages also known as Index Pages. These pages have pointers known as Index Key, pointing back to the table rows.

4) If you expecting your SQL query to return limited number or rows, then nonclustered indexes will be an ideal choice. We can limit the number of rows returned using a WHERE clause or using JOINs.

Create and Drop a Non-Clustered index using T-Sql

CREATE TABLE dbo.Employee 
    (EmpID INT NOT NULL,
        EmpName VARCHAR(50) NOT NULL,
	    Designation VARCHAR(50) NULL,
        Department VARCHAR(50) NULL, 
        JoiningDate DATETIME NULL
    )
    
CREATE NONCLUSTERED INDEX xlEmpName_Non_Clustered ON dbo.Employee (EmpName)

DROP INDEX xlEmpName_Non_Clustered ON dbo.Employee

← PreviousNext →