CREATE INDEX (Transact-SQL)

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Crea un índice relacional en una tabla o una vista. También se denomina índice de almacén de filas porque es un índice de árbol B agrupado o no agrupado. Puede crear un índice de almacén de filas antes de que haya datos en la tabla. Utilice un índice de almacén de filas para mejorar el rendimiento de las consultas, especialmente cuando las consultas hacen la selección en columnas específicas o requieren que los valores se ordenen en un orden concreto.

Nota:

La documentación utiliza el término árbol B generalmente en referencia a los índices. En los índices del almacén de filas, el motor de la base de datos implementa un árbol B+. Esto no se aplica a los índices de almacén de columnas ni a los índices de tablas optimizadas para memoria. Para obtener más información, consulte la guía de diseño y arquitectura de índices de SQL Server y Azure SQL.

En estos momentos, Azure Synapse Analytics y Analytics Platform System (PDW) no admiten las restricciones UNIQUE. Los ejemplos que hacen referencia a restricciones únicas solo se aplican a SQL Server, Azure SQL Database e Instancia administrada de Azure SQL.

Para obtener información sobre las directrices de diseño de índices, consulte la guía de diseño del índice de SQL Server.

Ejemplos:

1. Cree un índice no agrupado en una tabla o vista.

   CREATE INDEX index1 ON schema1.table1 (column1);
   

2. Cree un índice agrupado en una tabla y use un nombre de tres partes para la tabla.

   CREATE CLUSTERED INDEX index1 ON database1.schema1.table1 (column1);
   

3. Cree un índice no agrupado con una restricción UNIQUE y especifique el criterio de ordenación.

   CREATE UNIQUE INDEX index1 ON schema1.table1 (column1 DESC, column2 ASC, column3 DESC);
   

Escenario clave:

A partir de SQL Server 2016 (13.x), en Azure SQL Database y en Azure SQL Managed Instance, puede usar un índice no clúster en un índice de almacén de columnas para mejorar el rendimiento de las consultas de almacenamiento de datos. Para más información, consulte Índices de almacén de columnas: almacenamiento de datos.

Para los tipos adicionales de índices, consulte:

• CREATE XML INDEX
• CREATE SPATIAL INDEX
• CREATE COLUMNSTORE INDEX

Convenciones de sintaxis de Transact-SQL

Sintaxis

Sintaxis para SQL Server, Azure SQL Database y Azure SQL Managed Instance

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON <object> ( column [ ASC | DESC ] [ ,...n ] )
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WHERE <filter_predicate> ]
    [ WITH ( <relational_index_option> [ ,...n ] ) ]
    [ ON { partition_scheme_name ( column_name )
         | filegroup_name
         | default
         }
    ]
    [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

[ ; ]

<object> ::=
{ database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }

<relational_index_option> ::=
{
    PAD_INDEX = { ON | OFF }
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | STATISTICS_INCREMENTAL = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
  | RESUMABLE = { ON | OFF }
  | MAX_DURATION = <time> [MINUTES]
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE }
     [ ON PARTITIONS ( { <partition_number_expression> | <range> }
     [ , ...n ] ) ]
  | XML_COMPRESSION = { ON | OFF }
     [ ON PARTITIONS ( { <partition_number_expression> | <range> }
     [ , ...n ] ) ]
}

<filter_predicate> ::=
    <conjunct> [ AND ] [ ...n ]

<conjunct> ::=
    <disjunct> | <comparison>

<disjunct> ::=
        column_name IN (constant ,...n)

<comparison> ::=
        column_name <comparison_op> constant

<comparison_op> ::=
    { IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< }

<low_priority_lock_wait>::=
{
    WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ] ,
                          ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )
}

<range> ::=
<partition_number_expression> TO <partition_number_expression>

Índice relacional compatible con versiones anteriores

Importante

La estructura de sintaxis de índice relacional compatible con versiones anteriores se quitará en una versión futura de SQL Server. Evite usar esta estructura de sintaxis en los nuevos trabajos de desarrollo y piense en modificar las aplicaciones que la usan actualmente. En su lugar, use la estructura de sintaxis especificada en <relational_index_option>.

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON <object> ( column_name [ ASC | DESC ] [ ,...n ] )
    [ WITH <backward_compatible_index_option> [ ,...n ] ]
    [ ON { filegroup_name | "default" } ]

<object> ::=
{
    [ database_name. [ owner_name ] . | owner_name. ]
    table_or_view_name
}

<backward_compatible_index_option> ::=
{
    PAD_INDEX
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB
  | IGNORE_DUP_KEY
  | STATISTICS_NORECOMPUTE
  | DROP_EXISTING
}

Sintaxis para Azure Synapse Analytics y Almacenamiento de datos paralelos

CREATE CLUSTERED COLUMNSTORE INDEX index_name
    ON [ database_name . [ schema ] . | schema . ] table_name
    [ORDER (column[,...n])]
    [WITH ( DROP_EXISTING = { ON | OFF } )]
[;]


CREATE [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON [ database_name . [ schema ] . | schema . ] table_name
        ( { column [ ASC | DESC ] } [ ,...n ] )
    WITH ( DROP_EXISTING = { ON | OFF } )
[;]

Argumentos

UNIQUE

Crea un índice único en una tabla o una vista. Un índice único es aquel en el que no se permite que dos filas tengan el mismo valor de clave del índice.

El motor de base de datos no permite crear un índice único en columnas que ya incluyan valores duplicados, independientemente de si está establecido ONo no IGNORE_DUP_KEY en . Si se intenta, el motor de base de datos muestra un mensaje de error. Se deben quitar los valores duplicados para poder crear un índice único en la columna o columnas.

Una UNIQUE restricción trata NULL como un valor. Si una columna admite valores NULL y existe una UNIQUE restricción en la columna, se permite al máximo una fila con .NULL

CLUSTERED

Crea un índice en el que el criterio de ordenación especificado para las columnas de clave de índice determina el orden de página en la estructura de índice del disco. Las filas de las páginas de la parte inferior o hoja del índice agrupado siempre contienen todas las columnas de la tabla. Las filas de las páginas de los niveles superiores del índice solo contienen columnas de clave.

Una tabla solo puede tener un índice agrupado. Si existe un índice agrupado en una tabla, contiene todos los datos de la tabla. Una tabla sin un índice agrupado se denomina montón.

Una vista con un índice clúster único se denomina vista indizada. Una vista indizada solo puede tener un índice agrupado. La creación de un índice clúster único en una vista materializa físicamente la vista. Es necesario crear un índice clúster único en una vista para poder definir otros índices en la misma vista. Para obtener más información, consulte Creación de vistas indizadas.

Cree el índice clúster antes de crear los índices no clúster. Los índices no agrupados existentes en las tablas se vuelven a generar cuando se crea un índice agrupado, que es una operación que consume muchos recursos si la tabla es grande.

Si no se especifica CLUSTERED, se crea un índice no agrupado.

Nota:

Dado que el índice agrupado contiene todos los datos de la tabla, la creación de un índice agrupado y el uso de la ON partition_scheme_name cláusula o ON filegroup_name mueve eficazmente la tabla del grupo de archivos en el que se creó la tabla al nuevo esquema de partición o al grupo de archivos. Antes de crear tablas o índices en grupos de archivos específicos, compruebe cuáles están disponibles y que esos grupos de archivos tengan suficiente espacio disponible para el índice.

En algunos casos, la creación de un índice agrupado puede habilitar índices deshabilitados previamente. Para obtener más información, consulte Habilitación de índices y restricciones y Deshabilitación de índices y restricciones.

NONCLUSTERED

Crea un índice en el que el criterio de ordenación especificado para las columnas de clave de índice determina el orden de página en la estructura de índice del disco. A diferencia del índice agrupado, las filas de las páginas del nivel hoja de un índice no clúster contienen solo las columnas de clave de índice. Opcionalmente, se puede incluir un subconjunto de columnas que no son de clave mediante la INCLUDE cláusula .

Cada tabla puede tener hasta 999 índices no agrupados, independientemente de cómo se creen los índices: implícitamente con las PRIMARY KEY restricciones y UNIQUE o explícitamente con CREATE INDEX.

Para las vistas indizadas, solo se pueden crear índices no clúster en una vista que ya tenga definido un índice clúster único.

Si no se especifica, el tipo de índice predeterminado es no agrupado.

index_name

El nombre del índice. Los nombres de índice deben ser únicos en una tabla o vista, pero no es necesario que sean únicos en una base de datos. Los nombres de índice deben seguir las reglas de los identificadores.

column

Columna o columnas en las que se basa el índice. Especifique dos o más nombres de columna para crear un índice compuesto sobre los valores combinados de las columnas especificadas. Enumere las columnas que desee incluir en el índice compuesto (en orden de prioridad) entre paréntesis después de table_or_view_name.

Se pueden combinar hasta 32 columnas en la clave de un único índice compuesto. Todas las columnas de una clave del índice compuesto deben encontrarse en la misma tabla o vista. El tamaño máximo permitido de los valores de índice combinados es de 900 bytes para un índice agrupado o de 1700 para un índice no agrupado. Los límites son 16 columnas y 900 bytes para las versiones anteriores a SQL Database y SQL Server 2016 (13.x).

Las columnas de los tipos de datos de objetos grandes (LOB) ntext, text, varchar(max), nvarchar(max), varbinary(max), xml o image no pueden especificarse como columnas de clave para un índice. Además, una definición de vista indizada no puede incluir columnas ntext, text o image , incluso si no se hace referencia a ellas en la CREATE INDEX instrucción .

Puede crear índices en columnas de tipo definido por el usuario CLR si el tipo admite el orden binario. También puede crear índices en columnas calculadas que están definidas como invocaciones de método de una columna de tipo definido por el usuario, siempre que los métodos estén marcados como deterministas y no realicen operaciones de acceso a datos. Para obtener más información sobre la indexación de columnas de tipo definidas por el usuario clR, consulte Tipos definidos por el usuario clR.

[ ASC | DESC ]

Determina la dirección ascendente o descendente del orden de la columna de índice determinada. El valor predeterminado es ASC.

INCLUDE (column [ ,... n ] )

Especifica las columnas que no son de clave que se van a agregar al nivel hoja de un índice no agrupado. El índice no clúster puede ser único o no único.

Los nombres de columna no se pueden repetir en la INCLUDE lista y no se pueden usar simultáneamente como columnas clave y no clave. Los índices no clúster siempre contienen implícitamente las columnas de índice agrupadas si se define un índice agrupado en la tabla. Para obtener más información, consulte Creación de índices con columnas incluidas.

Se admiten todos los tipos de datos, a excepción de text, ntexte image. A partir de SQL Server 2012 (11.x), en Azure SQL Database y en Instancia administrada de Azure SQL, si alguna de las columnas no clave especificadas son varchar(max), nvarchar(max)o tipos de datos varbinary(max), el índice se puede compilar o volver a generar mediante la ONLINE opción .

Las columnas calculadas que son deterministas, y precisas o imprecisas, pueden ser columnas incluidas. Las columnas calculadas derivadas de los tipos de datos image, ntext, text, varchar(max), nvarchar(max), varbinary(max)y xml se pueden incluir siempre y cuando el tipo de datos de columna calculada se pueda permitir como una columna incluida. Para obtener más información, consulte Índices en columnas calculadas.

Para obtener información sobre cómo crear un índice XML, vea CREATE XML INDEX.

WHERE <filter_predicate>

Crea un índice filtrado especificando qué filas se van a incluir en el índice. El índice filtrado debe ser un índice no clúster en una tabla. Crea las estadísticas filtradas para las filas de datos en el índice filtrado.

El predicado de filtro usa lógica de comparación simple y no puede hacer referencia a una columna calculada, una columna de tipo de datos definido por el usuario (UDT), una columna de tipo de datos espacial o una columna de tipo de datos hierarchyid . No se permiten comparaciones con NULL literales que usan los operadores de comparación. Use en su lugar los operadores IS NULL y IS NOT NULL.

A continuación, se muestran algunos ejemplos de predicados de filtro para la tabla Production.BillOfMaterials:

WHERE StartDate > '20000101' AND EndDate <= '20000630'

WHERE ComponentID IN (533, 324, 753)

WHERE StartDate IN ('20000404', '20000905') AND EndDate IS NOT NULL

Los índices filtrados no se aplican a los índices XML ni a los índices de texto completo. En el caso UNIQUE de los índices, solo las filas seleccionadas deben tener valores de índice únicos. Los índices filtrados no admiten la opción IGNORE_DUP_KEY.

ON partition_scheme_name ( column_name )

Especifica el esquema de partición que define los grupos de archivos a los que se asignan las particiones de un índice con particiones. El esquema de partición debe existir dentro de la base de datos mediante la ejecución de CREATE PARTITION SCHEME o ALTER PARTITION SCHEME. column_name especifica la columna de partición del índice. Esta columna debe coincidir con el tipo de datos, la longitud y la precisión del argumento de la función de partición que partition_scheme_name emplea. column_name no está limitado a las columnas de la definición de índice. Se puede especificar cualquier columna de la tabla base, excepto al crear particiones de un índice único, column_name debe elegirse entre las usadas como clave única. Esta restricción permite que Motor de base de datos compruebe la unicidad de los valores de clave en una única partición solamente.

Nota:

Cuando se crean particiones en un índice clúster no único, Motor de base de datos agrega de forma predeterminada la columna de partición a la lista de claves del índice clúster, en caso de que aún no se hubiera especificado. Cuando se crean particiones en un índice no clúster que tampoco es único, Motor de base de datos agrega la columna de partición como una columna sin clave (incluida) del índice, si aún no se especificó.

Si no se especificó partition_scheme_name o filegroup y se crearon particiones en la tabla, el índice se coloca en el mismo esquema de partición y usa la misma columna de partición que en la tabla subyacente.

Nota:

No se puede especificar un esquema de partición en un índice XML. Si se crean particiones en la tabla base, el índice XML usa el mismo esquema de partición que la tabla.

Para obtener más información sobre la creación de particiones de índices, tablas e índices con particiones.

ON filegroup_name

Crea el índice especificado en el grupo de archivos especificado. Si no se ha especificado una ubicación y la tabla o vista no tiene particiones, el índice usa el mismo grupo de archivos que la tabla o vista subyacente. El grupo de archivos debe existir previamente.

ON [valor predeterminado]

Crea el índice especificado en el mismo grupo de archivos o esquema de partición que la tabla o la vista.

El término default, en este contexto, no es una palabra clave. Es un identificador para el grupo de archivos o el esquema con particiones de la tabla o vista y debe delimitarse, como en ON "default" o ON [default]. Si "default" se especifica , la QUOTED_IDENTIFIER opción debe ser ON para la sesión actual. Esta es la configuración predeterminada. Para más información, consulte SET QUOTED_IDENTIFIER.

Nota:

En el contexto de y "default"[default] no indique el grupo de CREATE INDEXarchivos predeterminado de la base de datos. Indican el grupo de archivos o el esquema de partición que usa la tabla base o vista. Esto difiere de CREATE TABLE, donde "default" y [default] colocan la tabla en el grupo de archivos predeterminado de la base de datos.

[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

Especifica la posición de datos FILESTREAM para la tabla cuando se crea un índice clúster. La cláusula FILESTREAM_ON permite mover los datos FILESTREAM a otro esquema de partición o a otro grupo de archivos FILESTREAM.

El filestream_filegroup_name es el nombre de un grupo de archivos FILESTREAM. El grupo de archivos debe tener un archivo definido para el grupo de archivos, usando para ello las instrucciones CREATE DATABASE o ALTER DATABASE; en caso contrario, se produce un error.

Si se crean particiones de la tabla, la cláusula FILESTREAM_ON deberá incluirse y especificar un esquema de partición de grupos de archivos FILESTREAM que utilice la misma función de partición y columnas de partición que el esquema de partición para la tabla. En caso contrario, se produce un error.

Si la tabla no tiene particiones, no se pueden crear particiones en la columna FILESTREAM. Los datos FILESTREAM para la tabla deben estar almacenados en un grupo de archivos único que se especifica en la cláusula FILESTREAM_ON.

FILESTREAM_ON NULL se puede especificar en una instrucción CREATE INDEX si se va a crear un índice agrupado y la tabla no contiene una columna FILESTREAM.

Para obtener más información, vea FILESTREAM (SQL Server).

<object>::=

El objeto completo o no completo que se indexará.

database_name

El nombre de la base de datos.

schema_name

Nombre del esquema al que pertenece la tabla o la vista.

table_or_view_name

Nombre de la tabla o la vista que se va a indexar.

Para crear un índice en una vista, la vista debe definirse con SCHEMABINDING. Es necesario crear un índice clúster único en una vista antes de crear los índices no clúster. Para obtener más información sobre las vistas indizadas, vea Comentarios.

A partir de SQL Server 2016 (13.x), el objeto puede ser una tabla almacenada con un índice de almacén de columnas agrupado.

Azure SQL Database admite el formato de nombre de tres partes <database_name>.<schema_name>.<object_name> cuando <database_name> es el nombre de la base de datos actual o <database_name> es tempdb y <object_name> comienza con # o ##. Si el nombre del esquema es dbo, se puede omitir <schema_name>.

<relational_index_option>::=

Especifica las opciones que se van a utilizar en la creación del índice.

PAD_INDEX = { ON | OFF }

Especifica el relleno del índice. El valor predeterminado es OFF.

• ACTIVAR

  El porcentaje de espacio libre especificado por factor de relleno se aplica a las páginas de nivel intermedio del índice. Si no se especifica FILLFACTOR al mismo tiempo PAD_INDEX se establece en ON, se usa el valor de factor de relleno en sys.indexes.

• Apagado

  Las páginas de nivel intermedio se llenan casi al máximo de su capacidad y dejan espacio suficiente para al menos una fila del tamaño máximo que puede tener el índice, considerando el conjunto de claves incluidas en las páginas de nivel intermedio. Esto también ocurre si PAD_INDEX se establece en ON pero no se especifica el factor de relleno.

La PAD_INDEX opción solo es útil cuando FILLFACTOR se especifica, ya que PAD_INDEX usa el porcentaje especificado por FILLFACTOR. Si el porcentaje especificado para FILLFACTOR no es lo suficientemente grande como para permitir una fila, el motor de base de datos invalida internamente el porcentaje para permitir el mínimo. El número de filas de una página de índice intermedio nunca es menor que dos, independientemente de la baja cantidad de valor de FILLFACTOR.

En la sintaxis compatible con versiones anteriores, WITH PAD_INDEX es equivalente a WITH PAD_INDEX = ON.

FILLFACTOR = fillfactor

Especifica un porcentaje que indica cuánto debe llenar el Motor de base de datos el nivel hoja de cada página de índice durante la creación o nueva generación de los índices. El valor fillfactor debe ser un valor entero de 1 a 100. Los valores de fill factor 0 y 100 son idénticos. Si fillfactor es 100, el Motor de base de datos crea índices con las páginas hoja llenas al máximo de su capacidad.

La configuración de FILLFACTOR solo se aplica cuando se crea o se vuelve a generar el índice. El Motor de base de datos no mantiene dinámicamente el porcentaje especificado de espacio disponible de las páginas.

Para ver la configuración del factor de relleno, use la fill_factor columna en la vista de catálogo sys.indexes .

Importante

La creación de un índice con un FILLFACTOR inferior a 100 aumenta la cantidad de espacio de almacenamiento que ocupan los datos porque el motor de base de datos redistribuye los datos según el factor de relleno cuando crea o vuelve a generar un índice.

Para obtener más información, vea Especificar factor de relleno para un índice.

SORT_IN_TEMPDB = { ON | OFF }

Especifica si se van a almacenar resultados de ordenación temporales en tempdb. El valor predeterminado es OFF excepto hiperescala de Azure SQL Database. Para todas las operaciones de compilación de índices en Hiperescala, SORT_IN_TEMPDB siempre se ON a menos que se use una compilación de índice reanudable. Para las compilaciones de índices reanudables, SORT_IN_TEMPDB siempre se OFF.

• ACTIVAR

  Los resultados de ordenación intermedios que se usan para compilar el índice se almacenan en tempdb. Esto puede reducir el tiempo necesario para crear un índice. Sin embargo, esto aumenta la cantidad de espacio en disco utilizado durante la generación del índice.

• Apagado

  Los resultados de orden intermedios se almacenan en la misma base de datos que el índice.

Además del espacio necesario en la base de datos de usuario para crear el índice, tempdb debe tener aproximadamente la misma cantidad de espacio adicional para contener los resultados de ordenación intermedios. Para obtener más información, consulte SORT_IN_TEMPDB opción para índices.

En la sintaxis compatible con versiones anteriores, WITH SORT_IN_TEMPDB es equivalente a WITH SORT_IN_TEMPDB = ON.

IGNORE_DUP_KEY = { ON | OFF }

Especifica la respuesta de error cuando una operación de inserción intenta insertar valores de clave duplicados en un índice único. La opción IGNORE_DUP_KEY se aplica solamente a operaciones de inserción realizadas tras crear o volver a generar el índice. La opción no tiene efecto cuando se ejecutan CREATE INDEX, ALTER INDEX o UPDATE. El valor predeterminado es OFF.

• ACTIVAR

  Se produce un mensaje de advertencia cuando se inserten valores de clave duplicados en un índice único. Solo las filas que infringen la restricción de unicidad no se insertan.

• Apagado

  Se produce un mensaje de error cuando se insertan valores de clave duplicados en un índice único. INSERT Toda la instrucción se revierte.

IGNORE_DUP_KEY no se puede establecer ON en para los índices creados en una vista, índices no únicos, índices XML, índices espaciales e índices filtrados.

Para ver la configuración de IGNORE_DUP_KEY para un índice, use la columna ignore_dup_key en la vista de catálogo sys.indexes de .

En la sintaxis compatible con versiones anteriores, WITH IGNORE_DUP_KEY es equivalente a WITH IGNORE_DUP_KEY = ON.

STATISTICS_NORECOMPUTE = { ON | OFF}

Especifica si se vuelven a calcular las estadísticas. El valor predeterminado es OFF.

• ACTIVAR

  Las estadísticas obsoletas no se vuelven a calcular automáticamente.

• Apagado

  Se habilita la actualización automática de las estadísticas.

Para restaurar la actualización automática de estadísticas, establezca STATISTICS_NORECOMPUTE en OFF o ejecute UPDATE STATISTICS sin la cláusula NORECOMPUTE.

Advertencia

Si deshabilita la recomputación automática de estadísticas estableciendo STATISTICS_NORECOMPUTE = ON, es posible que impida que el optimizador de consultas elija planes de ejecución óptimos para las consultas que implican la tabla.

Establecer STATISTICS_NORECOMPUTE en ON no impide la actualización de las estadísticas de índice que se producen durante la operación de recompilación del índice.

En la sintaxis compatible con versiones anteriores, WITH STATISTICS_NORECOMPUTE es equivalente a WITH STATISTICS_NORECOMPUTE = ON.

STATISTICS_INCREMENTAL = { ON | OFF }

se aplica a: SQL Server 2014 (12.x) y versiones posteriores, Azure SQL Database e Instancia administrada de Azure SQL

Cuando ON, las estadísticas creadas son por estadísticas de partición. Cuando OFF, el árbol de estadísticas se quita y SQL Server vuelve a calcular las estadísticas. El valor predeterminado es OFF.

Si no se admiten las estadísticas por partición, la opción se omite y se genera una advertencia. Las estadísticas incrementales no se admiten en los casos siguientes:

• Estadísticas creadas con índices que no están alineados por partición con la tabla base.
• Estadísticas creadas sobre bases de datos secundarias legibles AlwaysOn.
• Estadísticas creadas sobre bases de datos de solo lectura.
• Estadísticas creadas sobre índices filtrados.
• Estadísticas creadas sobre vistas.
• Estadísticas creadas sobre tablas internas.
• Estadísticas creadas con índices espaciales o índices XML.

DROP_EXISTING = { ON | OFF }

Es una opción para quitar y volver a generar el índice agrupado o no agrupado existente con las especificaciones de la columna modificada y mantener el mismo nombre para el índice. El valor predeterminado es OFF.

• ACTIVAR

  Especifica que se debe quitar y volver a generar el índice existente, que debe tener el mismo nombre que el parámetro index_name.

• Apagado

  Especifica que no se debe quitar y volver a generar el índice existente. SQL Server muestra un error si ya existe el nombre de índice especificado.

Con DROP_EXISTING, puede cambiar:

• Un índice no agrupado de almacén de filas por un índice agrupado de almacén de filas.

Con DROP_EXISTING, no se puede cambiar:

• Un índice agrupado de almacén de filas por un índice no agrupado de almacén de filas.
• Un índice de almacén de columnas agrupado por cualquier tipo de índice de almacén de filas.

En la sintaxis compatible con versiones anteriores, WITH DROP_EXISTING es equivalente a WITH DROP_EXISTING = ON.

ONLINE = { ON | OFF }

Especifica si las tablas subyacentes y los índices asociados están disponibles para realizar consultas y modificar datos durante la operación de índice. El valor predeterminado es OFF.

Importante

Las operaciones de índices en línea no están disponibles en todas las ediciones de Microsoft SQL Server. Para obtener una lista de las características admitidas por las ediciones de SQL Server, consulte Ediciones y características admitidas de SQL Server 2022.

• ACTIVAR

  Los bloqueos de tabla de larga duración no se mantienen durante la operación de indización. Durante la fase principal de la operación de índice, solo se mantiene un bloqueo de intención compartida (IS) en la tabla de origen. Esto habilita las consultas o actualizaciones en la tabla subyacente y en los índices. Al principio de la operación, se mantiene un bloqueo compartido (S) en el objeto de origen durante un breve período de tiempo. Al final de la operación, durante un breve período de tiempo, se adquiere un bloqueo compartido (S) en el objeto si se crea un índice no clúster. Se adquiere un bloqueo de modificación de esquema (Sch-M) cuando se crea o quita un índice agrupado en línea y cuando se vuelve a generar un índice agrupado o no clúster. ONLINE no se puede establecer ON en cuando se crea un índice en una tabla temporal local.

  Nota:

  Puede usar la opción WAIT_AT_LOW_PRIORITY para reducir o evitar el bloqueo durante las operaciones de índice en línea. Para obtener más información, consulte WAIT_AT_LOW_PRIORITY con operaciones de índice en línea.

• Apagado

  Los bloqueos de tabla se aplican durante la operación de índice. Una operación de índice sin conexión que crea, vuelve a generar o quita un índice agrupado, espacial o XML, o recompila o quita un índice no clúster, adquiere un bloqueo de modificación de esquema (Sch-M) en la tabla. Esto evita que todos los usuarios tengan acceso a la tabla subyacente durante la operación. Una operación de índice sin conexión que crea un índice no clúster adquiere inicialmente un bloqueo compartido (S) en la tabla. Esto evita modificaciones de la definición de tabla subyacente, pero permite leer y modificar los datos de la tabla mientras la compilación del índice está en curso.

Para obtener más información, consulte Realizar operaciones de índice en línea y directrices de para las operaciones de índice en línea.

Los índices, incluidos los índices de las tablas temp globales, se pueden crear en línea, salvo en los casos siguientes:

• Índice XML
• Índice de una tabla temporal local
• Índice clúster único inicial en una vista
• Índices clúster deshabilitados
• Índices de almacén de columnas agrupados en SQL Server 2017 (14.x)) y versiones anteriores
• Índices de almacén de columnas no agrupados en SQL Server 2016 (13.x)) y versiones anteriores
• Índice clúster, si la tabla subyacente contiene tipos de datos LOB (image, ntext, text) y tipos de datos espaciales
• Las columnas varchar(max) y varbinary(max) no pueden formar parte de una clave de índice. En SQL Server (a partir de SQL Server 2012 (11.x)), en Azure SQL Database y en Azure SQL Managed Instance, cuando una tabla contiene columnas varchar(max) o varbinary(max), se puede compilar o volver a generar un índice agrupado que contenga otras columnas mediante la ONLINE opción .
• Índices no agrupados en una tabla con un índice de almacén de columnas agrupado

Para obtener más información, consulte Funcionamiento de las operaciones de índice en línea.

RESUMABLE = { ON | OFF }

se aplica a: SQL Server 2019 (15.x) y versiones posteriores, Azure SQL Database e Instancia administrada de Azure SQL

Especifica si una operación de índice en línea se puede reanudar. Para obtener más información, consulte Reanudable index operations (Operaciones de índice reanudables ) y Resumable index considerations (Consideraciones sobre índices reanudables).

• ACTIVAR

  la operación de índice se puede reanudar.

• Apagado

  La operación de índice no se puede reanudar.

MAX_DURATION = time [MINUTES] usado con RESUMABLE = ON (requiere ONLINE = ON)

se aplica a: SQL Server 2019 (15.x) y versiones posteriores, Azure SQL Database e Instancia administrada de Azure SQL

Especifica durante cuánto tiempo, en minutos, se ejecuta una operación de índice reanudable antes de pausar.

ALLOW_ROW_LOCKS = { ON | OFF }

Especifica si se permiten los bloqueos de fila. El valor predeterminado es ON.

• ACTIVAR

  Los bloqueos de fila se admiten al obtener acceso al índice. El Motor de base de datos determina cuándo se usan los bloqueos de fila.

• Apagado

  No se usan los bloqueos de fila.

ALLOW_PAGE_LOCKS = { ON | OFF }

Especifica si se permiten bloqueos de página. El valor predeterminado es ON.

• ACTIVAR

  Los bloqueos de página se permiten al obtener acceso al índice. Motor de base de datos determina el momento en que se usan los bloqueos de página.

• Apagado

  No se utilizan bloqueos de página.

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }

se aplica a: SQL Server 2019 (15.x) y versiones posteriores, Azure SQL Database e Instancia administrada de Azure SQL

Especifica si se va a optimizar para evitar la contención de inserción de última página. El valor predeterminado es OFF. Consulte la sección Claves secuenciales para obtener más información.

MAXDOP = max_degree_of_parallelism

Invalida la grado máximo de paralelismo opción de configuración para la operación de índice. Para obtener más información, vea Establecer la opción de configuración del servidor Grado máximo de paralelismo. Use MAXDOP para limitar el grado de paralelismo y el consumo de recursos resultante para una operación de compilación de índice.

max_degree_of_parallelism puede tener estos valores:

• 1

  Suprime la generación de planes paralelos.

• >1

  Restringe el grado máximo de paralelismo usado en una operación de índice paralelo al número especificado o menos en función de la carga de trabajo del sistema actual.

• 0 (predeterminado)

  Usa el grado de paralelismo especificado en el nivel de servidor, base de datos o grupo de cargas de trabajo, a menos que se reduzca en función de la carga de trabajo del sistema actual.

Para obtener más información, consulte Configuración de operaciones de índice en paralelo.

Nota:

Las operaciones de índices en paralelo no están disponibles en todas las ediciones de Microsoft SQL Server. Para obtener una lista de las características admitidas por las ediciones de SQL Server, consulte Ediciones y características admitidas de SQL Server 2022.

DATA_COMPRESSION

Especifica la opción de compresión de datos para el índice, número de partición o intervalo de particiones especificado. Las opciones son las siguientes:

• Ninguno

  No se comprimen el índice ni las particiones especificadas. Esto no se aplica a los índices de almacén de columnas.

• ROW

  El índice o las particiones especificadas se comprimen mediante la compresión de fila. Esto no se aplica a los índices de almacén de columnas.

• PAGE

  El índice o las particiones especificadas se comprimen mediante la compresión de página. Esto no se aplica a los índices de almacén de columnas.

• COLUMNSTORE

  se aplica a: SQL Server 2014 (12.x) y versiones posteriores, Azure SQL Database e Instancia administrada de Azure SQL

  Solo se aplica a los índices de almacén de columnas, incluidos los clúster y no clúster.

• COLUMNSTORE_ARCHIVE

  se aplica a: SQL Server 2014 (12.x) y versiones posteriores, Azure SQL Database e Instancia administrada de Azure SQL

  Solo se aplica a los índices de almacén de columnas, incluidos los clúster y no clúster. COLUMNSTORE_ARCHIVE comprime aún más la partición especificada en un tamaño menor. Esto se puede usar para el archivado o para otras situaciones que requieran un tamaño de almacenamiento mínimo y pueda permitirse más tiempo para el almacenamiento y recuperación.

Para más información sobre la compresión, vea Compresión de datos.

XML_COMPRESSION

se aplica a: SQL Server 2022 (16.x) y versiones posteriores, Azure SQL Database e Instancia administrada de Azure SQL

Especifica la opción de compresión de XML para el índice especificado que contiene una o varias columnas de tipo de datos xml. Las opciones son las siguientes:

• ACTIVAR

  El índice o las particiones especificadas se comprimen mediante la compresión de XML.

• Apagado

  Los índices o particiones especificadas no se comprimen mediante compresión XML.

ON PARTITIONS ( { <partition_number_expression> | <range> } [ ,...n ] )

Especifica las particiones a las que se aplica el valor DATA_COMPRESSION o XML_COMPRESSION. Si el índice no tiene particiones, el ON PARTITIONS argumento genera un error. Si no se proporciona la cláusula ON PARTITIONS, la opción DATA_COMPRESSION o XML_COMPRESSION se aplica a todas las particiones de un índice con particiones.

<partition_number_expression> se puede especificar de estas maneras:

• Proporcionar el número de una partición, por ejemplo: ON PARTITIONS (2).
• Proporcionar los números de partición para varias particiones individuales separadas por comas, por ejemplo: ON PARTITIONS (1, 5).
• Proporcione los rangos y las particiones individuales, por ejemplo: ON PARTITIONS (2, 4, 6 TO 8).

<range> se puede especificar como números de partición separados por la palabra clave TO, por ejemplo: ON PARTITIONS (6 TO 8).

Para establecer diferentes tipos de compresión de datos para distintas particiones, especifique la opción DATA_COMPRESSION más de una vez, por ejemplo:

REBUILD WITH
(
DATA_COMPRESSION = NONE ON PARTITIONS (1),
DATA_COMPRESSION = ROW ON PARTITIONS (2, 4, 6 TO 8),
DATA_COMPRESSION = PAGE ON PARTITIONS (3, 5)
);

También puede especificar la opción XML_COMPRESSION más de una vez, por ejemplo:

REBUILD WITH
(
XML_COMPRESSION = OFF ON PARTITIONS (1),
XML_COMPRESSION = ON ON PARTITIONS (2, 4, 6 TO 8),
XML_COMPRESSION = OFF ON PARTITIONS (3, 5)
);

Observaciones

Al crear el plan de consulta para la CREATE INDEX instrucción , el optimizador de consultas puede optar por examinar otro índice en lugar de realizar un examen de tabla. La operación de ordenación se puede eliminar en algunas situaciones. En los equipos de varios procesadores, CREATE INDEX puede usar paralelismo para las operaciones de examen y ordenación asociadas a la creación del índice, de la misma manera que lo hacen otras consultas. Para obtener más información, consulte Configuración de operaciones de índice en paralelo.

Es CREATE INDEX posible que la operación se registre mínimamente si el modelo de recuperación de la base de datos está establecido en bulk-logged o simple.

Los índices se pueden crear en una tabla temporal. Cuando la tabla se quita o sale del ámbito, se quitan los índices.

Un índice agrupado se basa en una variable de tabla cuando se agrega una restricción de clave principal. Del mismo modo, un índice no agrupado se basa en una variable de tabla cuando se agrega una restricción única. Cuando la variable de tabla sale del ámbito, se quitan los índices.

Los índices admiten propiedades extendidas.

CREATE INDEX no se admite en Microsoft Fabric.

Índices clúster

La creación de un índice clúster en una tabla (montón) o la eliminación y nueva creación de un índice clúster existente requiere área de espacio adicional disponible en la base de datos para acomodar la ordenación de datos y una copia temporal de la tabla original o datos del índice clúster existente. Para obtener más información sobre los índices agrupados, consulte Creación de índices agrupadosy la guía de diseño y arquitectura de índices de SQL Server.

Índices no clúster

A partir de SQL Server 2016 (13.x), en Azure SQL Database y en Azure SQL Managed Instance, puede crear un índice no clúster en una tabla almacenada como un índice de almacén de columnas agrupado. Si primero crea un índice no agrupado en una tabla almacenada como un montón o un índice agrupado, el índice persiste si posteriormente convierte la tabla en un índice de almacén de columnas agrupado. Además, no es necesario quitar el índice no agrupado al volver a generar el índice de almacén de columnas agrupado.

La opción FILESTREAM_ON no es válida para crear un índice no agrupado en una tabla almacenada como un índice de almacén de columnas agrupado.

Índices únicos

Cuando existe un índice único, el motor de base de datos comprueba si hay valores duplicados cada vez que se agregan o modifican los datos. Las operaciones que generarían valores de clave duplicados se revierten y el motor de base de datos devuelve un mensaje de error. Esto es cierto incluso si la operación de adición o modificación de datos cambia muchas filas, pero solo provoca un duplicado. Si se intenta insertar filas cuando hay un índice único con la IGNORE_DUP_KEY opción establecida ONen , se omiten las filas que infringen el índice único.

Índices con particiones

La creación y el mantenimiento de los índices con particiones son similares a los de las tablas con particiones pero, al igual que en índices ordinarios, éstos son tratados como objetos de base de datos independientes. Puede tener un índice con particiones en una tabla que carezca de particiones, y puede tener un índice sin particiones en una tabla que tenga particiones.

Si crea un índice en una tabla con particiones y no especifica un grupo de archivos en el que quiere ubicar el índice, se crean particiones en el índice de la misma manera que en la tabla subyacente. Esto se debe a que, de manera predeterminada, los índices se ubican en los mismos grupos de archivos que sus tablas subyacentes, y en una tabla con particiones del mismo esquema de partición que usa las mismas columnas de partición. Cuando el índice usa el mismo esquema y columna de partición que la tabla, el índice está alineado con la tabla.

Advertencia

La creación y regeneración de índices no alineados en una tabla con más de 1.000 particiones es posible, pero no se admite. Si se hace, se puede degradar el rendimiento o consumir excesiva memoria durante estas operaciones. Se recomienda usar solo índices alineados cuando el número de particiones supere los 1000.

Cuando se crean particiones en un índice clúster no único, el Motor de base de datos agrega de forma predeterminada las columnas de partición a la lista de claves del índice clúster, en caso de que no se hubieran especificado aún.

Se pueden crear vistas indizadas en tablas con particiones de la misma manera que se hace con índices en tablas. Para obtener más información sobre los índices con particiones, vea Tablas e índices con particiones y la guía de diseño y arquitectura de índices de SQL Server.

Cuando se crea o se vuelve a generar un índice, la consulta optimiza las estadísticas de actualizaciones en el índice. Para un índice con particiones, el optimizador de consultas usa el algoritmo de muestreo predeterminado en lugar de examinar todas las filas de la tabla para un índice no particionado. Para obtener estadísticas sobre índices con particiones examinando todas las filas de la tabla, use CREATE STATISTICS o UPDATE STATISTICS con la cláusula FULLSCAN.

Índices filtrados

Un índice filtrado es un índice no clúster optimizado, adecuado para las consultas que seleccionan un porcentaje pequeño de las filas de una tabla. Utiliza un predicado de filtro para indizar una parte de los datos de la tabla. Un índice filtrado bien diseñado puede mejorar el rendimiento de las consultas, reducir los costos de almacenamiento y de mantenimiento.

Opciones SET requeridas para los índices filtrados

Las SET opciones de la columna Valor requerido son necesarias siempre que se produzca alguna de las condiciones siguientes:

• Cree un índice filtrado.

• Una INSERTinstrucción , UPDATE, DELETEo MERGE modifica los datos de un índice filtrado.

• El optimizador de consultas usa el índice filtrado para crear el plan de consulta.

  SET opción	Valor requerido	Valor de servidor predeterminado	Valor de OLE DB y ODBC	Valor de BD-Library (Biblioteca de código) predeterminado
  ANSI_NULLS	ON	ON	ON	OFF
  ANSI_PADDING	ON	ON	ON	OFF
  ANSI_WARNINGS 1	ON	ON	ON	OFF
  ARITHABORT	ON	ON	OFF	OFF
  CONCAT_NULL_YIELDS_NULL	ON	ON	ON	OFF
  NUMERIC_ROUNDABORT	OFF	OFF	OFF	OFF
  QUOTED_IDENTIFIER	ON	ON	ON	OFF

  ¹ Si se establece ANSI_WARNINGSON implícitamente en ARITHABORTON cuando el nivel de compatibilidad de la base de datos está establecido en 90 o superior. Si el nivel de compatibilidad de la base de datos se establece en 80 o versiones anteriores, la ARITHABORT opción debe establecerse explícitamente en ON.

Si las SET opciones son incorrectas, se pueden producir las siguientes condiciones:

• Se produce un error al crear el índice filtrado.
• El motor de base de datos genera un error y revierte la INSERTinstrucción , UPDATE, DELETEo MERGE que cambia los datos del índice.
• El optimizador de consultas no tiene en cuenta el índice en el plan de ejecución de ninguna instrucción Transact-SQL.

Para obtener más información sobre los índices filtrados, vea Crear índices filtrados y la guía de diseño y arquitectura de índices de SQL Server.

Índices espaciales

Para obtener información sobre los índices espaciales, consulte CREATE SPATIAL INDEX e Información general sobre los índices espaciales.

índices XML

Para obtener información sobre los índices XML, vea CREATE XML INDEX e Índices XML (SQL Server).

Tamaño de clave de índice

El tamaño máximo de una clave de índice es de 900 bytes para un índice agrupado y de 1700 bytes para un índice no agrupado. (Antes de SQL Database y SQL Server 2016 [13.x], el límite siempre era de 900 bytes). Se pueden crear índices en columnas varchar que superen el límite de bytes si los datos existentes de las columnas no superan el límite en el momento en que se crea el índice; sin embargo, las operaciones posteriores de inserción o actualización en las columnas que hacen que el tamaño total sea mayor que el límite, produzca un error. La clave de índice de un índice agrupado no puede contener columnas varchar que tengan datos existentes en la ROW_OVERFLOW_DATA unidad de asignación. Si se crea un índice agrupado en una columna varchar y los datos existentes están en la IN_ROW_DATA unidad de asignación, se producirán errores en las operaciones posteriores de inserción o actualización de la columna que insertarían los datos fuera de fila.

Los índices no clúster pueden incluir columnas no clave (incluidas) en el nivel hoja del índice. El motor de base de datos no considera estas columnas al calcular el tamaño de la clave de índice. Para obtener más información, vea Crear índices con columnas incluidas y la guía de diseño y arquitectura de índices de SQL Server.

Nota:

Cuando se dividen las tablas, si las columnas de clave de la partición no están aún presentes en un índice clúster no único, el Motor de base de datos las agrega al índice. El tamaño combinado de las columnas indizadas (sin contar las columnas incluidas) más cualquier columna de partición agregada no puede exceder 1800 bytes en un índice clúster no único.

Columnas calculadas

Los índices se pueden crear en columnas calculadas. Además, las columnas calculadas pueden tener la propiedad PERSISTED. Esto significa que Motor de base de datos almacena los valores calculados en la tabla y los actualiza cuando se actualiza cualquier otra columna de la que depende la columna calculada. Motor de base de datos utiliza estos valores persistentes cuando crea un índice en la columna y cuando se hace referencia al índice en una consulta.

Para indexar una columna calculada, esta debe ser determinista y precisa. Sin embargo, el uso de la PERSISTED propiedad expande el tipo de columnas calculadas indexables para incluir:

• Las columnas calculadas basadas en Transact-SQL, funciones CLR y métodos de tipos definidos por el usuario CLR que el usuario ha marcado como deterministas.
• Las columnas calculadas basadas en expresiones que son deterministas, como se definen en Motor de base de datos, aunque imprecisas.

Las columnas calculadas persistentes requieren que se establezcan las siguientes SET opciones, como se muestra en la sección anterior Opciones SET necesarias para los índices filtrados.

La UNIQUE restricción o PRIMARY KEY puede contener una columna calculada siempre que cumpla todas las condiciones para la indexación. En concreto, la columna calculada debe ser determinista y precisa, o determinista y persistente. Para obtener más información sobre el determinismo, vea Funciones deterministas y no deterministas.

Las columnas calculadas derivadas de los tipos de datos image, ntext, text, varchar(max) , nvarchar(max) , varbinary(max) y xml se pueden indexar como una columna de clave o columna sin clave incluida, siempre que el tipo de datos de la columna calculada esté disponible como una columna índice de clave o sin clave. Por ejemplo, no puede crear un índice XML principal en una columna xml calculada. Si el tamaño de clave de índice supera los 900 bytes, se muestra un mensaje de advertencia.

La creación de un índice en una columna calculada podría provocar el error de una operación de inserción o actualización que funcionaba anteriormente. Este error puede producirse cuando la columna calculada produce un error aritmético.

Por ejemplo, en la tabla siguiente, aunque la expresión de la columna c calculada parece producir un error aritmético cuando se inserta la fila, la INSERT instrucción funciona.

CREATE TABLE t1 (a INT, b INT, c AS a/b);
INSERT INTO t1 VALUES (1, 0);

Sin embargo, si crea un índice en la columna ccalculada, se produce un error en la misma INSERT instrucción.

CREATE TABLE t1 (a INT, b INT, c AS a/b);
CREATE UNIQUE CLUSTERED INDEX Idx1 ON t1(c);
INSERT INTO t1 VALUES (1, 0);

Para obtener más información, consulte Índices en columnas calculadas.

Columnas incluidas en índices

Las columnas que no son de clave, denominadas columnas incluidas, se pueden agregar en el nivel hoja de un índice no clúster para mejorar el rendimiento de las consultas al cubrir la consulta. Es decir, todas las columnas a las que se hace referencia en la consulta se incluyen en el índice como columnas de clave o que no son de clave. Esto permite al optimizador de consultas obtener toda la información necesaria de un examen o búsqueda de índices no clúster; No se tiene acceso a los datos de la tabla o del índice agrupado. Para obtener más información, vea Crear índices con columnas incluidas y la guía de diseño y arquitectura de índices de SQL Server.

Especificación de opciones de índice

SQL Server 2005 (9.x) introdujo nuevas opciones de índice y también modificó la forma en que se especifican las opciones. En la sintaxis compatible con versiones anteriores, WITH option_name es equivalente a WITH (option_name = ON). Al establecer opciones de índice, se aplican las siguientes reglas:

• Solo se pueden especificar nuevas opciones de índice mediante WITH (<option_name> = <ON | OFF>).
• Las opciones no se pueden especificar mediante el uso de la sintaxis compatible con versiones anteriores y la sintaxis nueva en la misma instrucción. Por ejemplo, si se especifica WITH (DROP_EXISTING, ONLINE = ON), se produce un error en la instrucción.
• Cuando se crea un índice XML, las opciones se deben especificar mediante WITH (<option_name> = <ON | OFF>).

DROP_EXISTING, cláusula

Puede usar la cláusula DROP_EXISTING para volver a generar el índice, agregar o quitar columnas, modificar opciones, modificar el criterio de ordenación de las columnas o cambiar el grupo de archivos o el esquema de partición.

Si el índice aplica una PRIMARY KEY restricción o UNIQUE y la definición del índice no se modifica de ninguna manera, el índice se quita y se vuelve a crear conservando la restricción existente. Sin embargo, si se ha modificado la definición de índice, se genera un error en la instrucción. Para cambiar la definición de una PRIMARY KEY restricción o UNIQUE , quite la restricción y agregue una restricción con la nueva definición.

DROP_EXISTING mejora el rendimiento cuando se vuelve a crear un índice agrupado (con el mismo conjunto de claves o con uno distinto) en una tabla que también tiene índices no agrupados. DROP_EXISTING reemplaza la ejecución de una instrucción DROP INDEX en el antiguo índice agrupado seguida de la ejecución de una instrucción CREATE INDEX para el nuevo índice agrupado. Los índices no clúster se vuelven a generar una vez, siempre que la definición de índice haya cambiado. La cláusula DROP_EXISTING no vuelve a generar los índices no agrupados cuando la definición de índice posee los mismos nombres de índice, columnas de clave y partición, atributo de unicidad y criterio de ordenación que el índice original.

Independientemente de si se vuelven a generar o no los índices no clúster, éstos siempre permanecen en sus esquemas de partición o grupos de archivos originales, y utilizan las funciones de partición originales. Si un índice clúster se vuelve a generar en un esquema de partición o grupo de archivos diferente, los índices no clúster no se mueven para coincidir con la nueva ubicación del índice clúster. Por lo tanto, incluso si los índices no agrupados previamente se alinean con el índice agrupado, es posible que ya no se alineen con él. Para obtener más información sobre la alineación de índices con particiones, consulte Tablas e índices con particiones.

La DROP_EXISTING cláusula no ordena de nuevo los datos si se usan las mismas columnas de clave de índice en el mismo orden y con el mismo orden ascendente o descendente, a menos que la instrucción index especifique un índice no clúster y la ONLINE opción se establezca OFFen . Si el índice clúster está deshabilitado, la CREATE INDEX WITH DROP_EXISTING operación debe realizarse con establecido en ONLINEOFF. Si un índice no clúster está deshabilitado y no está asociado a un índice clúster deshabilitado, la CREATE INDEX WITH DROP_EXISTING operación se puede realizar con establecido OFF en ONLINE o ON.

Nota:

Cuando se quitan o se vuelven a generar índices con 128 o más extensiones, el Motor de base de datos aplaza las cancelaciones de asignación de página reales y los bloqueos asociados, hasta después de que se confirme la transacción. Para obtener más información, vea desasignación diferida.

ONLINE, opción

Las directrices siguientes se aplican para el desarrollo de operaciones de índice en línea:

• La tabla subyacente no se podrá alterar, truncar ni quitar mientras haya una operación de índice en línea en curso.
• La operación de índice requiere un espacio en disco temporal adicional.
• Las operaciones en línea se pueden realizar en índices con particiones e índices que contienen columnas calculadas persistentes, o columnas incluidas.
• La WAIT_AT_LOW_PRIORITY opción de argumento permite decidir cómo continúa la operación de índice cuando espera un Sch-M bloqueo. Para obtener más información, consulte WAIT_AT_LOW_PRIORITY

Para obtener más información, consulte Realizar operaciones de índice en línea.

Operaciones de índice reanudable

se aplica a: SQL Server 2019 (15.x) y versiones posteriores, Azure SQL Database e Instancia administrada de Azure SQL

Puede reanudar una operación de creación de índices en línea. Esto significa que la compilación del índice se puede detener y reiniciar posteriormente desde el punto donde se detuvo. Para ejecutar una compilación de índice como reanudable, especifique la RESUMABLE = ON opción .

Las siguientes directrices se aplican a las operaciones de índice reanudables:

• Para usar la opción RESUMABLE, también debe usar la opción ONLINE.
• La opción RESUMABLE no se conserva en los metadatos de un índice determinado y solo se aplica a la duración de la instrucción DDL actual. Por tanto, la cláusula RESUMABLE = ON debe especificarse explícitamente para habilitar la capacidad de reanudación.
• La opción MAX_DURATION se puede especificar en dos contextos:
  • MAX_DURATION para la RESUMABLE opción especifica el intervalo de tiempo de un índice que se está recompilando. Una vez transcurrido este tiempo, y si la recompilación del índice todavía se está ejecutando, se pausa. Decide cuándo se puede reanudar la recompilación de un índice en pausa. El tiempo en minutos para MAX_DURATION debe ser mayor que 0 minutos y menor o igual que una semana (7 * 24 * 60 = 10080 minutos). Una pausa larga en una operación de índice podría afectar notablemente al rendimiento de DML en una tabla específica, así como a la capacidad del disco de base de datos, ya que tanto el índice original como el índice recién creado requieren espacio en disco y las operaciones DML deben actualizarse. Si se omite MAX_DURATION opción, la operación de índice continúa hasta que se complete o hasta que se produzca un error.
  • MAX_DURATION para la opción WAIT_AT_LOW_PRIORITY especifica el tiempo de espera mediante bloqueos de prioridad baja si la operación de índice está bloqueada, antes de tomar medidas. Para obtener más información, consulte WAIT_AT_LOW_PRIORITY con operaciones de índice en línea.
• Para pausar la operación de índice inmediatamente, puede ejecutar el comando ALTER INDEX PAUSE o ejecutar el comando KILL <session_id>.
• Al volver a ejecutar la instrucción original CREATE INDEX con los mismos parámetros, se reanuda una operación de compilación de índice en pausa. También puede reanudar una operación de compilación de índice en pausa ejecutando la ALTER INDEX RESUME instrucción .
• El comando ABORT elimina la sesión que ejecuta una compilación de índice y cancela la operación de índice. No se puede reanudar una operación de índice que se ha anulado.

Una operación de índice reanudable se ejecuta hasta que se completa, pausa o produce un error. En caso de que la operación se detenga, se emite un error que indica que la operación se ha pausado y que la creación del índice no se completó. En caso de que se produzca un error en la operación, también se emite un error.

Para ver si una operación de índice se ejecuta como una operación reanudable y para comprobar su estado de ejecución actual, use la vista de catálogo de sys.index_resumable_operations.

Recursos

Los siguientes recursos son necesarios para las operaciones de índice reanudables:

• Espacio adicional necesario para mantener el índice que se está compilando, incluida la hora en que se pausa la compilación.
• Rendimiento de registro adicional durante la fase de ordenación. El uso de espacio del registro general para el índice reanudable es menor en comparación con la creación del índice en línea habitual y permite truncar el registro durante esta operación.
• Las instrucciones DDL que intentan modificar la tabla asociada al índice que se está creando mientras no se permite la operación de índice.
• La limpieza de registros fantasma se bloquea en el índice de la compilación durante la operación, tanto en pausa como cuando la operación está en ejecución.
• Si la tabla contiene columnas LOB, una compilación de índice agrupado reanudable requiere un bloqueo de modificación de esquema (Sch-M) al principio de la operación.

Limitaciones funcionales actuales

Las operaciones de creación de índices reanudables tienen las siguientes limitaciones:

• Después de pausar una operación de creación de índices en línea reanudable, no se puede cambiar el valor inicial de MAXDOP .
• La opción SORT_IN_TEMPDB = ON no se admite para las operaciones de índice reanudables.
• El comando DDL con RESUMABLE = ON no se puede ejecutar dentro de una transacción explícita.
• No se puede crear un índice reanudable que contenga:
  • Columnas calculadas o timestamp (rowversion) como columnas de clave.
  • Columna LOB como columna incluida.
• No se admiten operaciones de índice reanudables para:
  • El comando ALTER INDEX REBUILD ALL
  • El comando ALTER TABLE REBUILD
  • Índices de almacén de columnas
  • Índices filtrados
  • Índices deshabilitados

WAIT_AT_LOW_PRIORITY con operaciones de índice en línea

se aplica a: SQL Server 2022 (16.x) y versiones posteriores, Azure SQL Database e Instancia administrada de Azure SQL

Cuando no use la WAIT_AT_LOW_PRIORITY opción , todas las transacciones de bloqueo activas que contienen bloqueos en la tabla o el índice deben completarse para que la operación de creación de índices se inicie y complete. Cuando se inicia la operación de índice en línea y antes de que se complete, debe adquirir un compartido (S) o un bloqueo de modificación de esquema (Sch-M) en la tabla y contenerlo durante un breve tiempo. Aunque el bloqueo solo se mantiene durante un breve tiempo, podría afectar significativamente al rendimiento de la carga de trabajo, aumentar la latencia de las consultas o provocar tiempos de espera de ejecución.

Para evitar estos problemas, la opción WAIT_AT_LOW_PRIORITY permite administrar el comportamiento de S o Sch-M bloqueos necesarios para que se inicie y complete una operación de índice en línea, seleccionando entre tres opciones. En todos los casos, si durante el tiempo de espera especificado por MAX_DURATION = n [minutes] no hay ningún bloqueo que implique la operación de índice, la operación de índice continúa inmediatamente.

WAIT_AT_LOW_PRIORITY hace que la operación de índice en línea espere mediante bloqueos de prioridad baja, lo que permite que otras operaciones usen bloqueos de prioridad normales mientras tanto. La omisión de la opción WAIT_AT_LOW_PRIORITY es equivalente a WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE).

MAX_DURATION = tiempo [MINUTES]

El tiempo de espera (un valor entero especificado en minutos) que espera la operación de índice en línea mediante bloqueos de prioridad baja. Si la operación está bloqueada durante el MAX_DURATION tiempo, se ejecuta la acción especificada ABORT_AFTER_WAIT . MAX_DURATION time siempre está en minutos y se puede omitir la palabra MINUTES .

ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS ]

• NONE: continúe esperando el bloqueo con prioridad normal.
• SELF: salga de la operación de índice en línea que se está ejecutando actualmente, sin realizar ninguna acción. La opción SELF no se puede usar cuando MAX_DURATION es 0.
• BLOCKERS: elimine todas las transacciones de usuario que bloqueen la operación de índice en línea para que la operación pueda continuar. La opción BLOCKERS requiere que la entidad de seguridad que ejecute la instrucción CREATE INDEX o ALTER INDEX tenga el permiso ALTER ANY CONNECTION.

Puede usar los siguientes eventos extendidos para supervisar las operaciones de índice que esperan bloqueos con prioridad baja:

• lock_request_priority_state
• process_killed_by_abort_blockers
• ddl_with_wait_at_low_priority

Opciones de bloqueo de fila y página

Si ALLOW_ROW_LOCKS = ON y ALLOW_PAGE_LOCK = ON, se permiten los bloqueos de nivel de fila, página y tabla al obtener acceso al índice. Motor de base de datos elige el bloqueo apropiado y puede cambiar de escala el bloqueo: de un bloqueo de fila o página a un bloqueo de tabla.

Si ALLOW_ROW_LOCKS = OFF y ALLOW_PAGE_LOCK = OFF, solo se permite un bloqueo de nivel de tabla al obtener acceso al índice.

Advertencia

No se recomienda deshabilitar bloqueos de fila o página en un índice. Pueden producirse problemas relacionados con la simultaneidad y es posible que ciertas funcionalidades no estén disponibles. Por ejemplo, no se puede reorganizar un índice cuando ALLOW_PAGE_LOCKS está establecido en OFF.

Claves secuenciales

Se aplica a: SQL Server 2019 (15.x) y versiones posteriores, en Azure SQL Database y en Azure SQL Managed Instance.

La contención de inserción de la última página es un problema común de rendimiento que se produce cuando un gran número de subprocesos simultáneos intentan insertar filas en un índice con una clave secuencial. Un índice se considera secuencial cuando la columna de clave inicial contiene valores que siempre aumentan (o disminuyen), como una columna de identidad o una fecha que toma como valor predeterminado la fecha y hora actuales. Dado que las claves que se insertan son secuenciales, todas las filas nuevas se insertan al final de la estructura de índice, es decir, en la misma página. Esto conduce a la contención de la página en la memoria, que se puede observar como varios subprocesos que esperan adquirir un bloqueo temporal para la página en cuestión. El tipo de espera correspondiente es PAGELATCH_EX.

Si se habilita la opción de índice OPTIMIZE_FOR_SEQUENTIAL_KEY, se activa una optimización en el motor de base de datos que ayuda a mejorar el rendimiento de las inserciones de alta simultaneidad en el índice. Está concebida para los índices que tienen una clave secuencial y, por tanto, son propensos a la contención de inserción de la última página, pero también puede ayudar con índices que tienen zonas activas en otras áreas de la estructura del índice de árbol B.

Nota:

La documentación utiliza el término árbol B generalmente en referencia a los índices. En los índices del almacén de filas, el motor de la base de datos implementa un árbol B+. Esto no se aplica a los índices de almacén de columnas ni a los índices de tablas optimizadas para memoria. Para obtener más información, consulte la guía de diseño y arquitectura de índices de SQL Server y Azure SQL.

Compresión de datos

Para obtener más información sobre la compresión de datos, consulte Compresión de datos.

A continuación se muestran los puntos clave que se deben tener en cuenta en el contexto de las operaciones de compilación de índices cuando se usa la compresión de datos:

• La compresión puede permitir que se almacenen más filas en una página, pero no cambia el tamaño máximo de la fila.
• Las páginas no hoja de un índice no tienen compresión de página pero pueden tener compresión de fila.
• Cada índice no agrupado tiene una configuración de compresión individual y no hereda la configuración de compresión de la tabla subyacente.
• Cuando se crea un índice clúster en un montón, el índice clúster hereda el estado de compresión del montón, a menos que se especifique otro estado de compresión.

Para evaluar cómo el cambio del estado de compresión afecta al uso del espacio por parte de una tabla, un índice o una partición, use el procedimiento almacenado sp_estimate_data_compression_savings .

Compresión XML

Se aplica a SQL Server 2022 (16.x) y versiones posteriores, Azure SQL Database y Azure SQL Managed Instance.

Muchas de las consideraciones de compresión de datos se aplican a la compresión XML. Tenga presentes también las siguientes consideraciones:

• Si se especifica una lista de particiones, la compresión XML se puede habilitar en particiones individuales. Si no se especifica una lista de particiones, todas las particiones se establecen para usar la compresión XML. Cuando se crea una tabla o índice, la compresión de datos XML se deshabilita, a menos que se especifique lo contrario. Cuando se modifica una tabla, se conserva la compresión existente, a menos que se especifique lo contrario.
• Si se especifica una partición o una lista de particiones que están fuera del intervalo, se genera un error.
• Cuando se crea un índice agrupado en un montón, este hereda el estado de compresión XML del montón, a menos que se especifique otra opción de compresión.
• El cambio del valor de compresión de un montón requiere que todos los índices no agrupados de la tabla se vuelvan a generar, de modo que tengan punteros a las nuevas ubicaciones de fila en el montón.
• La compresión XML se puede habilitar o deshabilitar con o sin conexión. La habilitación de la compresión en un montón es de un solo subproceso para una operación en línea.
• Para determinar el estado de compresión XML de las particiones de una tabla con particiones, use la xml_compression columna de la sys.partitions vista de catálogo.

Estadísticas de índice

Cuando se crea un índice de almacén de filas, el motor de base de datos también crea estadísticas en las columnas clave del índice. El nombre del objeto de estadísticas de la vista de catálogo sys.stats coincide con el nombre del índice. En el caso de un índice sin particiones, las estadísticas se compilan mediante un examen completo de los datos. Para un índice con particiones, las estadísticas se compilan mediante el algoritmo de muestreo predeterminado.

Cuando se crea un índice de almacén de columnas, el motor de base de datos crea también un objeto de estadísticas en sys.stats . Este objeto de estadísticas no contiene datos estadísticos como el histograma y el vector de densidad. Se usa al crear un clon de base de datos mediante el scripting de la base de datos. En ese momento, los DBCC SHOW_STATISTICS comandos y UPDATE STATISTICS ... WITH STATS_STREAM se usan para obtener metadatos de almacén de columnas, como el tamaño del almacén de segmentos, diccionarios y delta, y agregarlos a las estadísticas del índice de almacén de columnas. Estos metadatos se obtienen dinámicamente en tiempo de compilación de consultas para una base de datos normal, pero el objeto de estadísticas proporciona para un clon de base de datos. El comando UPDATE STATISTICS no se admite para el objeto statistics en un índice de almacén de columnas en cualquier otro escenario.

Permisos

Requiere el ALTER permiso en la tabla o vista o pertenencia al db_ddladmin rol fijo de base de datos.

Limitaciones y restricciones

En Azure Synapse Analytics y Analytics Platform System (PDW), no puede crear lo siguiente:

• Un índice de almacén de filas agrupado o no agrupado en una tabla de almacén de datos cuando ya existe un índice de almacén de columnas. Este comportamiento es diferente de SMP SQL Server, que permite que los índices de almacén de filas y columnas coexistan en la misma tabla.
• No puede crear un índice en una vista.

Metadatos

Para ver información sobre los índices existentes, puede consultar la vista de catálogo sys.indexes.

Notas de la versión

• Azure SQL Database no admite grupos de archivos distintos de PRIMARY.
• Azure SQL Database y Azure SQL Managed Instance no admiten opciones de FILESTREAM.
• Los índices de almacén de columnas no están disponibles antes de SQL Server 2012 (11.x).
• Las operaciones de índice reanudables están disponibles a partir de SQL Server 2017 (14.x), en Azure SQL Database y en Azure SQL Managed Instance.

Ejemplos: Todas las versiones. Utiliza la base de datos AdventureWorks

A. Crear un índice no clúster de almacén de filas simple

El ejemplo siguiente crea un índice no agrupado en la columna VendorID de la tabla Purchasing.ProductVendor.

CREATE INDEX IX_VendorID ON ProductVendor (VendorID);
CREATE INDEX IX_VendorID ON dbo.ProductVendor (VendorID DESC, Name ASC, Address DESC);
CREATE INDEX IX_VendorID ON Purchasing..ProductVendor (VendorID);

B. Crear un índice compuesto de almacén de filas no agrupado

En el ejemplo siguiente se crea un índice compuesto no agrupado en las columnas SalesQuota y SalesYTD de la tabla Sales.SalesPerson.

CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson (SalesQuota, SalesYTD);

C. Crear un índice en una tabla de otra base de datos

En el ejemplo siguiente se crea un índice agrupado en la columna VendorID de la tabla ProductVendor en la base de datos Purchasing.

CREATE CLUSTERED INDEX IX_ProductVendor_VendorID ON Purchasing..ProductVendor (VendorID);

D. Agregar una columna a un índice

En el ejemplo siguiente se crea el índice IX_FF con dos columnas de la tabla dbo.FactFinance. La instrucción siguiente vuelve a generar el índice con una columna más y mantiene el nombre existente.

CREATE INDEX IX_FF ON dbo.FactFinance (FinanceKey ASC, DateKey ASC);

-- Rebuild and add the OrganizationKey
CREATE INDEX IX_FF ON dbo.FactFinance (FinanceKey, DateKey, OrganizationKey DESC)
  WITH (DROP_EXISTING = ON);

Ejemplos: SQL Server, Azure SQL Database

E. Crear un índice no agrupado único

En el ejemplo siguiente se crea un índice no clúster único en la columna Name de la tabla Production.UnitMeasure en la base de datos AdventureWorks2022. El índice exigirá unicidad en los datos insertados en la columna Name.

CREATE UNIQUE INDEX AK_UnitMeasure_Name
  ON Production.UnitMeasure(Name);

La consulta siguiente prueba la restricción de unicidad intentando insertar una fila con el mismo valor que el de una fila existente.

-- Verify the existing value.
SELECT Name FROM Production.UnitMeasure WHERE Name = N'Ounces';
GO

INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name, ModifiedDate)
  VALUES ('OC', 'Ounces', GETDATE());

El mensaje de error resultante es:

Server: Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'UnitMeasure' with unique index 'AK_UnitMeasure_Name'. The statement has been terminated.

F. Usar la opción IGNORE_DUP_KEY

El ejemplo siguiente muestra el efecto de la opción IGNORE_DUP_KEY al insertar varias filas en una tabla temporal primero con la opción establecida en ON y luego con la opción establecida en OFF. Se inserta una única fila en la tabla #Test que intencionadamente proporcionará un valor duplicado cuando se ejecuta la segunda instrucción INSERT de varias filas. Un recuento de las filas de la tabla devuelve el número de filas insertadas.

CREATE TABLE #Test (C1 NVARCHAR(10), C2 NVARCHAR(50), C3 DATETIME);
GO

CREATE UNIQUE INDEX AK_Index ON #Test (C2)
  WITH (IGNORE_DUP_KEY = ON);
GO

INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO

SELECT COUNT(*) AS [Number of rows] FROM #Test;
GO

DROP TABLE #Test;
GO

A continuación se muestran los resultados de la segunda instrucción INSERT.

Server: Msg 3604, Level 16, State 1, Line 5 Duplicate key was ignored.

Number of rows
--------------
38

Observe que las filas insertadas desde la tabla Production.UnitMeasure que no infringieron la restricción de unicidad se insertaron correctamente. Se emitió una advertencia y se omitió la fila duplicada, pero no se revirtió la transacción completa.

Las mismas instrucciones se ejecutan nuevamente, pero con IGNORE_DUP_KEY establecido en OFF.

CREATE TABLE #Test (C1 NVARCHAR(10), C2 NVARCHAR(50), C3 DATETIME);
GO

CREATE UNIQUE INDEX AK_Index ON #Test (C2)
  WITH (IGNORE_DUP_KEY = OFF);
GO

INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO

SELECT COUNT(*) AS [Number of rows] FROM #Test;
GO

DROP TABLE #Test;
GO

A continuación se muestran los resultados de la segunda instrucción INSERT.

Server: Msg 2601, Level 14, State 1, Line 5
Cannot insert duplicate key row in object '#Test' with unique index
'AK_Index'. The statement has been terminated.

Number of rows
--------------
1

Observe que ninguna de las filas de la tabla Production.UnitMeasure se insertó en la tabla aunque solo una fila de la tabla infringió la restricción de índice UNIQUE.

G. Usar DROP_EXISTING para quitar y volver a crear un índice

En el ejemplo siguiente se quita y se vuelve a crear un índice existente en la columna ProductID de la tabla Production.WorkOrder en la base de datos AdventureWorks2022 utilizando la opción DROP_EXISTING. También se establecen las opciones FILLFACTOR y PAD_INDEX .

CREATE NONCLUSTERED INDEX IX_WorkOrder_ProductID
  ON Production.WorkOrder(ProductID)
    WITH (FILLFACTOR = 80,
      PAD_INDEX = ON,
      DROP_EXISTING = ON);
GO

H. Crear un índice en una vista

Este ejemplo siguiente crea una vista y un índice en esa vista. Se incluyen dos consultas que utilizan la vista indizada.

-- Set the options to support indexed views
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
  QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO

-- Create view with schemabinding
IF OBJECT_ID ('Sales.vOrders', 'view') IS NOT NULL
  DROP VIEW Sales.vOrders;
GO

CREATE VIEW Sales.vOrders
  WITH SCHEMABINDING
AS
  SELECT SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Revenue,
    OrderDate, ProductID, COUNT_BIG(*) AS COUNT
  FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o
  WHERE od.SalesOrderID = o.SalesOrderID
  GROUP BY OrderDate, ProductID;
GO

-- Create an index on the view
CREATE UNIQUE CLUSTERED INDEX IDX_V1
  ON Sales.vOrders (OrderDate, ProductID);
GO

-- This query can use the indexed view even though the view is
-- not specified in the FROM clause.
SELECT SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Rev,
  OrderDate, ProductID
FROM Sales.SalesOrderDetail AS od
  JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID = o.SalesOrderID
    AND ProductID BETWEEN 700 AND 800
    AND OrderDate >= CONVERT(DATETIME, '05/01/2002', 101)
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC;
GO

-- This query can use the above indexed view
SELECT OrderDate, SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
  JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID = o.SalesOrderID
    AND DATEPART(mm, OrderDate) = 3
  AND DATEPART(yy, OrderDate) = 2002
GROUP BY OrderDate
ORDER BY OrderDate ASC;
GO

I. Crear un índice con columnas incluidas (sin clave)

El ejemplo siguiente crea un índice no clúster con una columna de clave (PostalCode) y cuatro columnas que no son de clave (AddressLine1, AddressLine2, City, StateProvinceID). A continuación se presenta una consulta cubierta por el índice. Para mostrar el índice seleccionado con el optimizador de consultas, en el menú Consulta de SQL Server Management Studio, seleccione Mostrar plan de ejecución estimado antes de ejecutar la consulta.

CREATE NONCLUSTERED INDEX IX_Address_PostalCode
  ON Person.Address (PostalCode)
  INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);
GO

SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'98000' and N'99999';
GO

J. Crear un índice con particiones

En el ejemplo siguiente se crea un índice no clúster con particiones en TransactionsPS1, un esquema de partición existente en la base de datos AdventureWorks2022. En este ejemplo se supone que se ha instalado el ejemplo de índice con particiones.

CREATE NONCLUSTERED INDEX IX_TransactionHistory_ReferenceOrderID
  ON Production.TransactionHistory (ReferenceOrderID)
  ON TransactionsPS1 (TransactionDate);
GO

K. Crear un índice filtrado

En el ejemplo siguiente se crea un índice filtrado en la tabla Production.BillOfMaterials de la base de datos AdventureWorks2022. El predicado de filtro puede incluir columnas que no son columnas de clave en el índice filtrado. El predicado de este ejemplo selecciona solo las filas en que EndDate no es NULL.

CREATE NONCLUSTERED INDEX "FIBillOfMaterialsWithEndDate"
  ON Production.BillOfMaterials (ComponentID, StartDate)
  WHERE EndDate IS NOT NULL;

L. Crear un índice comprimido

En el ejemplo siguiente se crea un índice en una tabla sin particiones utilizando la compresión de fila.

CREATE NONCLUSTERED INDEX IX_INDEX_1
  ON T1 (C2)
  WITH (DATA_COMPRESSION = ROW);
GO

En el ejemplo siguiente se crea un índice en una tabla con particiones utilizando la compresión de fila en todas las particiones del índice.

CREATE CLUSTERED INDEX IX_PartTab2Col1
  ON PartitionTable1 (Col1)
  WITH (DATA_COMPRESSION = ROW);
GO

En el ejemplo siguiente se crea un índice en una tabla con particiones utilizando la compresión de página en la partición 1 del índice y la compresión de fila en las particiones 2 a 4 del índice.

CREATE CLUSTERED INDEX IX_PartTab2Col1
  ON PartitionTable1 (Col1)
  WITH (
    DATA_COMPRESSION = PAGE ON PARTITIONS(1),
    DATA_COMPRESSION = ROW ON PARTITIONS (2 TO 4)
  );
GO

M. Crear un índice con compresión XML

Se aplica a SQL Server 2022 (16.x) y versiones posteriores, Azure SQL Database y Azure SQL Managed Instance.

En el siguiente ejemplo se crea un índice en una tabla sin particiones utilizando la compresión XML de fila. Al menos una columna del índice debe tener el tipo de datos xml.

CREATE NONCLUSTERED INDEX IX_INDEX_1
  ON T1 (C2)
  WITH (XML_COMPRESSION = ON);
GO

En el siguiente ejemplo se crea un índice en una tabla con particiones usando la compresión XML en todas las particiones del índice.

CREATE CLUSTERED INDEX IX_PartTab2Col1
  ON PartitionTable1 (Col1)
  WITH (XML_COMPRESSION = ON);
GO

Hora Crear, reanudar, pausar y anular operaciones de índices reanudables

se aplica a: SQL Server 2019 (15.x) y versiones posteriores, Azure SQL Database e Instancia administrada de Azure SQL

-- Execute a resumable online index create statement with MAXDOP=1
CREATE INDEX test_idx1 ON test_table (col1) WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON);

-- Executing the same command again (see above) after an index operation was paused, resumes automatically the index create operation.

-- Execute a resumable online index creates operation with MAX_DURATION set to 240 minutes. After the time expires, the resumable index create operation is paused.
CREATE INDEX test_idx2 ON test_table (col2) WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240);

-- Pause a running resumable online index creation
ALTER INDEX test_idx1 ON test_table PAUSE;
ALTER INDEX test_idx2 ON test_table PAUSE;

-- Resume a paused online index creation
ALTER INDEX test_idx1 ON test_table RESUME;
ALTER INDEX test_idx2 ON test_table RESUME;

-- Abort resumable index create operation which is running or paused
ALTER INDEX test_idx1 ON test_table ABORT;
ALTER INDEX test_idx2 ON test_table ABORT;

O. CREATE INDEX con diferentes opciones de bloqueo de prioridad baja

En los ejemplos siguientes se usa la opción WAIT_AT_LOW_PRIORITY para especificar estrategias diferentes para tratar con un bloqueo.

--Kill this session after waiting 5 minutes
CREATE CLUSTERED INDEX idx_1 ON dbo.T2 (a) WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = SELF)));
GO

--Kill blocker sessions
CREATE CLUSTERED INDEX idx_1 ON dbo.T2 (a) WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = BLOCKERS)));
GO

En el ejemplo siguiente se usa la opción RESUMABLE y se especifican dos valores MAX_DURATION. El primero se aplica a la opción ABORT_AFTER_WAIT y el segundo, a la opción RESUMABLE.

--With resumable option; default locking behavior 
CREATE CLUSTERED INDEX idx_1 ON dbo.T2 (a) WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = NONE)), RESUMABLE = ON, MAX_DURATION = 240 MINUTES);

Ejemplos: Azure Synapse Analytics y Sistema de la plataforma de análisis (PDW)

P. Sintaxis básica

Crear, reanudar, pausar y anular operaciones de índices reanudables

se aplica a: SQL Server 2019 (15.x) y versiones posteriores, Azure SQL Database e Instancia administrada de Azure SQL

-- Execute a resumable online index create statement with MAXDOP=1
CREATE INDEX test_idx ON test_table WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON);

-- Executing the same command again (see above) after an index operation was paused, resumes automatically the index create operation.

-- Execute a resumable online index creates operation with MAX_DURATION set to 240 minutes. After the time expires, the resumable index create operation is paused.
CREATE INDEX test_idx ON test_table WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240);

-- Pause a running resumable online index creation
ALTER INDEX test_idx ON test_table PAUSE;

-- Resume a paused online index creation
ALTER INDEX test_idx ON test_table RESUME;

-- Abort resumable index create operation which is running or paused
ALTER INDEX test_idx ON test_table ABORT;

Q. Creación de un índice no agrupado en una tabla en la base de datos actual

El ejemplo siguiente crea un índice no clúster en la columna VendorID de la tabla ProductVendor.

CREATE INDEX IX_ProductVendor_VendorID
  ON ProductVendor (VendorID);

R. Crear un índice agrupado en una tabla de otra base de datos

En el ejemplo siguiente se crea un índice no clúster en la columna VendorID de la tabla ProductVendoren la base de datos Purchasing.

CREATE CLUSTERED INDEX IX_ProductVendor_VendorID
  ON Purchasing..ProductVendor (VendorID);

S. Creación de un índice agrupado ordenado en una tabla

En el ejemplo siguiente se crea un índice agrupado ordenado en las columnas c1 y c2 de la tabla T1 de la base de datos MyDB.

CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON MyDB.dbo.T1 
ORDER (c1, c2);

T. Conversión de un CCI en un índice agrupado ordenado en una tabla

En el ejemplo siguiente se convierte el índice de almacén de columnas agrupado existente en un índice de almacén de columnas agrupado ordenado denominado MyOrderedCCI en las columnas c1 y c2 de la tabla T2 en la base de datos MyDB.

CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON MyDB.dbo.T2
ORDER (c1, c2)
WITH (DROP_EXISTING = ON);

Contenido relacionado

• Guía de diseño y de arquitectura de índices de SQL Server
• Realizar operaciones de índice en línea
• Índices y ALTER TABLE
• ALTER INDEX
• CREATE PARTITION FUNCTION
• CREATE PARTITION SCHEME
• CREATE SPATIAL INDEX
• CREATE STATISTICS
• CREATE TABLE
• CREATE XML INDEX
• Tipo de datos
• DBCC SHOW_STATISTICS
• DROP INDEX
• Índices XML (SQL Server)
• sys.indexes
• sys.index_columns
• sys.xml_indexes
• EVENTDATA