Create nonclustered indexes in SQL Server can be done by using SQL Server Management Studio or Transact-SQL. Lets take look in to the details.

Bobcares gives solutions to any queries as part of our Server administration services.

Let’s take a deeper look on how to create nonclustered indexes in SQL Server

Create NonClustered Indexes in SQL server

A nonclustered index is a type of index that exists independently of the data in a table and reorders one or more columns. Nonclustered indexes can often speed up data retrieval when compared to searching the underlying table.

Nonclustered indexes are typically created to improve the performance of frequently used queries that aren’t covered by a clustered index or to locate rows in a table that aren’t covered by a clustered index (called a heap). On a table or indexed view, you can create multiple nonclustered indexes.

The data rows are not physically sorted in the non-clustered index. It separates the table data into a key-value structure, with the key containing the column values and each value containing a pointer to the data row containing the actual value.

Syntax
A non-clustered index has the same syntax as a clustered index. Simply replace “NONCLUSTERED” for “CLUSTERED.” To create a new non-clustered index on a table, use the following syntax.

CREATE NONCLUSTERED INDEX
ON (column)

Create a Nonclustered Index using SSMS

1. Firstly, Launch SSMS. Create a connection to the database. Expand the table where you want to make a non-clustered index in Object Explorer.
2. Select the Indexes folder with the right-click menu. As shown below, choose New Index, then Non-Clustered Index.
3. On the General page of the New Index dialogue box, under Index Name, give the new index a name.
4. As shown below, click the Add button under Index Key Columns.
5. Check the checkbox for the column(s) on which the non-clustered index should be created in the Select Columns from Table dialogue box.
6. Finally, Save the table by clicking Ok.

Create a nonclustered index by using the Table Designer

1. 1. Expand the database that contains the table for which you want to create a nonclustered index in Object Explorer.
   2. Then, expand the Tables folder.
   3. Select Design from the context menu of the table on which you want to create a nonclustered index.
   4. Select Indexes/Keys from the context menu when you right-click on the column you want to create the nonclustered index on.
   5. Click Add in the Indexes/Keys dialogue box.
   6. In the Selected Primary/Unique Key or Index text box, select the new index.
   7. Select Create as Clustered from the drop-down list to the right of the property in the grid.
   8. Click on close
   9. Click Savetable_name from the File menu.

[Looking for a solution to another query? We are just a click away.]

Conclusion

To sum up, we can create nonclustered indexes in SQL Server by using SQL Server Management Studio or Transact-SQL..