ALTER INDEX (Transact-SQL)

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 opciones en él.

Convenciones de sintaxis de Transact-SQL

Sintaxis

ALTER INDEX { index_name | ALL }
    ON <object>
    { REBUILD 
        [ [ 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
}

<single_partition_rebuild_index_option> ::=
{
    SORT_IN_TEMPDB = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
}

<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 sólo 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 Columnas de tipos de datos de objetos grandes: image, text, ntext, varchar(max), nvarchar(max), varbinary(max) y xml
  REBUILD PARTITION = partition_number	Índice sin particiones, XML o deshabilitado
  REORGANIZE	Índices con ALLOW_PAGE_LOCKS establecido en OFF
  REORGANIZE PARTITION = partition_number	Índice sin particiones, XML o deshabilitado
  SET	Índice deshabilitado

  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 vuelve a generar un índice agrupado, no se vuelven a generar los índices sin agrupar 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.

  Las opciones ONLINE e IGNORE_DUP_KEY no son válidas al volver a generar un índice XML.

  Si se especifica ALL y la tabla base es un montón, la operación de regeneración no tiene ningún efecto sobre la tabla. Se vuelven a generar los índices sin agrupar asociados a la tabla.

  Si el modelo de recuperación de base de datos está establecido como simple o por medio de registros de operaciones masivas, la operación de regeneración se puede registrar mínimamente. Para obtener más información, vea Elegir un modelo de recuperación para las operaciones de índice.

  [!NOTA] Cuando se vuelve a generar un índice XML principal, la tabla de usuario subyacente no está disponible mientras dura esta operación.

• PARTITION
  Especifica que sólo se va a volver a generar y organizar una partición de un índice. No es posible especificar PARTITION si index_name no es un índice con particiones.

• 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. Éstas incluyen variables o funciones de tipo definido por el usuario y funciones definidas por el usuario, pero no pueden hacer referencia a una instrucción de 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 y MAXDOP 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 SQL Server 2005 Database Engine (Motor de base de datos de SQL Server 2005). 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 agrupado 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.

• REORGANIZE
  Especifica que el nivel de hoja del índice se va a reorganizar. Esta cláusula es equivalente a DBCC INDEXDEFRAG. 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 a 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. Al compactar estos datos se 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.

    Al reorganizar un índice agrupado especificado se compactan todas las columnas LOB incluidas en él. Al reorganizar un índice sin agrupar se compactan todas las columnas LOB que no son columnas sin clave (incluidas) del índice. Para obtener más información, vea Crear índices con columnas incluidas.

    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, la tabla subyacente o el índice sin agrupar 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 hay columnas LOB, la cláusula LOB_COMPACTION se pasa por alto.

• 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 de índice. El valor predeterminado es OFF.

  • ON
    El porcentaje de espacio libre especificado por FILLFACTOR se aplica a 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 Database Engine (Motor de base de datos) el nivel de hoja de cada página del índice durante la creación o modificación de un índice. fillfactor debe ser un valor entero entre 1 y 100. El valor predeterminado es 0.

  [!NOTA] Los valores de factor de relleno 0 y 100 son idénticos.

  Un valor FILLFACTOR explícito sólo se aplica cuando se genera o reorganiza el índice por primera vez. Database Engine (Motor de base de datos) no mantiene dinámicamente el porcentaje especificado de espacio libre 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.

  Importante:
  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 Database Engine (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 intermedio 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, se incrementa la cantidad de espacio en disco que se utiliza en la generación del índice.
  • OFF
    Los resultados de orden intermedio se almacenan en la misma base de datos que el índice.

  Si no se necesita una operación de orden o ésta puede realizarse en la memoria, se pasa por alto la opción SORT_IN_TEMPDB.

  Para obtener más información, vea tempdb y la creación de índices.

• IGNORE_DUP_KEY = { ON | OFF }
  Especifica la respuesta de error a los valores de clave duplicados en una transacción de inserción de varias filas en un índice agrupado o sin agrupar único. El valor predeterminado es OFF.

  • ON
    Se emite un mensaje de advertencia y sólo se produce un error en las filas que infringen el índice UNIQUE.
  • OFF
    Se emite un mensaje de error y se revierte toda la transacción.

  La configuración de IGNORE_DUP_KEY se aplica sólo a operaciones de inserción realizadas tras la creación o regeneración del índice. La configuración no tiene efecto durante la operación de indización. IGNORE_DUP_KEY no tiene efecto en una instrucción UPDATE.

  IGNORE_DUP_KEY no se puede establecer en ON para índices XML e índices creados en una vista. Para obtener más información, vea CREATE INDEX (Transact-SQL).

• 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 no actualizadas no se vuelven a calcular automáticamente.
  • OFF
    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.

  Importante:
  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 e índices asociados están disponibles para consultas y modificación de datos durante la operación de indización. El valor predeterminado es OFF.

  [!NOTA] Las operaciones de índice en línea sólo están disponibles en SQL Server 2005 Enterprise Edition.

  • 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, sólo se mantiene un bloqueo de intención compartida 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 adquiere un bloqueo S sobre el origen si se está creando un índice sin agrupar; o se adquiere un bloqueo SCH-M (modificación del esquema) cuando se crea o se quita un índice agrupado con conexión o cuando se vuelve a generar un índice agrupado o sin agrupar. 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 agrupado, o vuelve a generar o quita un índice sin agrupar, adquiere un bloqueo de modificación del esquema (Sch-M) sobre la tabla. De esta forma se impide el acceso de todos los usuarios a la tabla subyacente durante la operación. Una operación de índice sin conexión que crea un índice sin agrupar adquiere un bloqueo compartido (S) sobre la tabla. De esta forma se impiden las actualizaciones en la tabla subyacente pero se permiten las operaciones de lectura, como las instrucciones SELECT.

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

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

  • Índices deshabilitados
  • Índices XML
  • Índices en tablas temp locales
  • Índices con particiones
  • Índices agrupados si la tabla subyacente contiene tipos de datos LOB
  • Índices sin agrupar definidos con columnas de tipo de datos LOB

  Los índices sin agrupar se pueden volver a generar en línea si la tabla contiene tipos de datos LOB pero ninguna de estas columnas se utiliza en la definición de índice como columna de clave o sin clave.

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

  • ON
    Se permiten bloqueos de fila en el acceso al índice. El Database Engine (Motor de base de datos) determina el momento en que se utilizan bloqueos de fila.
  • OFF
    No se utilizan bloqueos de fila.

• 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. Database Engine (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 max degree of parallelism durante la operación de índice. Para obtener más información, vea max degree of parallelism (opción). Utilice MAXDOP para establecer un límite en el número de procesadores utilizados en la ejecución de un plan paralelo. El valor máximo es de 64 procesadores.

  max_degree_of_parallelism puede ser:

  • 1
    Suprime la generación del plan paralelo.
  • >1
    Restringe el número máximo de procesadores utilizado en una operación de índices paralelos al especificado.
  • 0 (predeterminado)
    Utiliza el número real de procesadores o menos de acuerdo con la carga actual de trabajo del sistema.

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

  [!NOTA] Las operaciones de índices paralelos sólo están disponibles en SQL Server 2005 Enterprise Edition.

Notas

No es posible utilizar ALTER INDEX para volver a crear particiones en un índice o moverlo a un grupo de archivos distinto. No es posible utilizar esta instrucción para modificar la definición de índice, como 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. For example, if a FILLFACTOR setting is not specified in the REBUILD clause, the fill factor value stored in the system catalog will be used during the rebuild process. 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 orden asociadas a la modificación del índice. Cuando se ejecuta ALTER INDEX REORGANIZE, con o sin LOB_COMPACTION, el valor max degree of parallelism 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 no tiene conexión o está definido como de sólo 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 sólo lectura, se produce un error en la instrucción.

Volver a generar índices

El proceso de volver a crear un índice quita y vuelve a crear el índice. De esta forma se quita la fragmentación, se reclama el espacio en disco compactando las páginas según el valor de factor de relleno especificado o existente, y se vuelven 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 vuelven a generar índices con 128 extensiones o más, el Database Engine (Motor de base de datos) difiere de las cancelaciones de asignación de página y sus bloqueos asociados hasta después de la confirmación de la transacción. Para obtener más información, vea Quitar y volver a generar objetos grandes.

[!NOTA] En general, al regenerar o reorganizar índices pequeños no se suele reducir la fragmentación. Las páginas de índices pequeños se almacenan en extensiones mixtas. Las extensiones mixtas son compartidas por hasta ocho objetos, de modo que la fragmentación de un índice pequeño podría no reducirse tras la reorganización o regeneración del índice. Para obtener más información acerca de las extensiones mixtas, vea Páginas y extensiones.

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

Reorganizar índices

La reorganización de un índice utiliza muy pocos recursos del sistema. Desfragmenta el nivel de hoja de los índices agrupados y sin agrupar de las tablas y las vistas al volver a ordenar físicamente las páginas de nivel de hoja para hacer coincidir el orden lógico de izquierda a derecha de los nodos hoja. 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 agrupados como sin agrupar, 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

La deshabilitación de un índice evita que los usuarios obtengan acceso al mismo y, en los índices agrupados, a los datos de la tabla subyacente. La definición de índice permanece en el catálogo del sistema. La deshabilitación de un índice sin agrupar o agrupado en una vista elimina físicamente los datos del índice. La deshabilitación de un índice agrupado evita el acceso a los datos, aunque éstos 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 a la columna is_disabled de la vista de catálogo sys.indexes.

Si una tabla se encuentra en una publicación de réplica transaccional, no es posible deshabilitar ningún índice asociado a las columnas de clave principal. Estos índices son necesarios para la réplica. 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.

Establecer 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, los bloqueos de nivel de fila, página y tabla se permiten al tener acceso al índice. Database Engine (Motor de base de datos) selecciona el bloqueo apropiado y puede cambiar de escala el bloqueo desde un bloqueo de fila o página a un bloqueo de tabla.

Si ALLOW_ROW_LOCKS = OFF y ALLOW_PAGE_LOCK = OFF, sólo se permiten los bloqueos de nivel de tabla cuando se tiene acceso al índice. Para obtener más información sobre la configuración de la granularidad del bloqueo de un índice, vea Personalizar el bloqueo de un í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 sin agrupar asociado.
ALLOW_PAGE_LOCKS = ON	Al montón y a cualquier índice sin agrupar asociado.
ALLOW_PAGE_LOCKS = OFF	Completamente a los índices sin agrupar. Esto significa que no se permite ningún bloqueo de página en los índices sin agrupar. En el montón, los únicos bloqueos no permitidos para la página son los compartidos (S), de actualización (U) y exclusivos (X). Database Engine (Motor de base de datos) aún puede adquirir un bloqueo de página de intención (IS, IU o IX) por motivos internos.

Para obtener más información, vea Concentración de bloqueos (motor de base de datos).

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 sólo 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.

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

• Crear varios índices sin agrupar.
• Reorganizar índices diferentes en la misma tabla.
• Reorganizar índices diferentes 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 índices en línea.

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 vuelve a generar un único índice en la tabla Employee.

USE AdventureWorks;
GO
ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee
REBUILD;
GO

B. Volver a generar todos los índices de una tabla y especificar opciones

En el siguiente ejemplo, se especifica la palabra clave ALL. Con ella, se vuelven a generar todos los índices asociados a la tabla. Se especifican tres opciones.

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

C. Volver a organizar un índice con compactación LOB

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

USE AdventureWorks;
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 AdventureWorks;
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 sin agrupar de la tabla Employee.

USE AdventureWorks;
GO
ALTER INDEX IX_Employee_ManagerID 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 AdventureWorks;
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 AdventureWorks;
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. Volver a generar un índice con particiones

En el siguiente ejemplo se vuelve a generar una única partición, con el número 5, del índice con particiones IX_TransactionHistory_TransactionDate. En este ejemplo se da por supuesto que se ha instalado el ejemplo de índice con particiones. Para obtener información sobre la instalación, vea Readme_PartitioningScript.

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

Vea también

Referencia

CREATE INDEX (Transact-SQL)
sys.dm_db_index_physical_stats
EVENTDATA (Transact-SQL)

Otros recursos

Deshabilitar índices
Índices en columnas de tipo de datos xml
Realizar operaciones de índices en línea
Reorganizar y volver a generar índices

Ayuda e información

Obtener ayuda sobre SQL Server 2005