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. Microsoft allows you to create almost 999 non clustered indexes per table. The non clustered index is useful to improve the query performance. For example, say you want to retrieve employees by their sales (a regular report), then you can use this 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 the Customer key is the Primary key (means Clustered), but you must retrieve records by yearly Income.

In this situation, you must 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 is 79000, it will look for 79000 in the second table, and after finding the record, it will check the row address. Second, it will retrieve the record (from the First table) at that row address.

Two Tables Data 22

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

Table Data and Definition in Object Explorer 1

Create Non Clustered Index in SQL Server

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

CREATE NONCLUSTERED INDEX Index_Name
ON Table_Name (Column_Name(s) ASC/DESC)

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

-- Create Non Clustered Indexes
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.

EXECUTE SP_HELPINDEX to View 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.

EXECUTE SP_HELP to get Table Information 4

Create Composite Non Clustered Index in SQL Server

You can create it on multiple columns. So, you can point the same to Multiple columns. In this example, we add a composite non clustered index on the Professional and Yearly Income columns.

CREATE NONCLUSTERED INDEX IX_CustomerRecord_ProfessionIncome
ON CustomerRecord ([Profession] DESC, [Yearly Income] ASC)
Messages
--------
Command(s) completed successfully.

Now you can see them.

Create Composite Non Clustered Index in SQL Server 8

Create Multiple Non Clustered Indexes

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

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.

Create Multiple Non Clustered Indexes in SQL Server 10

Create Non Clustered Index in SQL Server Management Studio

Let me show you how to create and delete it using Management Studio

Use Object Explorer

This example explains how to create a Non Clustered using the Management Studio.

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

using Management Studio 11

Once you choose the option, a new window will open, as we showed below. Please change the Name as per your requirements.

Click the Add Button 12

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

Choose Column 13

Please change the sort order.

Change Sort Order and Click Ok 14

Now you can see it

View in Object Explorer Folder 15

Using Table Designer

Please extend the table on which you require to create, and Right-click on it will open the context menu. Next, 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.

Right click on Column Name and Choose keys option 16

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

Non Clustered Index in SQL Server 17

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

Choose the Column Name and Sort Order 18

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

Set Create as Clustered Option to No 19

See the newly created one

Sp_HELPINDEX Stored Procedure 20

Delete or Drop Non Clustered Index in SQL Server

Please use the DROP INDEX statement to delete or drop.

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

Use Management Studio Example

Within the object explorer, Expand the Database folder and choose the table where it is presented. Next, expand the Indexes folder, and right-click on the name will open the context menu. Please select the Delete option.

Delete Non Clustered Index in SQL Server 5

Once you select the Delete option, the Delete Object window opens. Click OK to delete the Non clustered Index.

Delete Object Window 6

Comments are closed.