SQL Server NonClustered Index

In previous post, we have learnt about Index and its types. In this post, we will learn about non-clustered index. In SQL Server, a non-clustered index is a separate structure from the actual table data, and a table can have multiple non-clustered indexes.

Syntax:

CREATE NONCLUSTERED INDEX [IndexName]
ON TableName (Column1, Column2, ...);

Let's break down this syntax:

• CREATE NONCLUSTERED INDEX: This statement is used to create a non-clustered index.
• IndexName: Replace this with a meaningful name for your index. It's optional, and if you omit it, SQL Server will generate a name.
• ON TableName: Specify the name of the table on which you want to create the non-clustered index.
• (Column1, Column2, ...): List the columns on which the index will be created. The order of columns is not as critical as it is in a clustered index because the data in the table is not physically reordered based on the non-clustered index.

Example: Suppose you have a table called Employee with columns ID, LastName, and DepartmentID, and you want to create a non-clustered index on the LastName column:

CREATE NONCLUSTERED INDEX IX_Employee_LastName
ON Employee (LastName);

This will create a non-clustered index named IX_Employee_LastName on the Employee table, using the LastName column. The data in the table remains physically ordered based on the clustered index or heap structure (if no clustered index is present), and the non-clustered index provides a separate structure for efficient lookups based on the specified column.

Points to remember:

• The indexed column must be chosen judicially as per the need of query.
• Since separate index structure is created for non clustered index, it has additional overhead because when data in table is modified, index structure is modified.

Example of Non-Clustered Index

Create the following table.

CREATE TABLE EmployeesNonCI(
     id int,
     first_name varchar(10),
     last_name varchar(10),
     salary int
)

Insert rows into the table.

INSERT EmployeesNonCI Values(2, 'Ram', 'Kumar', 2500)
INSERT EmployeesNonCI Values(1, 'Gaurav', 'Singh',4500)
INSERT EmployeesNonCI Values(5, 'Rima', 'Jain', 5400)

We get the following rows.

SELECT * FROM EmployeesNonCI

Now we create non clustered index on first_name column.

create nonclustered index ncidx_Demo
on EmployeesNonCI(first_name asc)

Look at the indexes folder. We find that non clustered index is created by default name.

Now run the following query.

SELECT * FROM EmployeesNonCI

Note that we do not find that first name sorted in ascending order. Why?

The non clustered index is created separately. When query is used then the index on first_name column is used.

Now we create another non clustered index on last_name column.

create nonclustered index ncidx_LN
on EmployeesNonCI(last_name)

Look at the indexes folder. We find that another non clustered index is created by default name.

In the next post we will learn about Unique Index in SQL Server.