• Artículo

Directrices para deshabilitar índices

Al deshabilitar un índice, se impide que el usuario pueda tener acceso al mismo y, en el caso de los índices agrupados, a los datos de la tabla subyacente. SQL Server Database Engine (Motor de base de datos de SQL Server) puede deshabilitar automáticamente un índice durante una actualización de SQL Server, o bien se puede deshabilitar de forma manual. Para obtener más información, vea Deshabilitar índices.

Se puede deshabilitar cualquier tipo de índice. Cuando se deshabilita un índice, se aplican las siguientes reglas:

  • Si el índice es único, 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. El usuario que deshabilita el índice debe tener permisos ALTER para esas tablas o la instrucción ALTER INDEX DISABLE generará un error. Si el índice está agrupado, se deshabilitan 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 volver a generar el índice, se deben habilitar las restricciones manualmente mediante la instrucción ALTER TABLE CHECK CONSTRAINT.

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

  • El optimizador de consultas no tiene en cuenta el índice 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 se puede crear un índice que utilice el mismo nombre que un índice deshabilitado existente, porque la definición del índice sigue existiendo en los metadatos.

  • Se puede quitar un índice deshabilitado.

Deshabilitar índices no agrupados

Al deshabilitar un índice no agrupado se eliminan físicamente los datos del índice. Sin embargo, la definición del índice permanece en los metadatos. Las siguientes directrices adicionales se aplican al deshabilitar índices no agrupados:

  • Las estadísticas del índice permanecen en su sitio y se actualizan automáticamente cuando sea necesario.

  • Los índices no agrupados se deshabilitan automáticamente cuando se deshabilita el índice agrupado asociado. No se pueden habilitar hasta que se habilita el índice agrupado en la tabla o vista, o bien hasta que se quita el índice agrupado en la tabla. Los índices no agrupados deben habilitarse de forma explícita, a no ser que el índice agrupado se haya habilitado mediante la instrucción ALTER INDEX ALL REBUILD. Para obtener más información, vea Directrices para habilitar índices y restricciones.

Deshabilitar índices agrupados

Las siguientes directrices adicionales se aplican al deshabilitar índices agrupados:

  • No se puede tener acceso a las filas de datos del índice agrupado deshabilitado excepto para quitar o volver a generar el índice agrupado. Esto significa lo siguiente:

    • Estas operaciones generarán un error: las instrucciones SELECT, UPDATE, DELETE, INSERT, CREATE INDEX, CREATE STATISTICS, UPDATE STATISTICS (en el índice) y ALTER TABLE que modifican columnas de tabla o restricciones.

    • Estas operaciones se realizarán correctamente: CREATE VIEW, DROP VIEW, CREATE TRIGGER, DROP TRIGGER, DROP INDEX, ALTER TABLE ENABLE/DISABLE TRIGGER, TRUNCATE TABLE y DROP TABLE.

    • Los índices no agrupados no se pueden crear mientras el índice agrupado esté deshabilitado.

  • Los índices no agrupados existentes y los índices XML asociados a la tabla se deshabilitan automáticamente y no se puede tener acceso a ellos.

  • Todos los índices agrupados y no agrupados en las vistas que hacen referencia a la tabla están deshabilitados. Estos índices deben volverse a generar como los de la tabla a la que se hace referencia.

Deshabilitar restricciones

Estas directrices adicionales se aplican al deshabilitar las restricciones PRIMARY KEY, FOREIGN KEY y UNIQUE:

  • Las restricciones PRIMARY KEY y UNIQUE se deshabilitan al deshabilitar el índice asociado mediante la instrucción ALTER INDEX DISABLE.

  • Cuando se deshabilita una restricción PRIMARY KEY, también se deshabilitan todas las restricciones FOREIGN KEY asociadas. Esto es equivalente a establecer la opción NOCHECK CONSTRAINT en la restricción.

  • Debe tener los permisos ALTER o CONTROL para las tablas a las que se hace referencia.

  • Si se declara una acción CASCADE UPDATE o DELETE en una referencia de clave externa y esta referencia está deshabilitada, todas las instrucciones de actualización o eliminación que causarían que la restricción propagase la modificación a la tabla de referencia generarán un error.

  • Es posible que se agreguen valores duplicados por accidente a una tabla mientras el índice PRIMARY KEY o UNIQUE está deshabilitado o bien, en una actualización de SQL Server, debido al cambio que causó que el índice estuviera deshabilitado. Debe corregir de forma manual las filas duplicadas antes de que se pueda habilitar el índice correctamente. Las siguientes soluciones son posibles:

    • Quitar o cambiar de forma manual los valores duplicados.

    • Si el índice UNIQUE no se creó como resultado de crear una restricción UNIQUE, utilice CREATE INDEX WITH DROP_EXISTING para volver a crear el índice sin especificar UNIQUE.

    • Si el índice se creó como un derivado de una restricción PRIMARY KEY o UNIQUE, deberá quitar la restricción. Entonces se quitará el índice. Para una restricción PRIMARY KEY, también se deben quitar las restricciones FOREIGN KEY.

  • Las restricciones FOREIGN KEY y CHECK que están deshabilitadas están marcadas como is_not_trusted. Están visibles en las vistas de catálogo sys.check_constraints y sys.foreign_keys. Esto significa que el sistema ya no comprueba la restricción para todas las filas de la tabla. Incluso cuando se vuelve a habilitar la restricción, ésta no comprobará de nuevo las filas existentes en la tabla a no ser el usuario especifique la opción WITH CHECK de ALTER TABLE. Especificar WITH CHECK vuelve a marcar la restricción como de confianza.

    En el ejemplo siguiente se deshabilita una restricción que limita los salarios aceptados en los datos. NOCHECK CONSTRAINT se utiliza con ALTER TABLE para deshabilitar la restricción y permitir una inserción que normalmente infringiría la restricción. WITH CHECK CHECK CONSTRAINT vuelve a habilitar la restricción y también valida los datos existentes con la restricción que se ha habilitado de nuevo.

    CREATE TABLE cnst_example 
(id INT NOT NULL,
    name VARCHAR(10) NOT NULL,
    salary MONEY NOT NULL
    CONSTRAINT salary_cap CHECK (salary < 100000);
)

-- Disable the constraint.
ALTER TABLE cnst_example NOCHECK CONSTRAINT salary_cap;

-- Reenable the constraint.
ALTER TABLE cnst_example WITH CHECK CHECK CONSTRAINT salary_cap;

Deshabilitar índices en vistas

Al deshabilitar un índice agrupado en un vista, se eliminan físicamente los datos del índice. Las siguientes directrices adicionales se aplican al deshabilitar índices en vistas:

  • Deshabilitar un índice agrupado en una vista no evita modificaciones en la tabla subyacente.

  • Deshabilitar un índice agrupado en una vista también deshabilita todos los índices no agrupados de esa vista.

  • Se eliminan las filas de datos del índice para los índices agrupados y no agrupados. Sin embargo, las definiciones de índice y vista permanecen en los metadatos y se pueden volver a crear regenerando el índice o índices.

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

  • Volver a generar el índice agrupado en una vista no habilita automáticamente los índices no agrupados en la vista.

  • Los índices no agrupados deben habilitarse de forma manual volviéndolos a generar después de volver a generar el índice agrupado.

Realizar operaciones de índice en línea en índices deshabilitados

Se puede volver a generar un índice no agrupado deshabilitado en línea cuando la tabla no tenga un índice agrupado deshabilitado. Sin embargo, siempre debe volver a generar un índice agrupado deshabilitado sin conexión si utiliza la instrucción ALTER INDEX REBUILD o CREATE INDEX WITH DROP_EXISTING. Para obtener más información acerca de las operaciones de índices en línea, vea Realizar operaciones de índices en línea.

Estadísticas sobre índices deshabilitados

Las restricciones siguientes se aplican a las estadísticas de índice cuando el índice está deshabilitado:

  • La instrucción CREATE STATISTICS no se puede ejecutar correctamente en una tabla que tenga un índice agrupado 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:

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

  • sp_autostats genera un error cuando la tabla especificada tiene un índice agrupado deshabilitado.

  • sp_updatestats no actualiza las estadísticas de los índices agrupados deshabilitados.

  • sp_createstats crea estadísticas de las columnas que pueden ser las columnas iniciales de un índice deshabilitado. Cuando se especifica indexonly, las estadísticas no se crean en una columna de un índice deshabilitado a no ser que esa columna también se utilice en otro índice habilitado.

Comandos DBCC

Si un índice agrupado está deshabilitado, DBCC CHECKDB no puede devolver información acerca de la tabla subyacente. En lugar de esto, la instrucción informa de que el índice agrupado está deshabilitado. No se puede utilizar DBCC INDEXDEFRAG para desfragmentar un índice deshabilitado. La instrucción genera un mensaje de error. Puede utilizar DBCC DBREINDEX para volver a generar un índice deshabilitado.

Ver el estado de un índice deshabilitado

Cuando un índice o una restricción PRIMARY KEY o UNIQUE está deshabilitado, se muestra un mensaje de advertencia que enumera todas las restricciones FOREIGN KEY o CHECK y los índices afectados. Además, puede ver el estado deshabilitado de un índice en la vista de catálogo sys.indexes o mediante la función INDEXPROPERTY. Puede ver el estado deshabilitado de las restricciones FOREIGN KEY y CHECK en las vistas de catálogo sys.foreign_keys y sys.check_constraints, respectivamente. Para obtener más información, vea Ver información de índice.

Ejemplos

En el ejemplo siguiente se deshabilita un índice no agrupado en la tabla Employee.

USE AdventureWorks;
GO
ALTER INDEX IX_Employee_ManagerID 
ON HumanResources.Employee DISABLE;