table (Transact-SQL)

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

table es un tipo de dato especial usado para almacenar un conjunto de resultados y procesarlo en otro momento. table se usa principalmente para almacenar temporalmente un conjunto de filas que se devuelven como el conjunto de resultados de la función con valores de tabla. Las funciones y las variables se pueden declarar como de tipo table. Las variables table se pueden usar en funciones, procedimientos almacenados y lotes. Para declarar variables de tipo table, use DECLARE @local_variable.

Convenciones de sintaxis de Transact-SQL

Sintaxis

table_type_definition ::=
    TABLE ( { <column_definition> | <table_constraint> } [ , ...n ] )

<column_definition> ::=
    column_name scalar_data_type
    [ COLLATE <collation_definition> ]
    [ [ DEFAULT constant_expression ] | IDENTITY [ ( seed , increment ) ] ]
    [ ROWGUIDCOL ]
    [ column_constraint ] [ ...n ]

 <column_constraint> ::=
    { [ NULL | NOT NULL ]
    | [ PRIMARY KEY | UNIQUE ]
    | CHECK ( logical_expression )
    }

<table_constraint> ::=
     { { PRIMARY KEY | UNIQUE } ( column_name [ , ...n ] )
     | CHECK ( logical_expression )
     }

Nota:

Para ver la sintaxis de Transact-SQL para SQL Server 2014 (12.x) y versiones anteriores, consulte Versiones anteriores de la documentación.

Argumentos

table_type_definition

Es el mismo subconjunto de información que se utiliza para definir una tabla en CREATE TABLE. La declaración de tabla incluye definiciones de columna, nombres, tipos de datos y restricciones. Solo se permiten los tipos de restricciones PRIMARY KEY, UNIQUE KEY y NULL.

Para más información sobre la sintaxis, vea CREATE TABLE (Transact-SQL), CREATE FUNCTION (Transact-SQL) y DECLARE @local_variable (Transact-SQL).

collation_definition

La intercalación de la columna que consta de una configuración regional de Microsoft Windows y un estilo de comparación, una configuración regional de Windows y la notación binaria o una intercalación de Microsoft SQL Server. Si no se especifica collation_definition, la columna hereda la intercalación de la base de datos actual. Si la columna se ha definido como un tipo definido por el usuario CLR (Common Language Runtime), la columna hereda la intercalación del tipo definido por el usuario.

Observaciones

Haga referencia a las variables table por el nombre en una cláusula FROM del lote, como se muestra en el ejemplo siguiente:

SELECT Employee_ID, Department_ID FROM @MyTableVar;

Fuera de una cláusula FROM, se debe hacer referencia a las variables table mediante un alias, como se muestra en este ejemplo:

SELECT EmployeeID,
    DepartmentID
FROM @MyTableVar m
INNER JOIN Employee
    ON m.EmployeeID = Employee.EmployeeID
    AND m.DepartmentID = Employee.DepartmentID;

Las variables de tabla proporcionan las siguientes ventajas respecto a las tablas temporales para consultas a pequeña escala que tienen planes de consulta invariables y cuando la recompilación es un tema importante:

• Una variable table se comporta como una variable local. Tiene un ámbito bien definido. Esta variable se puede usar en la función, el procedimiento almacenado o el lote en el que se declare.

  Dentro de su ámbito, la variable table se puede usar como una tabla normal. Puede aplicarse en cualquier lugar de las instrucciones SELECT, INSERT, UPDATE y DELETE donde se utilice una tabla o expresión de tabla. Sin embargo, table no puede usarse en la siguiente instrucción:

SELECT select_list INTO table_variable;

Las variables table se limpian automáticamente al final de la función, el procedimiento almacenado o el lote en que se definen.

• Cuando no hay elecciones basadas en el costo que afecten al rendimiento, las variables table usadas en procedimientos almacenados provocan menos recompilaciones de procedimientos almacenados que cuando se usan tablas temporales.

  Las variables de tabla están completamente aisladas del lote que las crea, por lo que no es necesario volver a resolverlas cuando se realiza una instrucción CREATE o ALTER, lo que podría suceder con una tabla temporal. Las tablas temporales necesitan esta "re-resolución" para que se pueda hacer referencia a la tabla desde un procedimiento almacenado anidado. Las variables de tabla evitan este paso por completo para que los procedimientos almacenados puedan usar el plan que ya está compilado, lo que ahorra recursos para procesar el procedimiento almacenado.

• Las transacciones con variables table existen solo mientras dura una actualización en la variable table. Por tanto, las variables table requieren menos recursos de registro y bloqueo.

Limitaciones y restricciones

Las variables table no tienen estadísticas de distribución. No desencadenan nuevas compilaciones. En muchos casos, el optimizador compilará un plan de consulta sobre el supuesto de que la variable de tabla no tiene filas. Por este motivo, las variables table deben usarse con precaución si se espera una gran cantidad de filas (más de 100). En estos casos, las tablas Temp pueden representar una mejor solución. Para las consultas que se unen a la variable de tabla con otras tablas, use la sugerencia RECOMPILE, que hará que el optimizador use la cardinalidad correcta para esta variable.

Las variables table no se admiten en el modelo de razonamiento basado en costos del optimizador de SQL Server. Por lo tanto, no se deben usar cuando se requieren elecciones basadas en el costo para lograr un plan de consultas eficaz. Se prefieren las tablas temporales cuando se requieren opciones basadas en costos. Este plan incluye normalmente consultas con uniones, decisiones de paralelismo y opciones de selección de índice.

Las consultas que modifican variables table no generan planes de ejecución de consultas en paralelo. El rendimiento puede verse afectado cuando se modifican variables table muy grandes o variables table en consultas complejas. Considere la posibilidad de usar tablas temporales en situaciones donde se modifican las variables table. Para más información, consulte CREATE TABLE (Transact-SQL). Las consultas que leen variables table sin modificarlas pueden generarse en paralelo.

Importante

El nivel de compatibilidad de la base de datos 150 mejora el rendimiento de las variables de tabla con la introducción de la compilación diferida de variables de tabla. Para obtener más información, consulte Compilación diferida de variables de tabla.

En las variables table no se pueden crear índices de forma explícita; en estas variables table tampoco se conserva ninguna estadística. A partir de SQL Server 2014 (12.x), se introdujo una sintaxis nueva que le permite crear determinado tipos de índice alineados con la definición de tabla. Con esta nueva sintaxis, puede crear índices en las variables de tabla como parte de la definición de tabla. En determinados casos, el rendimiento puede mejorar si en su lugar se utilizan tablas temporales, las que proporcionan estadísticas y compatibilidad total del índice. Para más información sobre la creación de índices alineados y las tablas temporales, consulteCREATE TABLE (Transact-SQL).

Las restricciones CHECK, los valores DEFAULT y las columnas calculadas de la declaración de tipos table no pueden llamar a funciones definidas por el usuario. No se permite la operación de asignación entre variables table. Como las variables table tienen ámbito limitado y no son parte de la base de datos persistente, las operaciones de reversión de transacciones no les afectan. Las variables table no se pueden modificar una vez creadas.

Las variables de tablas no se pueden usar en una instrucción INSERT EXEC o SELECT INTO.

No puede usar la instrucción EXEC ni el procedimiento almacenado sp_executesql para ejecutar una consulta de SQL Server dinámica que haga referencia a una variable de tabla, si la variable de tabla se creó fuera de la instrucción EXEC o del procedimiento almacenado sp_executesql. Dado que solo se puede hacer referencia a las variables de tabla en su ámbito local, una instrucción EXEC y un procedimiento almacenado sp_executesql estarían fuera del ámbito de la variable de tabla. Sin embargo, puede crear la variable de tabla y realizar todo el procesamiento dentro de la instrucción EXEC o el procedimiento almacenado sp_executesql porque el ámbito local de las variables de tabla se encuentra en la instrucción EXEC o en el procedimiento almacenado sp_executesql.

Una variable de tabla no es una estructura de solo memoria. Dado que una variable de tabla puede contener más datos de los que caben en la memoria, debe tener un lugar en el disco para almacenar datos. Las variables de tabla se crean en la base de datos tempdb, similar a las tablas temporales. Si la memoria está disponible, tanto las variables de tabla como las tablas temporales se crean y procesan mientras están en memoria (caché de datos).

Variables de tabla frente a tablas temporales

La elección entre variables de tabla y tablas temporales depende de estos factores:

• Número de filas que se insertan en la tabla.
• Número de recompilaciones de las que se guarda la consulta.
• El tipo de consultas y su dependencia de índices y estadísticas para el rendimiento.

En algunas situaciones, dividir un procedimiento almacenado con tablas temporales en procedimientos almacenados más pequeños para que la recompilación tenga lugar en unidades más pequeñas resulta útil.

En general, se usan variables de tabla siempre que sea posible, excepto cuando hay un volumen significativo de datos y se repite el uso de la tabla. En ese caso, puede crear índices en la tabla temporal para mejorar el rendimiento de las consultas. Sin embargo, cada escenario puede ser diferente. Microsoft recomienda probar si las variables de tabla son más útiles que las tablas temporales para una consulta determinada o un procedimiento almacenado.

Ejemplos

A. Declarar una variable de tipo table

El ejemplo siguiente crea una variable table que almacena los valores especificados en la cláusula OUTPUT de la instrucción UPDATE. Las dos instrucciones SELECT que le siguen devuelven los valores en @MyTableVar y los resultados de la operación de actualización en la tabla Employee. Los resultados de la columna INSERTED.ModifiedDate son diferentes de los valores de la columna ModifiedDate de la tabla Employee. Esta diferencia se debe a que el desencadenador AFTER UPDATE, que actualiza el valor de ModifiedDate con la fecha actual, se define en la tabla Employee. Sin embargo, las columnas que devuelve OUTPUT reflejan los datos anteriores a la activación de los desencadenadores. Para más información, vea Cláusula OUTPUT (Transact-SQL).

USE AdventureWorks2022;
GO
DECLARE @MyTableVar TABLE (
    EmpID INT NOT NULL,
    OldVacationHours INT,
    NewVacationHours INT,
    ModifiedDate DATETIME
);

UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25
OUTPUT INSERTED.BusinessEntityID,
    DELETED.VacationHours,
    INSERTED.VacationHours,
    INSERTED.ModifiedDate
INTO @MyTableVar;

--Display the result set of the table variable.
SELECT EmpID,
    OldVacationHours,
    NewVacationHours,
    ModifiedDate
FROM @MyTableVar;
GO

--Display the result set of the table.
--Note that ModifiedDate reflects the value generated by an
--AFTER UPDATE trigger.
SELECT TOP (10) BusinessEntityID,
    VacationHours,
    ModifiedDate
FROM HumanResources.Employee;
GO

B. Creación de una función alineada con valores de tabla

En el siguiente ejemplo se devuelve una función alineada con valores de tabla. Devuelve tres columnas ProductID, Name y el agregado de ventas totales anuales hasta la fecha por tienda como YTD Total para cada producto vendido a la tienda.

USE AdventureWorks2022;
GO
IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL
    DROP FUNCTION Sales.ufn_SalesByStore;
GO
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN
(
    SELECT P.ProductID,
        P.Name,
        SUM(SD.LineTotal) AS 'Total'
    FROM Production.Product AS P
    INNER JOIN Sales.SalesOrderDetail AS SD
        ON SD.ProductID = P.ProductID
    INNER JOIN Sales.SalesOrderHeader AS SH
        ON SH.SalesOrderID = SD.SalesOrderID
    INNER JOIN Sales.Customer AS C
        ON SH.CustomerID = C.CustomerID
    WHERE C.StoreID = @storeid
    GROUP BY P.ProductID,
        P.Name
);
GO

Para invocar la función, ejecute esta consulta.

SELECT * FROM Sales.ufn_SalesByStore (602);

Consulte también

• COLLATE (Transact-SQL)
• CREATE FUNCTION (Transact-SQL)
• Funciones definidas por el usuario
• CREATE TABLE (Transact-SQL)
• DECLARE @local_variable (Transact-SQL)
• Usar parámetros con valores de tabla (motor de base de datos)
• Sugerencias de consulta (Transact-SQL)