Skip to content
DBAExperts
SQL Server Performance

How to create an index in SQL Server (clustered vs. nonclustered)

Indexes speed up queries, but each one has a cost in writes and storage. Here is the difference between clustered and nonclustered indexes and how to create them.

  1. 1

    Understand clustered vs. nonclustered

    The clustered index defines the physical order of the table: there can be only one, usually the primary key. A nonclustered index is a separate structure that points to the rows: you can have several.

    -- Un indice clustered por tabla (suele ser la PK)
CREATE CLUSTERED INDEX IX_Ventas_Fecha
ON dbo.Ventas (Fecha);
  2. 2

    Create a nonclustered index for your queries

    Index the columns that appear in WHERE clauses and JOINs. Column order matters: put the most selective column, or the equality filter, first.

    CREATE NONCLUSTERED INDEX IX_Ventas_Cliente_Fecha
ON dbo.Ventas (ClienteID, Fecha);
  3. 3

    Use included columns (INCLUDE) to cover the query

    INCLUDE adds columns to the leaf level of the index without sorting them, so the query can be resolved without going back to the table (a covering index).

    CREATE NONCLUSTERED INDEX IX_Ventas_Cliente_Cubridor
ON dbo.Ventas (ClienteID)
INCLUDE (Total, Estatus);
  4. 4

    Check whether the index is being used

    An index nobody uses only weighs down your writes. Check the usage statistics before making it permanent.

    SELECT OBJECT_NAME(s.object_id) AS tabla, i.name AS indice,
       s.user_seeks, s.user_scans, s.user_updates
FROM sys.dm_db_index_usage_stats s
JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE s.database_id = DB_ID();

// common mistake

More indexes is not always better. Each index slows down INSERTs, UPDATEs, and DELETEs, and takes up space. Avoid duplicate or nearly identical indexes. Creating indexes on large tables causes blocking: use WITH (ONLINE = ON) on Enterprise to avoid interrupting operations.

// when it's worth an expert

Designing the right set of indexes requires analyzing your real queries and their plans. At dba.mx we do index tuning with evidence (not guesswork), so they perform without bloating your writes. Fixed price.

Book an assessment — from USD $550

This guide is for reference and uses example commands. In production, adapt to your version and test in a safe environment first.