Indexación de variables Tabla en Sql Server
Una de las principales características de las variables table frente a las tablas temporales, es que al ser declaradas en lugar de ser creadas no generan estadisticas ni histogramas al no generar tampoco un plan de ejecución como una tabla temporal o normal.
Su ventaja es que al ser un objeto de almacenaje simple, va mucho más rápido cuando se trata de almacenar pocos datos o consultas muy simples.
No obstante no crearse plan de ejecución sí que admite la declaración de constraintsnts unicas o de clave primaria que sirven a modo de indice y aumentan el rendimiento de la tabla.
Veamos un ejemplo:
Vamos a generar una variable tabla de un millon de registros llamando a una función:
IF OBJECT_ID('dbo.GetNums', 'IF') IS NOT NULL DROP FUNCTION dbo.GetNums;
GO
CREATE FUNCTION dbo.GetNums(@low AS BIGINT, @high AS BIGINT) RETURNS TABLE
AS
RETURN
WITH
L0 AS (SELECT c FROM (VALUES(1),(1)) AS D(c)),
L1 AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
L2 AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
L3 AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
L4 AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
L5 AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
FROM L5)
SELECT @low + rownum - 1 AS n
FROM Nums
ORDER BY rownum
OFFSET 0 ROWS FETCH FIRST @high - @low + 1 ROWS ONLY;
GO
seguidamente generamos una variable tabla que realice un filtro sobre los datos:
DECLARE @T1 AS TABLE
(
col1 INT NOT NULL,
col2 INT NOT NULL,
col3 DATE NOT NULL
);
INSERT INTO @T1(col1, col2, col3)
SELECT n, n * 2, CAST(SYSDATETIME() AS DATE)
FROM dbo.GetNums(1, 1000000);
SELECT col1, col2, col3
FROM @T1
WHERE col2 <= 5;
GO
y vemos su plan de ejcución:
y su temporizacion:
(2 filas afectadas)
Tabla '#B8056692'. Recuento de exámenes 1, lecturas lógicas 2476, lecturas físicas 0, lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.
(1 filas afectadas)
Tiempos de ejecución de SQL Server:
Tiempo de CPU = 62 ms, tiempo transcurrido = 128 ms.
Vemos que ha realizado un recorrido a toda la tabla /scan) y que ha tardado 128 ms.
Veamos que ocurre si declaramos una clave primaria y una clave unica para la col2 que se situa en el WHERE
DECLARE @T1 AS TABLE
(
col1 INT NOT NULL,
col2 INT NOT NULL,
col3 DATE NOT NULL,
PRIMARY KEY(col1),
UNIQUE(col2)
);
INSERT INTO @T1(col1, col2, col3)
SELECT n, n * 2, CAST(SYSDATETIME() AS DATE)
FROM dbo.GetNums(1, 1000000);
SELECT col1, col2, col3
FROM @T1
WHERE col2 <= 5;
GO
y veamos ahora su pla de ejccución:
y su temporización:
Tabla '#BBD5F776'. Recuento de exámenes 1, lecturas lógicas 2485, lecturas físicas 0, lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.
(1 filas afectadas)
Tiempos de ejecución de SQL Server:
Tiempo de CPU = 47 ms, tiempo transcurrido = 48 ms.
Vemos que se a realizado un examen de indice cluster en lugar de un table scan y que el tiempo de transcurrido a pasado de 128 ms a 48ms más de un 60% de mejora de rendimiento. No está nada mal!