index_option (Transact-SQL)

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.

Se aplica a: SQL Server (desde SQL Server 2008 hasta la versión actual), Windows Azure SQL Database (desde la versión inicial hasta la versión actual).

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 }
  | SORT_IN_TEMPDB = { ON | OFF } 
  | ONLINE = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE |ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
      [ ON PARTITIONS ( { <partition_number_expression> | <range> } 
      [ , ...n ] ) ]
  | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
}

<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 } ) 
}

Argumentos

• PAD_INDEX = { ON | OFF }

  Se aplica a: SQL Server 2008 a SQL Server 2014.

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

  • ON
    El porcentaje de espacio disponible especificado por FILLFACTOR se aplica a 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 a SQL Server 2014.

  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 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.

  • ON
    Se producirá un mensaje de advertencia cuando se inserten valores de clave duplicados en un índice único. Solo las filas que infrinjan la restricción de unicidad darán error.

  • OFF
    Se producirá un mensaje de error cuando se inserten valores de clave duplicados en un índice único. Toda la operación INSERT se revertirá.

  IGNORE_DUP_KEY no se puede establecer en ON para los índices creados en una vista, los índices que no sean únicos, los índices XML, los índices espaciales y los í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 }
  Especifica si se vuelven a calcular las estadísticas. El valor predeterminado es OFF.

  • ON
    Las estadísticas desusadas no se vuelven a calcular automáticamente.

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

• ALLOW_ROW_LOCKS = { ON | OFF }

  Se aplica a: SQL Server 2008 a SQL Server 2014.

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

  • ON
    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.

  • OFF
    No se usan los bloqueos de fila.

• ALLOW_PAGE_LOCKS = { ON | OFF }

  Se aplica a: SQL Server 2008 a SQL Server 2014.

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

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

  • OFF
    No se utilizan bloqueos de página.

• SORT_IN_TEMPDB = { ON | OFF }

  Se aplica a: SQL Server 2008 a SQL Server 2014.

  Especifica si los resultados de orden se almacenan en tempdb. El valor predeterminado es OFF.

  • ON
    Los resultados de orden intermedio utilizados para generar el índice se almacenan en tempdb. Esto puede reducir el tiempo necesario para crear un índice si tempdb y la base de datos de usuarios están en conjuntos de discos distintos. Sin embargo, esto aumenta la cantidad de espacio en disco utilizado durante la creación del índice.

  • OFF
    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 a SQL Server 2014.

  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 operación ONLINE.

  Nota

  No es posible crear índices clúster únicos en línea.Entre estos se incluyen los índices creados a causa de una restricción UNIQUE o KEY PRIMARY.

  • ON
    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 preventivo en la tabla de origen. Esto habilita las consultas o actualizaciones en la tabla subyacente y en los índices. Al inicio de la operación, se mantiene un bloqueo compartido (S) en el objeto de origen durante un período de tiempo muy corto. Al final de la operación, se adquiere un bloqueo S (compartido) sobre el origen durante un corto período, si se está creando un índice no clúster; o bien, se adquiere un bloqueo SCH-M (modificación del esquema) cuando se crea o se quita un índice clúster en línea, y cuando se vuelve a crear un índice clúster o no clúster. 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, bloqueo Sch-M bloquea las demás transacciones que esperen a este bloqueo al tener acceso a la misma tabla. ONLINE no se puede establecer en ON cuando se crea un índice en una tabla temporal local.

    Nota

    La regeneración de índices 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.

  • OFF
    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 que se realicen actualizaciones en la tabla subyacente, pero permite la realización de operaciones de lectura, como instrucciones SELECT.

  Para obtener 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 características compatibles con las ediciones de SQL Server, vea Características compatibles con las ediciones de SQL Server 2014.

• MAXDOP **=**max_degree_of_parallelism

  Se aplica a: SQL Server 2008 a SQL Server 2014.

  Reemplaza la opción de configuración max degree of parallelism mientras dure 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. Utilice MAXDOP para limitar el número de procesadores utilizados en la ejecución de un plan paralelo. El máximo es 64 procesadores.

  max_degree_of_parallelism puede ser:

  • 1
    Suprime la generación de planes paralelos.

  • >1
    Restringe el número máximo de procesadores utilizados en una operación de índice paralelo para el número especificado.

  • 0 (valor predeterminado)
    Usa el número real de procesadores o menos, según la carga de trabajo actual del sistema.

  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 características compatibles con las ediciones de SQL Server, vea Características compatibles con las ediciones de SQL Server 2014.

• DATA_COMPRESSION

  Se aplica a: SQL Server 2008 a SQL Server 2014.

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

  • NONE
    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 a SQL Server 2014.

    Solo se aplica a tablas de almacén de columnas. COLUMNSTORE especifica que se descomprima una partición que se comprimió con la opción COLUMNSTORE_ARCHIVE. Cuando se restauran los datos, seguirán estando comprimidos 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 a SQL Server 2014.

    Solo se aplica a las tablas de almacén de columnas almacenadas con un índice clúster de almacén de columnas. COLUMNSTORE_ARCHIVE comprimirá aún más la partición especificada a un tamaño mínimo. 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 obtener más información acerca de la compresión, vea Compresión de datos.

• ON PARTITIONS ( { <expresión_de_número_de_particiones> | <intervalo> } [ ,...n ] )

  Se aplica a: SQL Server 2008 a SQL Server 2014.

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

  <partition_number_expression> se puede especificar de las maneras siguientes:

  • Proporcionando el número de una partición, por ejemplo: ON PARTITIONS (2).

  • Proporcionando los números de partición de varias particiones separados por comas, por ejemplo: ON PARTITIONS (1, 5).

  • Proporcionar intervalos y particiones individuales: ON PARTITIONS (2, 4, 6 TO 8).

  <intervalo> se puede especificar como números de partición separados por la palabra 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:

  --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)
  )
  

• <opción_de_regeneración_de_partición_única>
  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

• low_priority_lock_wait

  Se aplica a: SQL Server 2014 a SQL Server 2014.

  SWITCH o una regeneración de índice en línea se completa en cuanto no hay operaciones de bloqueo para esta tabla. WAIT_AT_LOW_PRIORITY indica que si SWITCH o la operación de regeneración de índice en línea no se pueden completar inmediatamente, esperará. La operación contendrá los bloqueos de prioridad baja y permitirá que continúen otras operaciones que mantengan 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 (valor entero especificado en minutos) que SWITH o el bloqueo de regeneración de índice en línea que deben adquirirse esperarán al ejecutar el comando DDL. SWITCH o la operación de regeneración de índices en línea intenta completarse inmediatamente. Si la operación se bloquea durante el tiempo de MAX_DURATION, se ejecutará una de las acciones ABORT_AFTER_WAIT. El tiempo de MAX_DURATION siempre en minutos y la palabra MINUTES puede omitirse.

• ABOUT_AFTER_WAIT = [NONE | SELF | BLOCKERS } ]

  • NONE
    Continua la operación de regeneración del índice en línea o SWITCH sin cambiar la prioridad de bloqueos (con prioridad normal).

  • SELF
    Sale de SWITCH o de la operación DDL de regeneración del índice en línea que se está ejecutando actualmente sin realizar ninguna acción.

  • BLOCKERS
    Elimina todas las transacciones de usuario que bloqueen actualmente a SWITCH o la operación DDL de regeneración de índice en línea de forma que la operación pueda continuar.

    Requiere el permiso ALTER ANY CONNECTION.

Comentarios

Para obtener una descripción completa de las opciones de índice, vea CREATE INDEX (Transact-SQL).

Vea también

Referencia

ALTER TABLE (Transact-SQL)

column_constraint (Transact-SQL)

computed_column_definition (Transact-SQL)

table_constraint (Transact-SQL)