A Non Clustered Index in SQL Server stores the index structure separately from the data that is physically stored in a table. SQL Server allows you to create almost 999 non-clustered indexes per table. This type of indexes are used to improve the query performance. Say, you want to retrieve employees by their sales (which is a regular report) then you can use non clustered index on Sales to retrieve the records quickly.
Let me show you, what will happen if you create a Non clustered Index. For example, if you had Customer table with following records. Assume Customer key is Primary key (means Clustered Index) but your requirement is to retrieve records by yearly Income. In this situation you have to create a non clustered index on Yearly Income column. This will store the Yearly income records in separate location along with the row address. When you ask for customer record whose Income = 79000. First, SQL will look for 79000 in second table and after finding the record it will check the row address. Second, it will retrieve record (from First table) at that row address
TIP : You can create 999 Non Clustered Indexes per Table.
In this article we will show you, How to create and delete (drop) a non clustered index in SQL Server using Transact SQL Query, and SQL Server Management Studio
Create Non Clustered Index in SQL Server
In this example we will show 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)
Below statement will create a Non Clustered Index on Yearly Income Column present in Customer record table
-- Create Non Clustered Indexes in SQL Server CREATE NONCLUSTERED INDEX IX_CustomerRecord_YearlyIncome ON CustomerRecord ([Yearly Income] ASC)
Let me use the sp_helpindex stored procedure to check the indexes on CustomerRecord table.
EXECUTE SP_HELPINDEX CustomerRecord
As you can see from the below screenshot, Yearly Income column has Non Clustered index.
You can also use SP_HELP stored procedure to check the same.
EXECUTE SP_HELP CustomerRecord
This will show all the information about the table.
Create Composite Non Clustered Index in SQL Server
SQL allows 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 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)
Now you can see them.
Create Multiple Non Clustered Indexes in SQL Server
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)
Now you can see multiple indexes
Create Non Clustered Index using SQL Server Management Studio
Use Object Explorer to Create Non Clustered Index in SQL Server
In this example we will show you, How to create a Non Clustered Index using the SQL server management Studio. To do so, Please go to the Object Explorer and expand the Database folder. Please expand the table on which you want 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 Non-Clustered Index.. option as shown below
Once you select Non-Clustered Index.. option, a new window called New Index will be opened as we shown below. Please change the Index Name as per your requirements
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.
Please change the sort order as per your requirement
Now you can see the non clustered index
Create SQL Non Clustered Index using Table Designer
Please expand the table on which you want to create a Non Clustered Index, and Right click on it will open the context menu. Please select Design option to open the table in Designer window. Next, Right click on the column will open the context menu. Please select the Set Indexes / Keys… option as we shown below.
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.
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.
Under the Table Designer, Please change the Create As Clustered option to No
Now you can see them
Delete Non Clustered Index in SQL Server
Please use the DROP INDEX statement to delete or drop Non Clustered Index
-- DROP Non Clustered Index in SQL Server DROP INDEX CustomerRecord.IX_CustomerRecord_Profession
Use SSMS to Delete Non Clustered Index in SQL Server
Within the object explorer, Expand the Database folder, and select 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
Once you select the Delete option, Delete Object window will be opened. Click OK to delete the Index.
Thank You for Visiting Our Blog