Non Clustered Index in SQL Server

A Non Clustered Index in SQL Server stores the index structure separately from the data physically stored in a table. SQL Server allows you to create almost 999 non clustered indexes per table. The non clustered index in SQL Server is useful to improve the query performance. Say, you want to retrieve employees by their sales (which is a regular report), then you can use a non clustered index on Sales to retrieve the records quickly.

Let me show you what will happen if you create a Non clustered Index in SQL Server. For example, if you had a Customer table with the following records. Assume Customer key is Primary key (means Clustered Index), but you require to retrieve records by yearly Income.

In this situation, you have to create a non clustered index on the Yearly Income column. It will store the Yearly income records in separate locations along with the row address. When you ask for customer records whose Income = 79000. First, SQL will look for 79000 in the second table, and after finding the record, it will check the row address. Second, it will retrieve record (from the First table) at that row address

Non Clustered Index in SQL Server 22

In this chapter, we will show you how to create and delete a non clustered index in SQL Server using Transact SQL Query, and SQL Server Management Studio

Non Clustered Index in SQL Server 1

How many Non Clustered Indexes in SQL Server?

You can create 999 Non Clustered Indexes per Table.

Create Non Clustered Index in SQL Server

This example explains to you how to add Non Clustered Index to an existing table. And the syntax is:

-- Create Non Clustered Indexes in SQL Server
CREATE NONCLUSTERED INDEX Index_Name
ON Table_Name (Column_Name(s) ASC/DESC)

It will create Non Clustered Index on Yearly Income Column present in the Customer record table

-- Create Non Clustered Indexes in SQL Server
CREATE NONCLUSTERED INDEX IX_CustomerRecord_YearlyIncome
ON CustomerRecord ([Yearly Income] ASC)
Messages
--------
Command(s) completed successfully.

Let me use the sp_helpindex stored procedure to check the indexes on the Customer Record table.

EXECUTE SP_HELPINDEX CustomerRecord

As you can see, the Yearly Income column has Non Clustered index.

Non Clustered Index in SQL Server 3

You can also use SP_HELP stored procedure in SQL Server to check the same.

EXECUTE SP_HELP CustomerRecord

It displays all the information about the table.

Non Clustered Index in SQL Server 4

Create Composite Non Clustered Index in SQL Server

You to create non clustered index on multiple columns. So, you can point the same non clustered index to Multiple columns. In this example, we are adding composite SQL non clustered index on Professional column, and Yearly Income column

-- Create Composite Non Clustered Indexes in SQL Server
CREATE NONCLUSTERED INDEX IX_CustomerRecord_ProfessionIncome
ON CustomerRecord ([Profession] DESC, [Yearly Income] ASC)
Messages
--------
Command(s) completed successfully.

Now you can see them.

Non Clustered Index in SQL Server 8

Create Multiple Non Clustered Indexes

SQL allows you to create multiple non clustered indexes per table. In this example, we are adding non clustered index to Professional column, and another index on Yearly Income column

-- Create Multiple Non Clustered Indexes in SQL Server
CREATE NONCLUSTERED INDEX IX_CustomerRecord_Profession
ON CustomerRecord ([Profession] ASC)

CREATE NONCLUSTERED INDEX IX_CustomerRecord_Income
ON CustomerRecord ([Yearly Income] DESC)
Messages
--------
Command(s) completed successfully.

Now you can see multiple indexes

Non Clustered Index in SQL Server 10

Create Non Clustered Index in SQL Server Management Studio

Let me show you, how to create a non clustered index, how to delete it using Management Studio

Use Object Explorer to Create Non Clustered Index

In this example, we explain how to create a Non Clustered Index using the Management Studio.

To do so, please go to the Object Explorer and expand the Database folder. Please expand the table to create a Non Clustered Index, and Right-click on the Indexes folder will open the context menu. Please select New Index and then select the Non-Clustered Index..

Non Clustered Index in SQL Server 11

Once you choose the Non-Clustered Index.. option, a new window called New Index will be opened as we showed below. Please change the non clustered Index Name as per your requirements

Non Clustered Index in SQL Server 12

Click on the Add button, and select the column name on which you want to specify the Non Clustered Index. For now, we are selecting the Last Name column.

Non Clustered Index in SQL Server 13

Please change the sort order.

Non Clustered Index in SQL Server 14

Now you can see the non clustered index

Non Clustered Index in SQL Server 15

Create Non Clustered Index using Table Designer

Please extend the table on which you require to create a Non Clustered Index, and Right-click on it will open the context menu. Please choose the Design option to open the table in the Designer window.

Next, Right-click on the column and select the Set Indexes / Keys… option.

Non Clustered Index in SQL Server 16

Please click on the Add button to add a Non Clustered Index. Once you select the Add option, SSMS will create an Index for you.

Non Clustered Index in SQL Server 17

Next, click on the Browse button beside the Columns option to change the column. Next, we selected the Profession as the Column Name, and Descending is the Sort Order.

Non Clustered Index in SQL Server 18

Under the Table Designer, Please change the Create As Clustered option to No

Non Clustered Index in SQL Server 19

See the newly created non clustered index

Non Clustered Index in SQL Server 20

Drop Non Clustered Index in SQL Server Example

Please use the DROP INDEX statement to delete or drop Non Clustered Index in SQL Server

-- DROP Non Clustered Index in SQL Server

DROP INDEX CustomerRecord.IX_CustomerRecord_Profession
Messages
--------
Command(s) completed successfully.

Use SSMS to Drop Non Clustered Index Example

Within the object explorer, Expand the Database folder and choose the table on which your Non Clustered Index present. Next, expand the Indexes folder, and right-click on the key name will open the context menu. Please select the Delete option

Non Clustered Index in SQL Server 5

Once you select the Delete option, Delete Object window opened. Click OK to delete the Non clustered Index in SQL Server.

Non Clustered Index in SQL Server 6

Comments are closed.