Compartir a través de

Deshabilitar índices y restricciones

En este tema se describe cómo deshabilitar un índice o restricciones en SQL Server 2014 mediante SQL Server Management Studio o Transact-SQL. Al deshabilitar un índice, se impide que los usuarios accedan al índice, y si se trata de índices agrupados, también se impide el acceso a los datos de la tabla subyacente. La definición de índice permanece en metadatos y las estadísticas de índice se mantienen en índices no clúster. Al desactivar un índice no agrupado o agrupado en una vista, se eliminan físicamente los datos del índice. Deshabilitar un índice agrupado en una tabla impide el acceso a los datos; los datos siguen estando en la tabla, pero no están disponibles para las operaciones del lenguaje de manipulación de datos (DML) hasta que se quita o se vuelve a generar el índice.

En este tema

Antes de empezar

Limitaciones y restricciones

  • El índice no se actualiza ni se mantiene mientras está deshabilitado.

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

  • No se puede crear un índice que use el mismo nombre que un índice deshabilitado existente.

  • Se puede quitar un índice deshabilitado.

  • Al deshabilitar un índice único, la restricción PRIMARY KEY o UNIQUE y todas las restricciones FOREIGN KEY que hacen referencia a las columnas indizada de otras tablas también están deshabilitadas. Al deshabilitar un índice clúster, también se deshabilitan todas las restricciones FOREIGN KEY entrantes y salientes de la tabla subyacente. Los nombres de restricción se muestran en un mensaje de advertencia cuando el índice está deshabilitado. Después de volver a generar el índice, todas las restricciones deben habilitarse manualmente mediante la instrucción ALTER TABLE CHECK CONSTRAINT.

  • Los índices no clúster se deshabilitan automáticamente cuando el índice clúster asociado está deshabilitado. No se pueden habilitar hasta que el índice agrupado de la tabla o vista esté habilitado o se quite el índice agrupado de la tabla. Los índices no clúster deben habilitarse explícitamente, a menos que el índice clúster se haya habilitado mediante la instrucción ALTER INDEX ALL REBUILD.

  • La instrucción ALTER INDEX ALL REBUILD reconstruye y habilita todos los índices deshabilitados de la tabla, excepto los índices deshabilitados en las vistas. Los índices de las vistas deben estar habilitados en una instrucción ALTER INDEX ALL REBUILD independiente.

  • Al deshabilitar un índice agrupado en una tabla también se deshabilitan todos los índices agrupados y no agrupados en vistas que hacen referencia a esa tabla. Estos índices se deben volver a generar igual que 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 recompilar el índice agrupado.

  • Puede reconstruir un índice no agrupado deshabilitado de forma online cuando la tabla no tiene un índice agrupado deshabilitado. Sin embargo, siempre debe reconstruir un índice clúster deshabilitado en modo sin conexión si utiliza la instrucción ALTER INDEX REBUILD o CREATE INDEX WITH DROP_EXISTING. Para obtener más información sobre las operaciones de índice en línea, vea 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 nuevas estadísticas en una columna cuando el índice está deshabilitado y existen las condiciones siguientes:

    • AUTO_CREATE_STATISTICS está establecido en ON

    • No hay estadísticas existentes para la columna.

    • Las estadísticas son necesarias durante la optimización de consultas.

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

  • La creación de un nuevo índice agrupado habilita los índices no agrupados deshabilitados previamente. Para obtener más información, vea Habilitar índices y restricciones.

Seguridad

Permisos

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

Uso de SQL Server Management Studio

Para deshabilitar un índice

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

  2. Haga clic en el signo más para expandir la carpeta Tablas .

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

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

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

  6. En el cuadro de diálogo Deshabilitar índices , compruebe que el índice correcto está en la cuadrícula Índices para deshabilitar y haga clic en Aceptar.

Para deshabilitar todos los índices de una tabla

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

  2. Haga clic en el signo más para expandir la carpeta Tablas .

  3. Haga clic en el signo más para expandir la tabla en la que desea 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 , compruebe que los índices correctos están en la cuadrícula Índices para deshabilitar y haga clic en Aceptar. Para quitar un índice de la cuadrícula Índices para deshabilitarla , seleccione el índice y presione la tecla Eliminar.

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

Nombre del í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 creó el índice.

Tipo de índice
Muestra el tipo del índice: Clustered, Nonclustered, Spatial o XML.

Estado
Muestra el estado de la operación de deshabilitación. Los valores posibles después de la ejecución son:

  • En blanco

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

  • En curso

    Se ha iniciado la deshabilitación de los índices, pero no se ha completado.

  • Éxito

    La operación de deshabilitación se completó correctamente.

  • Error

    Se encontró un error durante la operación de deshabilitación del índice y la operación no se completó correctamente.

  • Detenido

    La deshabilitación del índice no se completó correctamente porque el usuario detuvo 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 rara vez es lo suficientemente ancha como para leer el texto completo del mensaje. Hay dos maneras de obtener el texto completo:

  • Pase el puntero del ratón sobre la celda del mensaje para mostrar un tooltip con el texto de error.

  • Haga clic en el hipervínculo para mostrar un cuadro de diálogo que muestra el error completo.

Uso de Transact-SQL

Para deshabilitar un índice

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

  2. En la barra de Estándar, haga clic en Nueva consulta.

  3. Copie y pegue el siguiente ejemplo en la ventana de consulta y haga clic en Ejecutar.

    USE AdventureWorks2012;  
GO  
-- disables the IX_Employee_OrganizationLevel_OrganizationNode index  
-- on the HumanResources.Employee table  
ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee  
DISABLE;

Para 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 de Estándar, haga clic en Nueva consulta.

  3. Copie y pegue el siguiente ejemplo en la ventana de consulta y haga clic en Ejecutar.

    USE AdventureWorks2012;  
GO  
-- Disables all indexes on the HumanResources.Employee table.  
ALTER INDEX ALL ON HumanResources.Employee  
DISABLE;

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

  • Last updated on