How to Create a Nonclustered Index in SQL Server?

 

How to Create a Nonclustered Index in SQL Server?

In your HumanResources.Department table, you already have a Clustered Index on DepartmentID because it is the Primary Key. Now, let’s create a Nonclustered Index on another column to speed up queries.

---

🔹 Syntax to Create a Nonclustered Index

To create a Nonclustered Index, use the following command:

CREATE NONCLUSTERED INDEX IX_Department_DepartmentName  
ON HumanResources.Department (DepartmentName);

✔ This creates a Nonclustered Index on DepartmentName.
✔ It helps queries that filter or sort by DepartmentName to execute faster.

---

🔹 Creating a Nonclustered Index on Multiple Columns

You can create a composite index (index on multiple columns) to speed up queries that involve multiple columns.

CREATE NONCLUSTERED INDEX IX_Department_DepartmentName_Location  
ON HumanResources.Department (DepartmentName, Location);

✔ Useful if queries frequently filter using both DepartmentName and Location.

---

🔍 Checking If the Index Exists

After creating the index, verify it using:

SELECT * FROM sys.indexes  
WHERE object_id = OBJECT_ID('HumanResources.Department');

---

🔄 When Should You Use a Nonclustered Index?

Use a Nonclustered Index when: ✔ Queries filter or sort by a column frequently.
✔ The column is not part of the Primary Key (because the Primary Key is already a Clustered Index).
✔ Queries return a small subset of rows, not the entire table.

---

📝 Summary

✔ CREATE NONCLUSTERED INDEX is used to create a nonclustered index.
✔ Nonclustered Indexes improve search performance on frequently used columns.
✔ Use Composite Indexes if multiple columns are used together in queries.

Would you like help analyzing which columns need indexing? 😊