ALTER INDEX (Transact-SQL)

  • Artículo

Modifica un índice existente de una tabla o una vista (relacional o XML) mediante su deshabilitación, regeneración o reorganización, o mediante el establecimiento de sus opciones.

Icono de vínculo a temas Convenciones de sintaxis de Transact-SQL

Sintaxis

ALTER INDEX { index_name | ALL }
    ON <object>
    { REBUILD 
        [ [PARTITION = ALL]
          [ WITH ( <rebuild_index_option> [ ,...n ] ) ] 
          | [ PARTITION = partition_number 
                [ WITH ( <single_partition_rebuild_index_option>
                        [ ,...n ] )
                ] 
            ]
        ]
    | DISABLE
    | REORGANIZE 
        [ PARTITION = partition_number ]
        [ WITH ( LOB_COMPACTION = { ON | OFF } ) ]
  | SET ( <set_index_option> [ ,...n ] ) 
    }
[ ; ]

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

<rebuild_index_option > ::=
{
    PAD_INDEX = { ON | OFF }
  | FILLFACTOR = fillfactor 
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | ONLINE = { ON | OFF } 
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE } 
     [ ON PARTITIONS ( { <partition_number_expression> | <range> } 
     [ , ...n ] ) ]
}
<range> ::= 
<partition_number_expression> TO <partition_number_expression>
}

<single_partition_rebuild_index_option> ::=
{
    SORT_IN_TEMPDB = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE } }
}

<set_index_option>::=
{
    ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
}

Argumentos

  • index_name
    Es 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.

  • ALL
    Especifica todos los índices asociados a la tabla o vista independientemente del tipo de índice. Si se especifica ALL y uno o más índices se encuentran en un grupo de archivos sin conexión o de solo lectura o la operación especificada no está permitida en uno o más tipos de índices, se produce un error en la instrucción. En la siguiente tabla se enumeran las operaciones de índice y los tipos de índices no permitidos.

    Si se especifica ALL con esta operación

    Se produce un error si la tabla tiene uno o más

    REBUILD WITH ONLINE = ON

    Índice XML

    Índice espacial

    REBUILD PARTITION = partition_number

    Índice sin particiones, índice XML, índice espacial o índice deshabilitado

    REORGANIZE

    Índices que tienen ALLOW_PAGE_LOCKS establecido en OFF

    REORGANIZE PARTITION = partition_number

    Índice sin particiones, índice XML, índice espacial o índice deshabilitado

    IGNORE_DUP_KEY = ON

    Índice espacial

    Índice XML

    ONLINE = ON

    Índice espacial

    Índice XML
    Nota de advertenciaAdvertencia

    Para obtener información detallada acerca de las operaciones de índices que pueden realizarse en línea, vea Directrices para operaciones de índices en línea.

    Si se especifica ALL con PARTITION = partition_number, es necesario alinear todos los índices. Esto significa que se crean particiones basadas en las funciones de partición equivalentes. Si se utiliza ALL con PARTITION, todas las particiones de índice con el mismo parámetro partition_number se vuelven a generar u organizar. Para obtener más información acerca de los índices con particiones, vea Tablas e índices con particiones.

  • database_name
    Es el nombre de la base de datos.

  • schema_name
    Es el nombre del esquema al que pertenece la tabla o la vista.

  • table_or_view_name
    Es el nombre de la tabla o vista asociada al índice. Para mostrar un informe de los índices de un objeto, utilice la vista de catálogo sys.indexes.

  • REBUILD [ WITH (<rebuild_index_option> [ ,... n]) ]
    Especifica que el índice se volverá a generar con unas columnas, un tipo de índice, un atributo de unicidad y un criterio de ordenación idénticos. Esta cláusula es equivalente a DBCC DBREINDEX. REBUILD habilita un índice deshabilitado. Cuando se regenera un índice clúster, no se vuelven a generar los índices no clúster asociados, a menos que se especifique la palabra clave ALL. Si no se especifican las opciones del índice, se aplican los valores de las opciones del índice existentes almacenados en sys.indexes. Para las opciones del índice cuyos valores no estén almacenados en sys.indexes, se aplica el valor predeterminado indicado en la definición del argumento de la opción.

    Al volver a generar un índice XML o un índice espacial, las opciones ONLINE = ON e IGNORE_DUP_KEY = ON no son válidas.

    Si se especifica ALL y la tabla subyacente es un montón, la operación de regeneración no tiene ningún efecto en la tabla. Se regeneran los índices no clúster asociados a la tabla.

    Si el modelo de recuperación de base de datos está establecido como simple u optimizado para cargas masivas de registros, la operación de regeneración se puede registrar mínimamente.

    [!NOTA]

    Cuando se regenera un índice XML principal, la tabla de usuario subyacente no está disponible mientras dura esta operación.

  • PARTITION
    Especifica que solamente se va a volver a generar o a reorganizar una partición de un índice. No es posible especificar PARTITION si index_name no es un índice con particiones.

    PARTITION = ALL vuelve a generar todas las particiones.

    Nota de advertenciaAdvertencia

    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 sea superior a 1.000.

  • partition_number
    Es el número de partición de un índice con particiones que se va a volver a generar o a reorganizar. partition_number es una expresión constante que puede hacer referencia a variables. Pueden ser variables o funciones de tipo definido por el usuario y funciones definidas por el usuario, pero no pueden hacer referencia a una instrucción Transact-SQL. El parámetro partition_number debe existir; de lo contrario se produce un error en la instrucción.

  • WITH (<single_partition_rebuild_index_option>)
    SORT_IN_TEMPDB, MAXDOP y DATA_COMPRESSION son las opciones que se pueden especificar cuando se vuelve a generar una partición única (PARTITION = n). No es posible especificar índices XML en una operación para volver a generar una partición única.

    La regeneración de un índice con particiones no se puede realizar en línea. Toda la tabla se bloquea durante esta operación.

  • DISABLE
    Marca el índice como deshabilitado y no disponible para el Motor de base de datos. Cualquier índice puede estar deshabilitado. La definición de índice de un índice deshabilitado se conserva en el catálogo del sistema sin datos del índice subyacente. La deshabilitación de un índice clúster evita que los usuarios obtengan acceso a los datos de la tabla subyacente. Para habilitar un índice, utilice ALTER INDEX REBUILD o CREATE INDEX WITH DROP_EXISTING. Para obtener más información, vea Deshabilitar índices y restricciones y Habilitar índices y restricciones.

  • REORGANIZE
    Especifica que el nivel hoja del índice se va a reorganizar. La instrucción ALTER INDEX REORGANIZE siempre se ejecuta en línea. Esto significa que los bloqueos de tabla a largo plazo no se mantienen y que las consultas o actualizaciones en la tabla subyacente pueden continuar durante la transacción ALTER INDEX REORGANIZE. No es posible especificar REORGANIZE para un índice deshabilitado o con ALLOW_PAGE_LOCKS establecido en OFF.

  • WITH ( LOB_COMPACTION = { ON | OFF } )
    Especifica que todas las páginas que contienen datos de objetos grandes (LOB) se compactan. Los tipos de datos LOB son image, text, ntext, varchar(max), nvarchar(max), varbinary(max) y xml. El compactar estos datos puede mejorar el uso del espacio en disco. El valor predeterminado es ON.

    • ON
      Todas las páginas que contienen datos de objetos grandes se compactan.

      Si reorganiza un índice clúster específico, se compactan todas las columnas LOB incluidas en el índice clúster. La reorganización de un índice no clúster compacta todas las columnas LOB que son columnas sin clave (incluidas) del índice.

      Cuando se especifica ALL, todos los índices asociados a la tabla o vista especificada se reorganizan y se compactan todas las columnas LOB asociadas al índice agrupado, a la tabla subyacente o al índice no clúster con columnas incluidas.

    • OFF
      Las páginas que contienen datos de objetos grandes no se compactan.

      OFF no tiene ningún efecto sobre un montón.

    Si no existen columnas LOB, la cláusula LOB_COMPACTION se omite.

  • SET ( <set_index option> [ ,... n] )
    Especifica las opciones del índice sin volver a generar ni organizar el índice. No es posible especificar SET para un índice deshabilitado.

  • PAD_INDEX = { ON | OFF }
    Especifica el relleno del índice. El valor predeterminado es OFF.

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

    • No se especifica OFF ni fillfactor.
      Las páginas de nivel intermedio se rellenan casi al máximo. Esto deja suficiente espacio para al menos una fila del tamaño máximo que puede tener el índice, según el conjunto de claves de las páginas intermedias.

    Para obtener más información, vea CREATE INDEX (Transact-SQL).

  • FILLFACTOR = fillfactor
    Especifica un porcentaje que indica cuánto debe llenar el Motor de base de datos el nivel de hoja de cada página de índice durante la creación o la modificación de este. fillfactor debe ser un valor entero entre 1 y 100. El valor predeterminado es 0.

    [!NOTA]

    Los valores de factor de relleno (fillfactor) 0 y 100 son idénticos.

    Un valor FILLFACTOR explícito solo se aplica la primera vez que se crea o se vuelve a generar el índice. Motor de base de datos no mantiene dinámicamente el porcentaje especificado de espacio disponible de las páginas. Para obtener más información, vea CREATE INDEX (Transact-SQL).

    Para ver el valor de factor de relleno, utilice sys.indexes.

    Nota importanteImportante

    La creación o modificación de un índice agrupado con un valor FILLFACTOR afecta a la cantidad de espacio de almacenamiento que ocupan los datos, dado que el Motor de base de datos vuelve a distribuir los datos cuando crea el índice agrupado.

  • SORT_IN_TEMPDB = { ON | OFF }
    Especifica si los resultados de orden se almacenan en tempdb. El valor predeterminado es OFF.

    • ON
      Los resultados de orden intermedios utilizados para generar el índice se almacenan en tempdb. Si tempdb se encuentra en un conjunto de discos distinto al de la base de datos de usuario, se puede reducir el tiempo necesario para crear un índice. 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.

    Si no se necesita una operación de ordenación o si la ordenación se puede realizar en la memoria, se omite la opción SORT_IN_TEMPDB.

    Para obtener más información, vea Opción SORT_IN_TEMPDB para índices.

  • 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. 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 de distribución. El valor predeterminado es OFF.

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

    • OFF
      La actualización automática de estadísticas está habilitada.

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

    Nota importanteImportante

    La deshabilitación del cálculo automático de estadísticas de distribución puede impedir que el optimizador de consultas elija los planes de ejecución óptimos para las consultas relativas a la tabla.

  • 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 indización. El valor predeterminado es OFF.

    Para un índice XML o un índice espacial, solo se admite ONLINE = OFF y, si ONLINE se establece en ON, se produce un error.

    [!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 admitidas por las ediciones de SQL Server, vea Características compatibles con las ediciones de SQL Server 2012.

    • 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, solo se mantiene un bloqueo de intención compartida (IS) en la tabla de origen. De esta forma, las consultas o actualizaciones realizadas en la tabla y los índices subyacentes pueden continuar. Al principio de la operación, se mantiene un bloqueo compartido (S) sobre el objeto de origen durante un breve período. Al final de la operación, durante un breve período, se mantiene un bloqueo S sobre el origen si se está creando un índice no clúster; o se adquiere un bloqueo SCH-M (modificación del esquema) cuando se crea o se quita un índice clúster en línea o cuando se regenera un índice clúster o no clúster. ONLINE no se puede establecer en ON cuando se crea un índice en una tabla temporal local.

    • OFF
      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 clúster, un índice espacial o un índice XML, o que vuelve a generar o quita un índice no clúster, adquiere un bloqueo de modificación del 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 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.

    Los índices, incluidos los índices de las tablas temp globales, se pueden volver a generar en línea, con las excepciones siguientes:

    • índices XML

    • Índices en tablas temp locales

    • Un subconjunto de un índice con particiones (un índice entero con particiones se puede regenerar en línea).

  • ALLOW_ROW_LOCKS = { ON | OFF }
    Especifica si se admiten los bloqueos de fila. El valor predeterminado es ON.

    • ON
      Los bloqueos de fila se permiten al obtener acceso al índice. El Motor de base de datos determina cuándo se utilizan los bloqueos de fila.

    • OFF
      Los bloqueos de fila no se utilizan.

  • ALLOW_PAGE_LOCKS = { ON | OFF }
    Especifica si se permiten bloqueos de página. El valor predeterminado es ON.

    • ON
      Se permiten bloqueos de página cuando se tiene acceso al índice. El Motor de base de datos determina el momento en que se utilizan bloqueos de página.

    • OFF
      No se utilizan bloqueos de página.

    [!NOTA]

    No es posible reorganizar un índice cuando ALLOW_PAGE_LOCKS está establecido en OFF.

  • MAXDOP **=**max_degree_of_parallelism
    Invalida la opción de configuración grado máximo de paralelismo durante 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 que se utilizan en la ejecución de planes paralelos. El máximo es 64 procesadores.

    Nota importanteImportante

    Aunque la opción MAXDOP se admite sintácticamente para todos los índices XML, ALTER INDEX utiliza en la actualidad un solo procesador para un índice espacial o un índice XML principal.

    max_degree_of_parallelism puede ser:

    • 1
      Suprime la generación de planes paralelos.

    • >1
      Restringe al número especificado el número máximo de procesadores que se utilizan en una operación de índices paralelos.

    • 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 admitidas por las ediciones de SQL Server, vea Características compatibles con las ediciones de SQL Server 2012.

  • 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:

    • NONE
      No se comprimen el índice ni las particiones especificadas.

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

    • PAGE
      El índice o las particiones especificadas se comprimen mediante la compresión de página.

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

  • ON PARTITIONS ( { <partition_number_expression> | <range> } [,...n] )
    Especifica las particiones a las que se aplica el valor DATA_COMPRESSION. Si el índice 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 aplica a todas las particiones de un índice con particiones.

    <partition_number_expression> se puede especificar de las maneras siguientes:

    • Proporcionar el número de una partición, por ejemplo: EN PARTICIONES (2).

    • Proporcionar los números de partición para varias particiones individuales separadas por comas, por ejemplo: EN PARTICIONES (1,5).

    • Proporcionar particiones individuales y de intervalos: EN PARTICIONES (2, 4, 6 A 8).

    <intervalo> puede especificarse como números de partición separados por la palabra A, por ejemplo: EN PARTICIONES (6 A 8).

    Para establecer tipos de compresión de datos diferentes para particiones distintas, 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)
)

Comentarios

No es posible utilizar ALTER INDEX para volver a crear particiones en un índice o moverlo a un grupo de archivos distinto. No se puede usar esta instrucción para modificar la definición de índice; por ejemplo, para agregar o eliminar columnas o cambiar su orden. Utilice CREATE INDEX con la cláusula DROP_EXISTING para realizar estas operaciones.

Cuando no se especifica una opción de forma explícita, se aplica el valor actual. Por ejemplo, si no se especifica un valor FILLFACTOR en la cláusula REBUILD, se utilizará el valor de factor de relleno almacenado en el catálogo del sistema durante el proceso de regeneración. Para ver el valor actual de las opciones de índice, utilice sys.indexes.

[!NOTA]

Los valores de ONLINE, MAXDOP y SORT_IN_TEMPDB no se almacenan en el catálogo del sistema. A menos que se especifiquen en la instrucción de índice, se utiliza el valor predeterminado de la opción.

En los equipos con varios procesadores, ALTER INDEX REBUILD, al igual que otras consultas, utiliza automáticamente más procesadores para realizar las operaciones de examen y ordenación asociadas a la modificación del índice. Cuando se ejecuta ALTER INDEX REORGANIZE, con o sin LOB_COMPACTION, el valor de grado máximo de paralelismo es una operación de un solo subproceso. Para obtener más información, vea Configurar operaciones de índice en paralelo.

No es posible volver a organizar o generar un índice si el grupo de archivos en el que se encuentra está sin conexión o está definido como de solo lectura. Cuando se especifica la palabra clave ALL y hay uno o más índices en un grupo de archivos sin conexión o de solo lectura, se produce un error en la instrucción.

Volver a generar índices

El proceso de volver a generar un índice quita y vuelve a crear el índice. Quita la fragmentación, utiliza espacio en disco al compactar las páginas según el valor de factor de relleno especificado o existente y vuelve a ordenar las filas del índice en páginas contiguas. Cuando se especifica ALL, todos los índices de la tabla se quitan y se vuelven a generar en una única transacción. No es necesario quitar las restricciones FOREIGN KEY por adelantado. Cuando se regeneran índices con 128 extensiones o más, el Motor de base de datos difiere las cancelaciones de asignación de página y sus bloqueos asociados hasta después de la confirmación de la transacción.

Con frecuencia, cuando se vuelven a generar o se reorganizan los índices pequeños no se reduce la fragmentación. Las páginas de índices pequeños se almacenan en extensiones mixtas. Las extensiones mixtas pueden estar compartidas por hasta ocho objetos, de modo que es posible que no se pueda reducir la fragmentación en un índice pequeño después de reorganizar o volver a generar dicho índice.

En SQL Server 2012, las estadísticas no se crean examinando todas las filas de la tabla cuando se crea o se vuelve a generar un índice con particiones. En su lugar, el optimizador de consultas usa el algoritmo de muestreo predeterminado para generar estadísticas. Para obtener estadísticas sobre índices con particiones mediante el examen de todas las filas de la tabla, use CREATE STATISTICS o UPDATE STATISTICS con la cláusula FULLSCAN.

En versiones anteriores de SQL Server, a veces se podía volver a generar un índice no clúster para corregir incoherencias provocadas por errores de hardware. En SQL Server 2008 y versiones posteriores, aún es posible reparar estas incoherencias entre el índice y el índice agrupado al volver a generar un índice no clúster sin conexión. Sin embargo, no es posible reparar las incoherencias de índices no agrupados mediante la regeneración del índice en línea, ya que el mecanismo de regeneración con conexión usará el índice no clúster existente como base para la regeneración y, por tanto, persistirá la incoherencia. En cambio, la regeneración del índice sin conexión hará que se examine el índice agrupado (o montón) y eliminará la incoherencia. Al igual que en las versiones anteriores, para recuperar incoherencias se recomienda restaurar los datos afectados desde una copia de seguridad. No obstante, es posible que pueda reparar las incoherencias del índice mediante la regeneración del índice no clúster sin conexión. Para obtener más información, vea DBCC CHECKDB (Transact-SQL).

Reorganizar índices

La reorganización de un índice emplea muy pocos recursos del sistema. Desfragmenta el nivel hoja de los índices clúster y no clúster de las tablas y las vistas reordenando físicamente las páginas de nivel hoja para que coincidan con el orden lógico de los nodos hoja, de izquierda a derecha. La reorganización también compacta las páginas de índice. La compactación se basa en el valor de factor de relleno existente. Para ver el valor de factor de relleno, utilice sys.indexes.

Cuando se especifica ALL, se reorganizan los índices relacionales, tanto clúster como no clúster, y los índices XML. Cuando se especifica ALL, se aplican algunas restricciones; vea la definición de ALL en la sección Argumentos.

Para obtener más información, vea Reorganizar y volver a generar índices.

Deshabilitar índices

Al deshabilitar un índice, se impide que el usuario pueda tener acceso al mismo y, en el caso de los índices clúster, a los datos de la tabla subyacente. La definición de índice permanece en el catálogo del sistema. Al deshabilitar un índice no clúster o clúster en una vista, se eliminan físicamente los datos del índice. La deshabilitación de un índice clúster evita el acceso a los datos, aunque estos permanecen en el árbol b hasta que el índice se quita o se vuelve a generar. Para ver el estado de un índice habilitado o deshabilitado, realice una consulta en la columna is_disabled de la vista de catálogo sys.indexes.

Si una tabla se encuentra en una publicación de replicación transaccional, no puede deshabilitar ningún índice que esté asociado con las columnas de clave principal. Estos índices son necesarios para la replicación. Para deshabilitar un índice, primero debe quitar la tabla de la publicación. Para obtener más información, vea Publicar datos y objetos de base de datos.

Utilice la instrucción ALTER INDEX REBUILD o CREATE INDEX WITH DROP_EXISTING para habilitar el índice. No es posible volver a generar un índice agrupado deshabilitado si la opción ONLINE está establecida en ON. Para obtener más información, vea Deshabilitar índices y restricciones.

Configurar opciones

Es posible establecer las opciones ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS, IGNORE_DUP_KEY y STATISTICS_NORECOMPUTE de un índice especificado sin volver a generar u organizar ese índice. Los valores modificados se aplican inmediatamente al índice. Para ver estos valores, utilice sys.indexes. Para obtener más información, vea Establecer opciones de índice.

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 cuando se obtiene 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 permiten los bloqueos de nivel de tabla cuando se tiene acceso al índice.

Si se especifica ALL al establecer las opciones de bloqueo de fila o página, la configuración se aplica a todos los índices. Cuando la tabla subyacente es un montón, la configuración se aplica de las siguientes formas:

ALLOW_ROW_LOCKS = ON u OFF

Al montón y a cualquier índice no clúster asociado.

ALLOW_PAGE_LOCKS = ON

Al montón y a cualquier índice no clúster asociado.

ALLOW_PAGE_LOCKS = OFF

Completamente a los índices no clúster. Esto significa que no se permite ningún bloqueo de página en los índices no clúster. En el montón, los únicos bloqueos no permitidos para la página son los bloqueos compartidos (S), de actualización (U) y exclusivos (X). El Motor de base de datos aún puede adquirir un bloqueo de página de intención (IS, IU o IX) por motivos internos.

Operaciones de índice en línea

Cuando se vuelve a generar un índice y la opción ONLINE está establecida en ON, los objetos subyacentes, las tablas y los índices asociados están disponibles para las consultas y la modificación de datos. Los bloqueos de tabla exclusivos solo se mantienen un espacio de tiempo muy reducido durante el proceso de modificación.

La reorganización de un índice siempre se realiza en línea. El proceso no mantiene bloqueos a largo plazo y, por ello, no bloquea las consultas o las actualizaciones en ejecución.

Únicamente se pueden realizar operaciones de índice simultáneas en línea en la misma tabla si se hace lo siguiente:

  • Crear varios índices no clúster.

  • Reorganizar diferentes índices en la misma tabla.

  • Reorganizar diferentes índices mientras se vuelven a generar índices que no se superponen en la misma tabla.

En todas las demás operaciones de índice en línea realizadas al mismo tiempo se produce un error. Por ejemplo, no es posible volver a generar dos o más índices en la misma tabla de forma simultánea ni crear un índice nuevo mientras se regenera un índice existente en la misma tabla.

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

Restricciones de los índices espaciales

Cuando se regenera un índice espacial, la tabla de usuario subyacente no está disponible mientras dura esta operación, porque el índice espacial tiene un bloqueo del esquema.

No se puede modificar la restricción PRIMARY KEY de la tabla de usuario mientras se define un índice espacial en una columna de esa tabla. Para cambiar la restricción PRIMARY KEY, quite primero todos los índices espaciales de la tabla. Después de modificar la restricción PRIMARY KEY, puede volver a crear cada uno de los índices espaciales.

No se puede especificar ningún índice espacial en una operación para volver a generar una partición única. Sin embargo, se pueden especificar índices espaciales cuando se regeneran todas las particiones.

Para cambiar opciones específicas de un índice espacial, como BOUNDING_BOX o GRID, puede usar una instrucción CREATE SPATIAL INDEX que especifica DROP_EXISTING = ON, o quitar el índice espacial y crear uno nuevo. Para obtener un ejemplo, vea CREATE SPATIAL INDEX (Transact-SQL).

Restricciones de índice de almacén de columnas

Excepto en la opción REBUILD, un índice de almacén de columnas optimizado de memoria xVelocity no se puede modificar. En su lugar se ha de quitar y volver a crear el índice de almacén de columnas.

Compresión de datos

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

Para evaluar cómo afecta el cambio del estado de compresión en una tabla, índice o partición, utilice el procedimiento almacenado sp_estimate_data_compression_savings.

Las restricciones siguientes se aplican a los índices con particiones:

  • Al utilizar ALTER INDEX ALL ..., no puede cambiar la configuración de compresión de una única partición si la tabla tiene índices no alineados.

  • La sintaxis ALTER INDEX <index> ... REBUILD PARTITION ... vuelve a generar la partición especificada del índice.

  • La sintaxis ALTER INDEX <index> ... REBUILD WITH ... vuelve a generar todas las particiones del índice.

Estadísticas

Cuando se ejecuta ALTER INDEX ALL … en una tabla, solo se actualizan las estadísticas asociadas a índices. Las estadísticas automáticas o manuales creadas en la tabla (en lugar de en un índice) no se actualizan.

Permisos

Para ejecutar ALTER INDEX, se necesita, como mínimo, el permiso ALTER en la tabla o en la vista.

Ejemplos

A. Volver a generar un índice

En el siguiente ejemplo se regenera un único índice en la tabla Employee.

USE AdventureWorks2012;
GO
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD;
GO

B. Regenerar todos los índices de una tabla y especificar opciones

En el siguiente ejemplo se especifica la palabra clave ALL. Así se regeneran todos los índices asociados a la tabla. Se especifican tres opciones.

USE AdventureWorks2012;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);
GO

C. Reorganizar un índice con compactación LOB

En el siguiente ejemplo, se reorganiza un solo índice clúster. Dado que el índice contiene un tipo de datos de LOB en el nivel hoja, la instrucción también compacta todas las páginas que contienen datos de objetos grandes. Observe que no es necesario especificar la opción WITH (LOB_COMPACTION) porque el valor predeterminado es ON.

USE AdventureWorks2012;
GO
ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
REORGANIZE ;
GO

D. Establecer opciones en un índice

En el siguiente ejemplo se establecen varias opciones en el índice AK_SalesOrderHeader_SalesOrderNumber.

USE AdventureWorks2012;
GO
ALTER INDEX AK_SalesOrderHeader_SalesOrderNumber ON
    Sales.SalesOrderHeader
SET (
    STATISTICS_NORECOMPUTE = ON,
    IGNORE_DUP_KEY = ON,
    ALLOW_PAGE_LOCKS = ON
    ) ;
GO

E. Deshabilitar un índice

En el siguiente ejemplo se deshabilita un índice no clúster de la tabla Employee.

USE AdventureWorks2012;
GO
ALTER INDEX IX_Employee_OrganizationNode ON HumanResources.Employee
DISABLE ;
GO

F. Deshabilitar restricciones

En el siguiente ejemplo se deshabilita una restricción PRIMARY KEY al deshabilitar el índice PRIMARY KEY. La restricción FOREIGN KEY de la tabla subyacente se deshabilita automáticamente y aparece un mensaje de advertencia.

USE AdventureWorks2012;
GO
ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department
DISABLE ;
GO

El conjunto de resultados devuelve este mensaje de advertencia.

Warning: Foreign key 'FK_EmployeeDepartmentHistory_Department_DepartmentID'

on table 'EmployeeDepartmentHistory' referencing table 'Department'

was disabled as a result of disabling the index 'PK_Department_DepartmentID'.

G. Habilitar restricciones

En el siguiente ejemplo se habilitan las restricciones PRIMARY KEY y FOREIGN KEY deshabilitadas en el ejemplo F.

La restricción PRIMARY KEY se habilita al volver a generar el índice PRIMARY KEY.

USE AdventureWorks2012;
GO
ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department
REBUILD ;
GO

A continuación, se habilita la restricción FOREIGN KEY.

ALTER TABLE HumanResources.EmployeeDepartmentHistory
CHECK CONSTRAINT FK_EmployeeDepartmentHistory_Department_DepartmentID;
GO

H. Regenerar un índice con particiones

En el siguiente ejemplo se vuelve a generar una única partición, de número 5, del índice con particiones IX_TransactionHistory_TransactionDate.

USE AdventureWorks;
GO
-- Verify the partitioned indexes.
SELECT *
FROM sys.dm_db_index_physical_stats (DB_ID(),OBJECT_ID(N'Production.TransactionHistory'), NULL , NULL, NULL);
GO
--Rebuild only partition 5.
ALTER INDEX IX_TransactionHistory_TransactionDate
ON Production.TransactionHistory
REBUILD Partition = 5;
GO

I. Cambiar la configuración de compresión de un índice

En el ejemplo siguiente se vuelve a generar un índice en una tabla sin particiones.

ALTER INDEX IX_INDEX1 
ON T1
REBUILD 
WITH ( DATA_COMPRESSION = PAGE )
GO

Para obtener más ejemplos de compresión de datos, vea Compresión de datos.

Vea también

Referencia

CREATE INDEX (Transact-SQL)

CREATE SPATIAL INDEX (Transact-SQL)

CREATE XML INDEX (Transact-SQL)

DROP INDEX (Transact-SQL)

sys.dm_db_index_physical_stats (Transact-SQL)

EVENTDATA (Transact-SQL)

Conceptos

Deshabilitar índices y restricciones

Índices XML (SQL Server)

Realizar operaciones de índice en línea

Reorganizar y volver a generar índices