Query primary keys, unique constraints and indexes

Observed sign or problem

We need to understand how each row is identified and what structures exist to preserve uniqueness or support searches. Seeing just the name of an index is not enough: the order of its columns, the included columns, and their filters also matter.

What we need to distinguish

A primary key identifies every row uniquely, and a UNIQUE constraint prevents a combination of values from repeating. An index organizes values to make rows easier to access; it can support a constraint without being a model rule by itself.

A primary key and a unique constraint are supported by unique indexes, but not every unique index is a constraint. Additionally, a normal index may sort key columns and include others solely to cover a query.

Query

DECLARE @Tabla NVARCHAR(517) = N'dbo.Producto';
DECLARE @IdTabla INT = OBJECT_ID(@Tabla, N'U');

IF @IdTabla IS NULL
    THROW 50000, 'La tabla no existe o no es visible en esta base.', 1;

SELECT
    i.name AS Indice,
    CASE
        WHEN kc.type = N'PK' THEN N'LLAVE PRIMARIA'
        WHEN kc.type = N'UQ' THEN N'RESTRICCION UNICA'
        WHEN i.is_unique = 1 THEN N'INDICE UNICO'
        ELSE N'INDICE'
    END AS Clasificacion,
    i.type_desc AS TipoIndice,
    i.is_unique AS EsUnico,
    i.has_filter AS TieneFiltro,
    i.filter_definition AS Filtro,
    c.name AS Columna,
    ic.key_ordinal AS OrdenLlave,
    ic.is_descending_key AS EsDescendente,
    ic.is_included_column AS EsIncluida,
    i.is_disabled AS EstaDeshabilitado
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
    ON ic.object_id = i.object_id
   AND ic.index_id = i.index_id
INNER JOIN sys.columns AS c
    ON c.object_id = ic.object_id
   AND c.column_id = ic.column_id
LEFT JOIN sys.key_constraints AS kc
    ON kc.parent_object_id = i.object_id
   AND kc.unique_index_id = i.index_id
WHERE i.object_id = @IdTabla
  AND i.index_id > 0
  AND i.is_hypothetical = 0
ORDER BY
    i.index_id,
    ic.is_included_column,
    ic.key_ordinal,
    ic.index_column_id;

Parameters or permissions

A heap is a table without a clustered index. A hypothetical index retains a definition for estimation but has no usable structure; a materialized index occupies storage and can answer queries.

Change @Tabla to a two-part name. The account needs visibility on the table. The query ignores the heap (index_id = 0) and hypothetical indexes; focuses on materialized indexes with registered columns.

How to read the result

The leaf level is the final part of an index where its rows or locators are stored. An included column is kept there without participating in search order.

Each index occupies one or more rows. Key columns have OrdenLlave greater than zero. Your position matters: an index on (IdCliente, FechaPedido) does not offer exactly the same possibilities as another on (FechaPedido, IdCliente).

EsIncluida = 1 identifies a column that does not participate in the key order, but is stored at the leaf level to cover certain queries. In those rows OrdenLlave appears as zero.

EsUnico = 1 prevents duplicates in the index key, subject to the behavior of NULL and, if it exists, Filtro. The Clasificacion column separates that property from a declared primary key or unique constraint.

A filtered index only contains rows that satisfy Filtro. A disabled index retains its definition, but is not available to maintain or answer queries until it is rebuilt.

What this does establish

It allows you to describe visible keys and indexes of the table, their classification, order, address, included columns, uniqueness, filter and state. It also allows you to check whether a declared constraint is supported by the expected index.

What this still does not establish

Fragmentation describes how dispersed the physical page order is. An execution plan describes the strategy chosen by SQL Server for a query.

The existence of an index does not prove that a query uses it, that its order is appropriate, or that its maintenance cost is justified. The absence of a primary key also does not necessarily mean that another business uniqueness rule does not exist.

This query does not measure reads, writes, fragmentation, or execution plans. It also does not recommend creating or dropping indexes alone.

Next reasonable check

It relates each index to the queries that are actually executed. Then review plans, usage statistics, and modification volume before proposing changes. If an integrity rule is missing, first validate existing data and expected system behavior.

Search SQL Server