How to create a nonclustered index in SQL Server

João Barros 12 de March de 2024 2 min read

Slow queries in SQL Server almost always share the same cause: a missing index. A nonclustered index is the fastest way to speed up lookups on columns that are not part of the primary key.

Prerequisites

SQL Server (2016 or later) and SQL Server Management Studio (SSMS) or Azure Data Studio.
ALTER permission on the table where you will create the index.
A table with enough data to see the difference (a few thousand rows).

Step 1: Identify the column to index

Pick the column that appears often in WHERE or JOIN clauses. In this example we will index the Email column of the Clientes table.

Step 2: Create the nonclustered index

Use the CREATE NONCLUSTERED INDEX statement, giving the index a descriptive name:

CREATE NONCLUSTERED INDEX IX_Clientes_Email
    ON dbo.Clientes (Email);

If your queries return more columns, add them with INCLUDE to build a covering index:

CREATE NONCLUSTERED INDEX IX_Clientes_Email_Inc
    ON dbo.Clientes (Email)
    INCLUDE (Nome, Telefone);

Step 3: Confirm the query uses the index

Turn on the actual execution plan in SSMS (Ctrl+M) and run the query:

SELECT Nome, Telefone
FROM dbo.Clientes
WHERE Email = 'ana@exemplo.pt';

Verify the result

In the execution plan you should see an Index Seek on IX_Clientes_Email instead of a Table Scan. An Index Seek means SQL Server jumps straight to the right rows — much faster.

Conclusion

Creating the right index is the highest-return optimization in SQL Server. Index the columns you actually filter on and avoid indexing everything — each index speeds up reads but makes writes slightly slower. Which of your slowest queries would benefit from an index today?