¿Por qué usar la cláusula INCLUDE al crear un índice?

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

Imagine que necesita consultar el ID de un empleado, el ID de 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 de un departamento determinado, ahora tiene que hacer "bookmark lookup" para obtener el registro completo del empleado real, solo para obtener la columna lastname. Que puede ser bastante caro en términos de rendimiento, si usted encuentra una gran cantidad de 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 hoja del índice no agrupado. Con solo buscar en el índice no agrupado y encontrar a sus empleados para un departamento determinado, tiene toda la información necesaria, y la búsqueda de marcadores para cada empleado que se encuentra en el índice ya no es necesaria> > ahorra mucho tiempo.

Obviamente, no puede incluir todas las columnas en todos los índices no agrupados, pero si tiene consultas que faltan solo una o dos columnas para ser "cubiertas" (y que se usan mucho), puede ser muy útil incluirlas en un índice no agrupado adecuado.

Las columnas de índice básico se ordenan, pero las columnas incluidas no se ordenan. Esto ahorra recursos en el mantenimiento del índice, al 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 clasificar con datos que no sean de búsqueda. Definitivamente ayuda a reducir la cantidad de clasificación y fragmentación en el índice mantenimiento.

Esta discusión se está perdiendo el punto importante: La pregunta no es si las "columnas sin clave" son mejores para incluir como index-columns o como included-columns.

La pregunta es qué tan caro es usar el mecanismo include para incluir columnas que no son realmente necesarias en index? (normalmente no forma parte de las cláusulas where, pero a menudo se incluyen en selects). Así que su dilema es siempre:

1. Utilice el índice en id1, id2 ... idN solo o
2. Utilice el índice en id1, id2 ... idN más incluir col1, col2 ... colN

Donde: id1, id2 ... idN son columnas a menudo utilizadas en restricciones y col1, col2 ... Los colN son columnas a menudo seleccionadas, pero típicamente 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 mantener ya que el índice debe actualizarse y ordenados incluso cuando las "claves" no han cambiado).

Así que utilice la opción 1 o 2?

Respuesta: Si su tabla rara vez se actualiza-en su mayoría insertada en / eliminada de - entonces es relativamente barato usar el mecanismo include para incluir algunas "columnas calientes" (que se usan a menudo en selects - pero no se usan a menudo en restricciones) ya que las inserciones / eliminaciones requieren que el índice se actualice / clasifique de todos modos y, por lo tanto, poca sobrecarga adicional se asocia con el almacenamiento de algunas columnas adicionales mientras que ya está actualizando 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 incluidas-las columnas se actualizan a menudo (sin la tecla index- - las columnas se actualizan) - o - si son tantas que el índice se acerca a una copia de su tabla - ¡use la opción 1 que sugeriría! Además, si agregar ciertas columnas de inclusión resulta no hacer ninguna diferencia de rendimiento, es posible que desee omitir el idea de agregarlos:) Verifique que son útiles!

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

Observe que si una columna - que se agrega como included - columna de índice-se usa en la restricción : Siempre y cuando el índice como tal se puede utilizar (basado en la restricción contra index-key-columns) - entonces SQL Server está haciendo coincidir la restricción de columna con el índice (leaf-node-values) en lugar de ir por el camino caro alrededor de la propia tabla.

Las razones (incluyendo los datos en el nivel de hoja del índice) han sido bien explicadas. La razón por la que da dos sacudidas sobre esto, es que cuando ejecuta su consulta, si no tiene las columnas adicionales incluidas (nueva característica en SQL 2005), SQL Server tiene que ir al índice agrupado para obtener las columnas adicionales, lo que lleva más tiempo y agrega más carga al servicio de SQL Server, los discos y la memoria (caché de búfer para ser específico) a medida que se cargan memoria, potencialmente empujando otros datos más a menudo necesarios fuera de la 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 clave de índice, como varchar(max).

Esto le permite incluir dichas columnas en un índice de cobertura. Recientemente tuve que hacer esto para proporcionar una consulta generada por NHibernate, que tenía muchas columnas en la SELECCIÓN, con un índice útil.

Hay un límite para el tamaño total de todas las columnas alineadas en la definición del índice. Dicho esto, sin embargo, nunca he tenido 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 ha incluido columnas, ya que no tienen que definirse en ningún orden en particular. Piense en es como un índice dentro del índice. Un ejemplo sería el storeId (donde storeId es baja selectividad, lo que significa que cada tienda está asociada con una gran cantidad de clientes) y luego datos demográficos del cliente (apellido, nombre, DOB): Si solo inserta esas columnas en este orden (storeId, LastName, FirstName, DOB), solo puede buscar de manera eficiente los clientes para los que conoce storeId y LastName.

Por otro lado, definir el índice en storeId e incluir las columnas LastName, FirstName, DOB le permitiría en esencia hacer dos seeks - index predicate en storeId y luego buscar predicate en cualquiera de las columnas incluidas. Esto te dejaría cubre todas las posibles permutaciones de búsqueda, siempre y cuando comience con storeId.