Deshabilitar índices y restricciones

Se aplica a: SQL Server Azure SQL Database Azure SQL Managed Instance

En este artículo, se explica cómo deshabilitar un índice o restricción en SQL Server mediante SQL Server Management Studio o Transact-SQL. 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 del índice se conserva en los metadatos y las estadísticas de índice se mantienen en índices no clúster. La deshabilitación de un índice agrupado en una vista o un índice no agrupado elimina físicamente los datos del índice.

La deshabilitación de un índice clúster en una tabla impide el acceso a los datos. Los datos siguen en la tabla pero dejan de estar disponibles para las operaciones de lenguaje de manipulación de datos (DML) hasta que se quite o recompile el índice.

Limitaciones

El índice no se mantiene mientras está deshabilitado.

El optimizador de consultas no tiene en cuenta el índice deshabilitado a la hora de crear planes de ejecución de consultas. Además, las consultas que hacen referencia al índice deshabilitado con una sugerencia de tabla generan un error.

No puedes crear un índice que use el mismo nombre que un índice existente deshabilitado.

Se puede quitar un índice deshabilitado.

Al deshabilitar un índice único, también se deshabilitan la restricción PRIMARY KEY o UNIQUE y todas las restricciones FOREIGN KEY que hacen referencia a las columnas indizadas de otras tablas. Al deshabilitar un índice clúster, se deshabilitan también todas las restricciones FOREIGN KEY entrantes y salientes de la tabla subyacente. Los nombres de las restricciones se enumeran en un mensaje de advertencia cuando se deshabilita el índice. Después de recompilar el índice, se deben habilitar todas las restricciones manualmente mediante la instrucción ALTER TABLE CHECK CONSTRAINT.

Los índices no clúster se deshabilitan automáticamente cuando se deshabilita el índice clúster asociado. No se pueden habilitar hasta que se habilita el índice clúster en la tabla o vista, o bien hasta que se quita el índice clúster en la tabla. Los índices no clúster deben habilitarse de forma explícita, a no ser que el índice clúster se haya habilitado mediante la instrucción ALTER INDEX ALL REBUILD.

La instrucción ALTER INDEX ALL REBUILD vuelve a generar y habilita todos los índices deshabilitados de la tabla, excepto los índices deshabilitados en las vistas. Los índices en las vistas deben habilitarse en una instrucción ALTER INDEX ALL REBUILD independiente.

Al deshabilitar un índice clúster en una tabla también se deshabilitan todos los índices clúster y no clúster en las vistas que hacen referencia a esa tabla. Estos índices deben volverse a generar como los de la tabla a la que se hace referencia.

No se puede tener acceso a las filas de datos del índice clúster deshabilitado excepto para quitar o volver a generar el índice clúster.

Se puede recompilar un índice no clúster deshabilitado en línea cuando la tabla no tenga un índice clúster deshabilitado. Sin embargo, siempre debes volver a generar un índice clúster deshabilitado sin conexión si utilizas la instrucción ALTER INDEX REBUILD o CREATE INDEX WITH DROP_EXISTING. Para obtener más información sobre las operaciones de índices en línea, ve Realizar operaciones de índice en línea.

La instrucción CREATE STATISTICS no se puede ejecutar correctamente en una tabla que tenga un índice clúster deshabilitado.

La opción de base de datos AUTO_CREATE_STATISTICS crea estadísticas en una columna cuando el índice está deshabilitado y existen las condiciones siguientes:

• El valor de AUTO_CREATE_STATISTICS está establecido en ON.
• No hay estadísticas existentes para la columna.
• Las estadísticas son obligatorias durante la optimización de consultas.

Si un índice clúster está deshabilitado, DBCC CHECKDB no puede devolver información acerca de la tabla subyacente; en su lugar, la instrucción indica que el índice clúster está deshabilitado. DBCC INDEXDEFRAG no se puede usar para desfragmentar un índice deshabilitado; la instrucción genera un mensaje de error. Puede usar DBCC DBREINDEX para recompilar un índice deshabilitado.

Al crear un nuevo índice clúster se habilitan los índices no clúster deshabilitados previamente. Para obtener más información, consulte Enable Indexes and Constraints.

Si la tabla es un montón, se volverán a generar todos los índices no clúster.

Permisos

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

Uso de SQL Server Management Studio

Deshabilitación de un índice

1. En el Explorador de objetos, haz clic en el signo más para expandir la base de datos que contiene la tabla en la que deseas deshabilitar un índice.

2. Selecciona el signo más para expandir la carpeta Tablas.

3. Haz clic en el signo más para expandir la tabla en la que deseas deshabilitar un índice.

4. Haz clic en el signo más para expandir la carpeta Índices .

5. Haga clic con el botón derecho en el índice que quiera deshabilitar y seleccione Deshabilitar.

   Nota:

   Si la tabla está abierta en modo Diseño, el control Deshabilitar no está disponible. Para continuar, cierre el diseñador de tablas y comience de nuevo.

6. En el cuadro de diálogo Deshabilitar índices, comprueba que el índice correcto se encuentra en la cuadrícula Índices que vas a deshabilitar y haz clic en Aceptar.

Deshabilitar todos los índices de una tabla

1. En el Explorador de objetos, haz clic en el signo más para expandir la base de datos que contiene la tabla en la que deseas deshabilitar los índices.

2. Selecciona el signo más para expandir la carpeta Tablas.

3. Haz clic en el signo más para expandir la tabla en la que deseas deshabilitar los índices.

4. Haga clic con el botón derecho en la carpeta Índices y seleccione Deshabilitar todo.

5. En el cuadro de diálogo Deshabilitar índices, comprueba que los índices correctos se encuentran en la cuadrícula Índices que vas a deshabilitar y haz clic en Aceptar. Para quitar un índice de la cuadrícula Índices que vas a deshabilitar, selecciona el índice y, a continuación, presiona la tecla Suprimir.

La siguiente información está disponible en el cuadro de diálogo Deshabilitar índices :

• Nombre de índice

  Muestra el nombre del índice. Durante la ejecución, esta columna también muestra un icono que representa el estado.

• Nombre de tabla

  Muestra el nombre de la tabla o vista en la que se ha creado el índice.

• Tipo de índice

  Muestra el tipo de índice: Agrupado, No agrupado, Espacialo XML.

• Estado

  Muestra el estado de la operación de deshabilitación. Los valores posibles tras la ejecución son:

  • En blanco

    Antes de la ejecución, el estado está en blanco.

  • En curso

    La deshabilitación de los índices se ha iniciado, pero no ha finalizado.

  • Success

    La operación de deshabilitación ha finalizado correctamente.

  • Error

    Se ha encontrado un error durante la operación de deshabilitación de índices; la operación no ha finalizado correctamente.

  • Stopped

    La deshabilitación del índice no ha finalizado correctamente porque el usuario ha detenido la operación.

• Mensaje

  Proporciona el texto de los mensajes de error durante la operación de deshabilitación. Durante la ejecución, los errores aparecen como hipervínculos. El texto de los hipervínculos describe el cuerpo del error. La columna Mensaje pocas veces es lo suficientemente ancha para poder leer el texto completo del mensaje. Hay dos maneras de leer el texto completo:

  • Mueve el puntero sobre la celda del mensaje para que aparezca la información sobre herramientas con el texto de error.
  • Haz clic en el hipervínculo para mostrar un cuadro de diálogo con el error completo.

Uso de Transact-SQL

Los ejemplos de código de Transact-SQL de este artículo utilizan la base de datos de ejemplo AdventureWorks2022 o AdventureWorksDW2022, que se pueden descargar desde la página principal de Ejemplos y proyectos de la comunidad de Microsoft SQL Server.

Deshabilitación de un índice

1. En el Explorador de objetos, conéctese a una instancia del Motor de base de datos.

2. En la barra Estándar, seleccione Nueva consulta.

3. Copie y pegue el ejemplo siguiente en la ventana de consulta y seleccione Ejecutar. En este ejemplo se deshabilita el índice IX_Employee_OrganizationLevel_OrganizationNode de la tabla HumanResources.Employee.

   USE AdventureWorks2022;
   GO
   
   ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode
       ON HumanResources.Employee
   DISABLE;
   

Deshabilitar todos los índices de una tabla

1. En el Explorador de objetos, conéctese a una instancia del Motor de base de datos.

2. En la barra Estándar, seleccione Nueva consulta.

3. Copie y pegue el ejemplo siguiente en la ventana de consulta y seleccione Ejecutar. En este ejemplo se deshabilitan todos los índices de la tabla HumanResources.Employee.

   USE AdventureWorks2022;
   GO
   
   ALTER INDEX ALL ON HumanResources.Employee
   DISABLE;
   

Contenido relacionado

• ALTER INDEX (Transact-SQL)