Crear un índice en SQL Server es una tarea moderadamente simple que mejora el rendimiento de la consulta. Podemos crear un índice agrupado así como un índice no agrupado que puede ser único o no único según el requisito.
Sin embargo, si ya hemos creado un índice en una tabla, debido a un cambio de requisitos, debemos agregar / eliminar una o más columnas a un índice existente. En este caso, podemos utilizar el DROP_EXISTING Opción sin escribir la línea de código adicional para eliminar primero el índice existente y luego volver a crearlo. (Este es un estilo de codificación anticuado: soltar si existe y crear una nueva).
Con DROP_EXISTING = ON, SQL Server eliminará y reconstruirá el índice existente agrupado / no agrupado con especificaciones de índice modificadas, manteniendo el nombre del índice tal como existe. Podemos agregar / eliminar columnas, modificar el orden de clasificación o cambiar un grupo de archivos también.
DROP_EXISTING está desactivado por defecto, y no funciona si está incluyendo esta opción al crear un nuevo índice por primera vez. DROP_EXISTING = ON en SQL Server solo funciona si ya tiene un índice con el nombre especificado en una tabla.
Con DROP_EXISTING, podemos hacer lo siguiente:
1. Se permite cambiar un índice no agrupado a un índice agrupado.
Con DROP_EXISTING, no podemos hacer lo siguiente:
1. No se permite cambiar un índice agrupado a ningún otro tipo de índice.
Ventajas de usar DROP_EXISTING = ON
Si estamos recreando un índice agrupado en una tabla, que ya tiene un índice no agrupado, usando DROP_EXISTING = ON en SQL Server, mientras que la recreación de un índice agrupado tiene los siguientes beneficios:
• Los índices no agrupados no se reconstruyen de nuevo. Si estamos recreando un índice agrupado con DROP_EXISTING = ON en SQL Server, sabe que la clave de agrupación no se ha cambiado en el índice no agrupado. Esto ahorra una cantidad significativa de tiempo. Cada índice no agrupado tiene una clave de agrupamiento que se refiere al índice agrupado. Si se reconstruye un índice agrupado, entonces SQL Server también tiene que modificar el índice no agrupado. Sin embargo, usar DROP_EXISTING = ON es beneficioso porque no necesitamos reconstruir los índices no agrupados nuevamente.
• Si usamos el método antiguo para eliminar y volver a crear un índice agrupado, hace que todos los índices no agrupados en esa tabla se reconstruyan nuevamente para que coincidan con la clave de agrupación. Esto se puede guardar usando DROP_EXISTING = ON con índice agrupado.
Sintaxis para la creación de índices con DROP_EXISTING
CREAR [Único] [Agrupado | No agrupado] INDEX Index_Name ON (Nombre / s de columna) WITH DROP_EXISTING = {ON | APAGADO}; Ejemplos
Crear una tabla de muestra, empleado.
SI OBJECT_ID ('Empleado', 'U') NO ES UNA TABLA DE GOTA NULA Empleado; CREAR TABLA Empleado (Id. LLA PRIMARIA INT, Nombre VARCHAR (50), Teléfono VARCHAR (10), Sexo CHAR (1), País VARCHAR (100), Salario DECIMAL (18,2)); 1. DROP_EXISTING = ON falla al crear un nuevo índice
En el siguiente ejemplo, la opción DROP_EXISTING = ON intenta encontrar un índice existente con NCI_Employee_TelphoneGender nombre, ya que no sale, la creación del índice falla con el siguiente mensaje de error.
CREAR ÍNDICE NO CLUSIFICADO NCI_Employee_TelphoneGender ON Empleado (teléfono, género) WITH (DROP_EXISTING = ON);
Mensaje 7999, Nivel 16, Estado 9, Línea 1
No se pudo encontrar ningún índice denominado 'NCI_Employee_TelphoneGender' para la tabla 'Empleado'.
Creando un índice
CREAR ÍNDICE NO CLUSIFICADO NCI_Employee_TelphoneGender ON Employee (Telephone, Gender);
Consulta para ver la lista de columnas en un índice
La siguiente consulta muestra que las columnas Teléfono y Género se han incluido en el índice No agrupado creado.
seleccione el nombre como ColumnListInIndex de sys.index_columns como ic internal join sys.columns c en ic.column_id = c.column_id y ic.object_id = c.object_id donde ic.object_id = (seleccione object_id de sys.indexes donde name = 'NCI_Employee_TelphonephoneGender' y type_desc = 'NONCLUSTERED') e ic.index_id = 2;
2. DROP_EXISTING = ON en SQL Server Works en el índice existente
En el ejemplo anterior, hemos creado un ÍNDICE, NCI_Employee_TelphoneGender En la columna Teléfono y género. Ahora, debido a un cambio en el requisito, debemos incluir una columna País también en el índice no agrupado existente. Podemos lograr esto usando la opción DROP_EXISTING = ON.
En el ejemplo a continuación, con DROP_EXISTING = ENCENDIDO, SQL Server primero eliminará el índice especificado y volverá a crear un índice con el mismo nombre y la columna de País incluida.
CREAR ÍNDICE NO CLUSIFICADO NCI_Employee_TelphoneGender ON Empleado (teléfono, género, país) CON (DROP_EXISTING = ON);
Antiguo método para escribir el código de arriba
El código anterior con DROP_EXISTING = ON es un reemplazo de un método antiguo para eliminar y volver a crear un índice. Si usamos el código a continuación para eliminar / recrear el índice agrupado, también se reconstruirán todos los índices no agrupados, lo que podría ser un gran éxito de rendimiento.
IF EXISTS (SELECT * FROM sys.indexes AS si WHERE si.name = 'NCI_Employee_TelphoneGender') ÍNDICE DE GOTA NCI_Employee_TelphoneGender ON Employee; GO CREATE INDEX NCI_Employee_TelphoneGender ON Employee (Teléfono, Género, País) GO
Resumen
Hemos visto cómo DROP_EXISTING = ON en SQL Server nos ayuda con el índice agrupado para mejorar el rendimiento con algunos ejemplos, y DROP_EXISTING = ON solo se puede usar con el índice creado existente para modificar sus especificaciones.