in

SQL Server – ¿Qué significan realmente los índices agrupados y no agrupados?

apple touch icon@2

En SQL Server, el almacenamiento orientado a filas, tanto los índices agrupados como los no agrupados, se organizan como árboles B.

ingrese la descripción de la imagen aquí

(Fuente de imagen)

La diferencia clave entre índices agrupados e índices no agrupados es que el nivel de hoja del índice agrupado es la mesa. Esto tiene dos implicaciones.

  1. Las filas de las páginas hoja de índice agrupadas siempre contienen alguna cosa para cada una de las columnas (no dispersas) de la tabla (ya sea el valor o un puntero al valor real).
  2. El índice agrupado es la copia principal de una tabla.

Los índices no agrupados también pueden hacer el punto 1 mediante el uso de INCLUDE cláusula (Desde SQL Server 2005) para incluir explícitamente todas las columnas que no son clave, pero son representaciones secundarias y siempre hay otra copia de los datos alrededor (la tabla en sí).

CREATE TABLE T
(
A INT,
B INT,
C INT,
D INT
)

CREATE UNIQUE CLUSTERED INDEX ci ON T(A, B)
CREATE UNIQUE NONCLUSTERED INDEX nci ON T(A, B) INCLUDE (C, D)

Los dos índices anteriores serán casi idénticos. Con las páginas de índice de nivel superior que contienen valores para las columnas clave A, B y las páginas de nivel de hoja que contienen A, B, C, D

Solo puede haber un índice agrupado por tabla, porque las filas de datos en sí mismas se pueden ordenar en un solo orden.

La cita anterior de los libros en línea de SQL Server causa mucha confusión

En mi opinión, estaría mucho mejor redactado como.

Solo puede haber un índice agrupado por tabla porque las filas de nivel de hoja del índice agrupado están las filas de la tabla.

La cita en línea del libro no es incorrecta, pero debe tener claro que la «clasificación» de los índices agrupados y no agrupados es lógica, no física. Si lee las páginas a nivel de hoja siguiendo la lista enlazada y lee las filas de la página en el orden de la matriz de ranuras, entonces leerá las filas del índice en orden ordenado, pero físicamente es posible que las páginas no estén ordenadas. La creencia común de que con un índice agrupado, las filas siempre se almacenan físicamente en el disco en el mismo orden que el índice. llave Es falso.

Esta sería una implementación absurda. Por ejemplo, si se inserta una fila en el medio de una tabla de 4 GB, SQL Server no tiene que copiar 2GB de datos en el archivo para dejar espacio para la fila recién insertada.

En cambio, se produce una división de página. Cada página en el nivel de hoja de índices agrupados y no agrupados tiene la dirección (File: Page) de la página siguiente y anterior en orden lógico de teclas. No es necesario que estas páginas sean contiguas ni estén en orden de clave.

por ejemplo, la cadena de páginas enlazadas podría ser 1:2000 <-> 1:157 <-> 1:7053

Cuando ocurre una división de página, se asigna una nueva página desde cualquier lugar del grupo de archivos (desde una extensión mixta, para tablas pequeñas o una extensión uniforme no vacía que pertenece a ese objeto o una extensión uniforme recién asignada). Es posible que ni siquiera esté en el mismo archivo si el grupo de archivos contiene más de uno.

El grado en que el orden lógico y la contigüidad difieren de la versión física idealizada es el grado de fragmentación lógica.

En una base de datos recién creada con un solo archivo, ejecuté lo siguiente.

CREATE TABLE T
  (
     X TINYINT NOT NULL,
     Y CHAR(3000) NULL
  );

CREATE CLUSTERED INDEX ix
  ON T(X);

GO

--Insert 100 rows with values 1 - 100 in random order
DECLARE @C1 AS CURSOR,
        @X  AS INT

SET @C1 = CURSOR FAST_FORWARD
FOR SELECT number
    FROM   master..spt_values
    WHERE  type="P"
           AND number BETWEEN 1 AND 100
    ORDER  BY CRYPT_GEN_RANDOM(4)

OPEN @C1;

FETCH NEXT FROM @C1 INTO @X;

WHILE @@FETCH_STATUS = 0
  BEGIN
      INSERT INTO T (X)
      VALUES        (@X);

      FETCH NEXT FROM @C1 INTO @X;
  END

Luego verificó el diseño de la página con

SELECT page_id,
       X,
       geometry::Point(page_id, X, 0).STBuffer(1)
FROM   T
       CROSS APPLY sys.fn_PhysLocCracker( %% physloc %% )
ORDER  BY page_id

Los resultados estaban por todos lados. La primera fila en el orden de las claves (con el valor 1 – resaltado con una flecha debajo) estaba casi en la última página física.

ingrese la descripción de la imagen aquí

La fragmentación se puede reducir o eliminar reconstruyendo o reorganizando un índice para aumentar la correlación entre el orden lógico y el orden físico.

despues de correr

ALTER INDEX ix ON T REBUILD;

Tengo lo siguiente

ingrese la descripción de la imagen aquí

Si la tabla no tiene un índice agrupado, se denomina montón.

Los índices no agrupados se pueden crear en un montón o en un índice agrupado. Siempre contienen un localizador de filas de regreso a la tabla base. En el caso de un montón, este es un identificador de fila físico (rid) y consta de tres componentes (Archivo: Página: Ranura). En el caso de un índice agrupado, el localizador de filas es lógico (la clave de índice agrupado).

Para el último caso, si el índice no agrupado ya incluye naturalmente la (s) columna (s) de clave de CI como columnas de clave de NCI o INCLUDE-d columnas, entonces no se agrega nada. De lo contrario, las columnas de claves de CI que faltan se agregan silenciosamente al NCI.

SQL Server siempre garantiza que las columnas clave sean únicas para ambos tipos de índices. Sin embargo, el mecanismo en el que esto se aplica a los índices no declarados como únicos difiere entre los dos tipos de índices.

Los índices agrupados obtienen un uniquifier agregado para cualquier fila con valores clave que dupliquen una fila existente. Este es solo un número entero ascendente.

Para índices no agrupados no declarados como únicos, SQL Server agrega silenciosamente el localizador de filas a la clave de índice no agrupado. Esto se aplica a todas las filas, no solo a las que son realmente duplicadas.

La nomenclatura agrupada frente a no agrupada también se utiliza para los índices de almacenamiento de columnas. El papel Mejoras en los almacenes de columnas de SQL Server estados

Aunque los datos del almacén de columnas no están realmente «agrupados» en ninguna clave, decidimos mantener la convención tradicional de SQL Server de referirse al índice principal como un índice agrupado.

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

power bi

Tutorial de Power BI

HtFVnLCMvwkMTKPnBvreJN 1200 80

Guía de Némesis de la División 2: cómo conseguir el francotirador exótico y encontrar todas las piezas de Némesis