Share via

ALTER TABLE index_option (Transact-SQL)

  • Artículo

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Especifica un conjunto de opciones que se pueden aplicar a un índice que forma parte de una definición de restricción creada con ALTER TABLE.

Para obtener una descripción completa de las opciones de índice, consulte CREATE INDEX.

Convenciones de sintaxis de Transact-SQL

Sintaxis

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

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

<single_partition_rebuild__option> ::=
{
    SORT_IN_TEMPDB = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE } }
  | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
}

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

Nota:

Para ver la sintaxis de Transact-SQL para SQL Server 2014 (12.x) y versiones anteriores, consulte Versiones anteriores de la documentación.

Argumentos

PAD_INDEX = { ON | OFF }

Se aplica a: SQL Server 2008 (10.0.x) y versiones posteriores

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

  • ACTIVAR

    El porcentaje de espacio disponible que se especifica mediante FILLFACTOR se aplica a las páginas de nivel intermedio del índice.

  • No se especifica OFF ni fillfactor

    Las páginas de nivel intermedio se llenan casi al máximo de su capacidad, dejando espacio suficiente para al menos una fila del tamaño máximo que admite el índice, en función del conjunto de claves de las páginas intermedias.

FILLFACTOR = fillfactor

Se aplica a: SQL Server 2008 (10.0.x) y versiones posteriores

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 modificación de los índices. El valor especificado debe ser un entero de 1 a 100. El valor predeterminado es 0.

Nota

Los valores de factor de relleno 0 y 100 son idénticos en todos los sentidos.

IGNORE_DUP_KEY = { ON | OFF }

Especifica el tipo de respuesta 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 dan error las filas que infringen la restricción de unicidad.

  • Apagado

    Se produce un mensaje de error cuando se insertan valores de clave duplicados en un índice único. Toda INSERT la operació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 IGNORE_DUP_KEY, utilice sys.indexes.

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

STATISTICS_NORECOMPUTE = { ON | OFF }

Deshabilite o habilite la opción de actualización automática de estadísticas, AUTO_STATISTICS_UPDATE, para las estadísticas relacionadas con los índices especificados. El valor predeterminado es OFF.

  • ACTIVAR

    Las actualizaciones automáticas de estadísticas se deshabilitan después de volver a generar el índice.

  • Apagado

    Las actualizaciones automáticas de estadísticas se habilitan después de volver a generar el índice.

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

Advertencia

Si deshabilita la actualización automática de estadísticas, podría impedir que el optimizador de consultas seleccione planes de ejecución óptimos para las consultas que implican la tabla. Debe usar esta opción con moderación y solo por un administrador de bases de datos calificado.

Esta configuración no impide una actualización automática con el examen completo de las estadísticas relacionadas con el índice, durante la operación de recompilación.

ALLOW_ROW_LOCKS = { ON | OFF }

Se aplica a: SQL Server 2008 (10.0.x) y versiones posteriores

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 bloqueos de fila.

ALLOW_PAGE_LOCKS = { ON | OFF }

Se aplica a: SQL Server 2008 (10.0.x) y versiones posteriores

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 usan bloqueos de página.

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }

Se aplica a: SQL Server 2019 (15.x) y versiones posteriores.

Especifica si se deben optimizar la contención de inserción de la última página. El valor predeterminado es OFF. Para obtener más información, consulte la sección Claves secuenciales del CREATE INDEX artículo.

SORT_IN_TEMPDB = { ON | OFF }

Se aplica a: SQL Server 2008 (10.0.x) y versiones posteriores

Especifica si se van a almacenar los resultados de ordenación en tempdb. El valor predeterminado es 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 si tempdb está en un conjunto diferente de discos que la base de datos de usuario. 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.

ONLINE = { ON | OFF }

Se aplica a: SQL Server 2008 (10.0.x) y versiones posteriores

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. REBUILD se puede realizar como una ONLINE operación.

Nota:

No se pueden crear índices no clúster únicos en línea. Esto incluye índices creados debido a una UNIQUE restricción o PRIMARY KEY .

  • ACTIVAR

    Los bloqueos de tabla de larga duración no se mantienen durante la operación de índice. Durante la fase principal de la operación de índice, solo se mantiene un bloqueo preventivo 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 S (compartido) en el origen si se crea un índice no clúster; o se adquiere un bloqueo Sch-M (Modificación del esquema) cuando se crea o quita un índice agrupado en línea y cuando se vuelve a generar un índice agrupado o no agrupado. Aunque los bloqueos de índice en línea son bloqueos de metadatos cortos, especialmente el bloqueo Sch-M debe esperar a que todas las transacciones de bloqueo se completen en esta tabla. Durante el tiempo de espera, el bloqueo Sch-M bloquea todas las demás transacciones que esperan detrás de este bloqueo al acceder a la misma tabla. ONLINE no se puede establecer ON en cuando se crea un índice en una tabla temporal local.

    Nota:

    La regeneración de índice en línea puede establecer las opciones low_priority_lock_wait que se describen más adelante en esta sección. low_priority_lock_wait administra la prioridad de los bloqueos S y Sch-M durante la regeneración de índices en línea.

  • Apagado

    Los bloqueos de tabla se aplican durante la operación de índice. Esto evita que todos los usuarios tengan acceso a la tabla subyacente durante la operación. Una operación de índice sin conexión para crear, volver a crear o quitar un índice clúster, o para volver a crear o quitar un índice no clúster, adquiere un bloqueo de modificación del esquema (Sch-M) de 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 un bloqueo compartido (S) en la tabla. Esto evita las actualizaciones de la tabla subyacente, pero permite operaciones de lectura, como SELECT instrucciones .

Para más información, vea Cómo funcionan las operaciones de índice en línea.

Nota:

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.

RESUMABLE = { ON | OFF}

Se aplica a: SQL Server 2022 (16.x) y versiones posteriores.

Especifica si una operación ALTER TABLE ADD CONSTRAINT se puede reanudar. La operación para agregar una restricción de tabla se puede reanudar cuando tiene el valor ON. La operación para agregar una restricción de tabla no se puede reanudar cuando tiene el valor OFF. El valor predeterminado es OFF. Cuando la opción RESUMABLE está establecida en ON, es necesaria la opción ONLINE = ON.

MAX_DURATION cuando se usa con RESUMABLE = ON (requiere ONLINE = ON) indica el tiempo (un valor entero especificado en minutos) que se ejecuta una operación de restricción de adición en línea reanudable antes de pausarse. Si no se especifica, la operación continúa hasta acabar. MAXDOP también se admite con RESUMABLE = ON .

Para obtener más información sobre cómo habilitar y usar operaciones ALTER TABLE ADD CONSTRAINT reanudables, consulte Adición reanudable de restricciones de tabla.

MAXDOP = max_degree_of_parallelism

Se aplica a: SQL Server 2008 (10.0.x) y versiones posteriores

Reemplaza la opción de configuración de max_degree_of_parallelism durante la operación de índice. Para obtener más información, consulte Configuración del grado máximo de paralelismo (opción de configuración del servidor). Use MAXDOP para limitar el número de procesadores usados en una ejecución de plan paralelo. El máximo es 64 procesadores.

max_degree_of_parallelism puede tener estos valores:

  • 1: suprime la generación de planes paralelos.
  • >1: restringe el número máximo de procesadores usados en una operación de índice paralela al número especificado.
  • 0 (valor predeterminado): usa el número real de procesadores o menos en función de la carga de trabajo del sistema actual.

Para obtener más información, vea Configurar 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

Se aplica a: SQL Server 2008 (10.0.x) y versiones posteriores

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

  • Ninguno

    No se comprimen la tabla ni las particiones especificadas. Solo se aplica a tablas de almacén de filas; no se aplica a tablas de almacén de columnas.

  • ROW

    La tabla o las particiones especificadas se comprimen utilizando la compresión de fila. Solo se aplica a tablas de almacén de filas; no se aplica a tablas de almacén de columnas.

  • PAGE

    La tabla o las particiones especificadas se comprimen utilizando la compresión de página. Solo se aplica a tablas de almacén de filas; no se aplica a tablas de almacén de columnas.

  • COLUMNSTORE

    Se aplica a: SQL Server 2014 (12.x) y versiones posteriores.

    Solo se aplica a tablas de almacén de columnas. COLUMNSTORE especifica para descomprimir una partición que se comprimió con la COLUMNSTORE_ARCHIVE opción . Cuando se restauran los datos, el COLUMNSTORE índice continúa comprimiéndose con la compresión de almacén de columnas que se usa para todas las tablas de almacén de columnas.

  • COLUMNSTORE_ARCHIVE

    Se aplica a: SQL Server 2014 (12.x) y versiones posteriores.

    Solo se aplica a las tablas de almacén de columnas almacenadas con un índice clúster de almacén de columnas. 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 menos almacenamiento y en las que 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 y Azure SQL Managed Instance.

Especifica la opción de compresión XML de las columnas de tipo de datos xml de la tabla. Las opciones son las siguientes:

  • ACTIVAR

    Las columnas que usan el tipo de datos xml se comprimen.

  • Apagado

    Las columnas que usan el tipo de datos xml no se comprimen.

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

Se aplica a: SQL Server 2008 (10.0.x) y versiones posteriores

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

<partition_number_expression> se puede especificar de estas maneras:

  • Proporcione 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 TO, como 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:

--For rowstore tables
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)
)

--For columnstore tables
REBUILD WITH
(
  DATA_COMPRESSION = COLUMNSTORE ON PARTITIONS (1, 3, 5),
  DATA_COMPRESSION = COLUMNSTORE_ARCHIVE ON PARTITIONS (2, 4, 6 TO 8)
)

<single_partition_rebuild__option>

En la mayoría de los casos, la regeneración de un índice hace que se vuelvan a generar todas las particiones de un índice con particiones. Cuando las opciones siguientes se aplican a una partición única, no vuelven a generar todas las particiones.

  • SORT_IN_TEMPDB
  • MAXDOP
  • DATA_COMPRESSION
  • XML_COMPRESSION

low_priority_lock_wait

Se aplica a: SQL Server 2014 (12.x) y versiones posteriores.

Una SWITCH recompilación de índices en línea o se completa en cuanto no hay operaciones de bloqueo para esta tabla. WAIT_AT_LOW_PRIORITY indica que si la SWITCH operación de recompilación del índice o en línea no se puede completar inmediatamente, espera. La operación mantiene bloqueos de prioridad baja y permite que continúen otras operaciones que mantienen bloqueos en conflicto con la instrucción DDL. 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 = time [ MINUTES ]

El tiempo de espera (un valor entero especificado en minutos) que el bloqueo de recompilación de SWITCH índice en línea o que se debe adquirir, espera al ejecutar el comando DDL. La operación de recompilación de SWITCH índices en línea o intenta completarse inmediatamente. Si la operación se bloquea durante el MAX_DURATION tiempo, se ejecuta una de las ABORT_AFTER_WAIT acciones. MAX_DURATION time siempre está en minutos y se puede omitir la palabra MINUTES .

ABORT_AFTER_WAIT = { NONE | SELF | BLOQUEADORES }

  • NONE

    Continúa la operación de recompilación del SWITCH índice o en línea sin cambiar la prioridad de bloqueo (mediante la prioridad regular).

  • SELF

    Sale de la operación DDL de recompilación de SWITCH índices o en línea que se está ejecutando actualmente sin realizar ninguna acción.

  • BLOCKERS

    Elimina todas las transacciones de usuario que bloquean actualmente la operación DDL de recompilación del SWITCH índice en línea o en línea para que la operación pueda continuar.

    BLOCKERS requiere el ALTER ANY CONNECTION permiso .

Contenido relacionado