How to Create Index in SQL Server

by

If you want to know how to speed up the operation on a table, then you must know how to create index in an SQL server and use it.

Multiple operations are performed with a large database to retrieve the information from the database. As a developer, you should know how to improve the operation speed to get the data from the database faster.

There, you can implement the concept of the index to speed up the process of receiving data. I will explain how to create, view, and drop indexes in SQL Server.

Especially you will learn how to create the nonclustered type of SQL Server index.

Create Index in SQL Server

Before creating an index, we must understand “What are SQL Server indexes?” the index is a kind of data structure that enhances the speed of operations in a table.

In other words, it improves the query performance and is used on tables containing large data.

That means that if you use the index on the table containing specific data like 50 or 100 rows, you won’t see any improvement in query performance. The real power of indexes arises when it is applied to large tables.

So, as a database developer, you must understand how to improve the operation or query performance using the indexes.

SQL Server provides two kinds of indexes:

  • Non-Clustered Index:
  • Clustered Index:

Non-Clustered Index: It is a separate copy of a table structure that contains two things: a key and a value. a key contains the column value, a column is specified as an index, and a value contains a pointer pointing to the actual value in the data row.

Here, I will explain to you how to create an SQL server index of type non-clustered; if you want to know how to create an SQL server of type clustered, then visit this tutorial, “How to Create Clustered Index in SQL Server”.

Let me tell you the general syntax to create any index.

CREATE NONCLUSTERED INDEX index_name
on table_name (column1, column2, ....)

Where,

  • CREATE NONCLUSTERED INDEX: This command creates a non-clustered index in SQL Server. Here, the NONCLUSTERED keyword is an option; if you don’t specify the keyword, by default, SQL Server creates a non-cluster index on the table.
  • index_name: Name the index you want to create on a table.
  • on table_name: specify the table name on which you want to create a non-clustered index.
  • (column1, column2, ..): Specify a single or multiple-column name of the table that will be used for indexing.

Let me also show you the general syntax that allows you to create a non-clustered index.

CREATE INDEX index_name
on table_name (column1, column2, ....)

The above syntax doesn’t contain the keyword NONCLUSTERED and will work as the preceding syntax (containing NONCLUSTERED).

Let’s see with an example: here I have the Employees table, which contains around 100000 rows. This is a dummy table, shown below.

Create index in SQL Server Employees Table

Look table contains 100000 rows, as it is a big table. Look at the columns of the table, which are FirstName, LastName, Department, and JoiningData. The table is filled with dummy data based on the column name followed by a sequence of numbers FirstName8, LastName8, etc.

First, you must see the SQL Server Execution time when you run the query. You need to activate the statistics time, so use the command below to activate this.

SET STATISTICS TIME ON;

Whenever you run any query in SSMS, this command shows CPU Time and elapsed time in the Message tab.

For example, let’s fetch the employee with a first name equal to FirstName7000 using the query below.

SELECT * FROM Employees
WHERE FirstName = 'FirstName6000';
Create Index in SQL Server Fetching Records From Table

The query fetches the information related to the employee ‘FirstName600’. Let’s see the SQL Server time execution for this query. So click on the Message table as shown below.

Create Index in SQL Server Fetching Records Timing

Now look at the elapsed time, which is 10 ms, so to fetch that single record takes ten milliseconds; it can vary depending on each time you run the query.

You can reduce this time by creating an index on the Employees table. Let’s see. Create an index named idx_firstname on the FirstName column. Use the query below to create an index.

CREATE INDEX idx_firstName
ON Employees(FirstName);

After creating the index, run the same query for fetching the employee ‘FirstName6000’ details and move to the Message tab to see the elapsed time, as shown below.

Create index in SQL Server

As you can see, the elapsed time is 0 ms this time, so this is how the index improves the query performance or reduces the execution time of the query.

This is how you can create an index in SQL Server and check the SQL Server execution time for each query.

View Indexes on Table

Use the syntax below to view all the indexes on the table in a database.

EXEC sp_helpindex 'table_name';

Let’s view the indexes on the Employees table using the query below.

EXEC sp_helpindex 'Employees';
View index in SQL Server on Table

As you can see, the result set contains three columns: index_name, index_description and index_keys. As you can see, the index idx_firstName we created is a nonclustered index, and the index’s key is the FirstName column.

This is how you can view all the indexes on the table in SQL Server.

Drop Indexes of Table in SQL Server

You have created and viewed the indexes; sometimes, when you don’t need an index, you must delete that index. For that, you can use the syntax below.

DROP INDEX table_name.index_name;

Where,

  • DROP INDEX: This is the command to delete the indexes from the table.
  • table_name.index_name: To delete the index, specify the table name followed by a dot and then the index name you want to drop.

For example, let’s delete the index idx_firstname from the Employees table. So run the query below.

DROP INDEX Employees.idx_firstname;

After executing the above command, use the below command to see if the index is deleted.

EXEC sp_helpindex 'Employees';
Drop Indexes of Table in SQL Server

As you can see in the output, there is no index with the name ‘idx_firstname’ because that index is deleted using the DROP INDEX command.

Using Non-Clustered Index

So, when you use the non-clustered index, there are several factors that you need to consider.

First, a non-clustered index would be a good choice if you want to create more than one index on the database.

Now, if you need to select only the index value, like a select name from the column, here using the non-clustered index will return results most quickly, but using the non-clustered index, like numeric column age, price, etc., would result in a slower response.

You can use the non-clustered index when performing the insert or update operation.

This is how to use the non-clustered index in SQL Server.

Conclusion

In this SQL Server tutorial, you have learned how to create index in SQL Server using the CREATE INDEX command and create an index on the table. Additionally, you learned how to view all the indexes of the table and drop the specific indexes of the table.

Finally, you learned when to use the non-clustered index.