¿Por qué usar la cláusula INCLUDE cuando se crea un índice?

Mientras estudiaba para el examen 70-433 noté que puede crear un índice de cobertura de una de las siguientes dos maneras.

CREATE INDEX idx1 ON MyTable (Col1, Col2, Col3) 

– O –

 CREATE INDEX idx1 ON MyTable (Col1) INCLUDE (Col2, Col3) 

La cláusula INCLUDE es nueva para mí. ¿Por qué lo usaría y qué pautas sugeriría para determinar si crear un índice de cobertura con o sin la cláusula INCLUDE?

Si la columna no está en WHERE/JOIN/GROUP BY/ORDER BY , pero solo en la lista de columnas en la cláusula SELECT .

La cláusula INCLUDE agrega los datos en el nivel más bajo / hoja, en lugar de en el árbol de índice. Esto hace que el índice sea más pequeño porque no es parte del árbol

INCLUDE columns no son INCLUDE columns clave en el índice, por lo que no están ordenadas. Esto significa que no es realmente útil para predicados, clasificación, etc. como mencioné anteriormente. Sin embargo, puede ser útil si tiene una búsqueda residual en algunas filas de la (s) columna (s) clave.

Otro artículo de MSDN con un ejemplo trabajado

Utilizaría el INCLUDE para agregar una o más columnas al nivel de la hoja de un índice no agrupado, si al hacerlo, puede “cubrir” sus consultas.

Imagine que necesita consultar el ID de un empleado, la ID del departamento y el apellido.

 SELECT EmployeeID, DepartmentID, LastName FROM Employee WHERE DepartmentID = 5 

Si tiene un índice no agrupado en (EmployeeID, DepartmentID), una vez que encuentre los empleados para un departamento determinado, ahora tiene que hacer “búsqueda de favoritos” para obtener el registro completo del empleado, solo para obtener la columna de apellido. . Eso puede ser bastante caro en términos de rendimiento, si encuentra muchos empleados.

Si hubiera incluido ese apellido en su índice:

 CREATE NONCLUSTERED INDEX NC_EmpDep ON Employee(EmployeeID, DepartmentID) INCLUDE (Lastname) 

entonces toda la información que necesita está disponible en el nivel de la hoja del índice no agrupado. Solo buscando en el índice no agrupado y encontrando a sus empleados para un departamento dado, usted tiene toda la información necesaria, y la búsqueda de favoritos para cada empleado que se encuentra en el índice ya no es necesaria -> ahorra mucho tiempo.

Obviamente, no puede incluir cada columna en cada índice no agrupado, pero si tiene consultas que faltan solo una o dos columnas para “cubrir” (y que se usan mucho), puede ser muy útil INCLUIR aquellas en un índice adecuado no agrupado.

Las columnas de índice básico están ordenadas, pero las columnas incluidas no están ordenadas. Esto ahorra recursos para mantener el índice, al mismo tiempo que permite proporcionar los datos en las columnas incluidas para cubrir una consulta. Por lo tanto, si desea cubrir consultas, puede colocar los criterios de búsqueda para ubicar filas en las columnas ordenadas del índice, pero luego “incluir” columnas adicionales sin ordenar con datos que no sean de búsqueda. Definitivamente ayuda a reducir la cantidad de clasificación y fragmentación en el mantenimiento del índice.

Esta discusión se está perdiendo en el punto importante: la pregunta no es si las “columnas sin clave” son mejores para incluir como columnas de índice o como columnas incluidas .

La pregunta es qué tan costoso es usar el mecanismo de inclusión para incluir columnas que realmente no son necesarias en el índice . (Por lo general, no forma parte de where-clauses, pero a menudo se incluye en selects). Entonces tu dilema es siempre:

  1. Use el índice en id1, id2 … idN solo o
  2. Utilice el índice en id1, id2 … idN plus include col1, col2 … colN

Donde: id1, id2 … idN son columnas que a menudo se usan en restricciones y col1, col2 … colN son columnas que a menudo se seleccionan, pero que normalmente no se usan en restricciones

(La opción de incluir todas estas columnas como parte de la clave de índice es siempre tonta (a menos que también se usen en restricciones), porque siempre sería más costoso de mantener, ya que el índice debe actualizarse y ordenarse incluso cuando el índice “claves” no han cambiado).

Entonces usa la opción 1 o 2?

Respuesta: si su tabla rara vez se actualiza, en su mayoría insertada / eliminada, entonces es relativamente barato usar el mecanismo de inclusión para incluir algunas “columnas calientes” (que a menudo se usan en selectas pero que no se usan con frecuencia en las restricciones) desde las inserciones / eliminaciones requieren que el índice se actualice / ordene de todos modos y, por lo tanto, se requiere una pequeña sobrecarga adicional para almacenar algunas columnas adicionales mientras se actualiza el índice. La sobrecarga es la memoria extra y la CPU utilizada para almacenar información redundante en el índice.

Si las columnas que considera agregar como columnas incluidas a menudo se actualizan (sin que se actualicen las columnas de clave- índice), o si es tan grande que el índice se acerca a una copia de la tabla, use la opción 1 ¡Sugeriría! Además, si agrega ciertas columnas de inclusión para que no se produzca ninguna diferencia en el rendimiento, es posible que desee omitir la idea de agregarlas 🙂 ¡Verifique que sean útiles!

El número promedio de filas por los mismos valores en las claves (id1, id2 … idN) también puede ser de cierta importancia.

Observe que si se usa una columna, que se agrega como una columna de índice incluida, siempre que el índice como tal se pueda usar (según la restricción contra las columnas de clave de índice), SQL Server se está haciendo coincidir. la restricción de la columna contra el índice (leaf-node-values) en lugar de ir por el camino caro alrededor de la mesa.

Las razones por las cuales (incluyendo los datos en el nivel de hoja del índice) han sido muy bien explicadas. La razón por la que da dos sacudidas al respecto, es que cuando ejecuta su consulta, si no tiene las columnas adicionales incluidas (nueva función en SQL 2005), el SQL Server debe ir al índice agrupado para obtener las columnas adicionales. lo cual lleva más tiempo y agrega más carga al servicio de SQL Server, a los discos y a la memoria (la memoria caché del búfer es específica) a medida que se cargan nuevas páginas de datos en la memoria, lo que puede alejar otros datos más necesarios de la memoria caché del búfer.

Una consideración adicional que no he visto en las respuestas ya dadas, es que las columnas incluidas pueden ser de tipos de datos que no están permitidos como columnas de clave de índice, como varchar (max).

Esto le permite incluir dichas columnas en un índice de cobertura. Hace poco tuve que hacer esto para proporcionar una consulta generada nHibernate, que tenía muchas columnas en SELECT, con un índice útil.

Hay un límite para el tamaño total de todas las columnas incluidas en la definición del índice. Sin embargo, dicho esto, nunca tuve que crear un índice tan amplio. Para mí, la mayor ventaja es el hecho de que puede cubrir más consultas con un índice que incluye columnas, ya que no tienen que definirse en un orden en particular. Pensar es como un índice dentro del índice. Un ejemplo sería el StoreID (donde StoreID es de baja selectividad, lo que significa que cada tienda está asociada a muchos clientes) y luego los datos demográficos del cliente (Apellido, Nombre, Fecha de nacimiento): si simplemente alinea esas columnas en este orden (StoreID, LastName , FirstName, DOB), solo puede buscar eficientemente clientes para los que conoce StoreID y LastName.

Por otro lado, definir el índice en StoreID e incluir las columnas Apellido, Nombre y Fecha de nacimiento le permitiría en esencia hacer dos predicados índice de búsqueda en StoreID y luego buscar predicado en cualquiera de las columnas incluidas. Esto le permitiría cubrir todas las permutaciones de búsqueda posibles siempre que comience con StoreID.