table (Transact-SQL)

Se aplica a:yesSQL Server (todas las versiones compatibles) YesAzure SQL Database YesAzure SQL Managed Instance

Es un tipo de datos 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.

Se aplica a: SQL Server (SQL Server 2008 y versiones posteriores) y Azure SQL Database.

Topic link iconConvenciones 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 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
Es 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  
JOIN Employee on (m.EmployeeID =Employee.EmployeeID AND  
   m.DepartmentID = Employee.DepartmentID);  

Las variables table proporcionan las siguientes ventajas 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 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.

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 AdventureWorks2012;  
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. Crear 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 AdventureWorks2012;  
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   
    JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID  
    JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID  
    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)