table (Transact-SQL)table (Transact-SQL)

SE APLICA A: síSQL Server síAzure SQL Database noAzure Synapse Analytics (SQL DW) noAlmacenamiento de datos paralelos APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Es un tipo de datos especial usado para almacenar un conjunto de resultados y procesarlo en otro momento.Is a special data type used to store a result set for processing at a later time. 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.table is primarily used for temporarily storing a set of rows that are returned as the table-valued function result set. Las funciones y las variables se pueden declarar como de tipo table.Functions and variables can be declared to be of type table. Las variables table se pueden usar en funciones, procedimientos almacenados y lotes.table variables can be used in functions, stored procedures, and batches. Para declarar variables de tipo table, use DECLARE @local_variable.To declare variables of type table, use DECLARE @local_variable.

Se aplica a: SQL ServerSQL Server (de SQL Server 2008SQL Server 2008 hasta SQL ServerSQL Server), Base de datos SQL de AzureAzure SQL Database.Applies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 through SQL ServerSQL Server), Base de datos SQL de AzureAzure SQL Database.

Icono de vínculo de tema Convenciones de sintaxis de Transact-SQLTopic link icon Transact-SQL Syntax Conventions

SintaxisSyntax

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 )   
     }   

ArgumentosArguments

table_type_definitiontable_type_definition
Es el mismo subconjunto de información que se utiliza para definir una tabla en CREATE TABLE.Is the same subset of information that is used to define a table in CREATE TABLE. La declaración de tabla incluye definiciones de columna, nombres, tipos de datos y restricciones.The table declaration includes column definitions, names, data types, and constraints. Solo se permiten los tipos de restricciones PRIMARY KEY, UNIQUE KEY y NULL.The only constraint types allowed are PRIMARY KEY, UNIQUE KEY, and NULL.
Para saber más sobre la sintaxis, vea CREATE TABLE (Transact-SQL), CREATE FUNCTION (Transact-SQL) y DECLARE @local_variable (Transact-SQL).For more information about the syntax, see CREATE TABLE (Transact-SQL), CREATE FUNCTION (Transact-SQL), and DECLARE @local_variable (Transact-SQL).

collation_definitioncollation_definition
Es la intercalación de la columna que consta de una configuración regional de MicrosoftMicrosoft Windows y un estilo de comparación, una configuración regional de Windows y la notación binaria o una intercalación de MicrosoftMicrosoft SQL ServerSQL Server.Is the collation of the column that is made up of a MicrosoftMicrosoft Windows locale and a comparison style, a Windows locale, and the binary notation, or a MicrosoftMicrosoft SQL ServerSQL Server collation. Si no se especifica collation_definition, la columna hereda la intercalación de la base de datos actual.If collation_definition isn't specified, the column inherits the collation of the current database. 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.Or if the column is defined as a common language runtime (CLR) user-defined type, the column inherits the collation of the user-defined type.

NotasRemarks

Haga referencia a las variables table por el nombre en una cláusula FROM del lote, como se muestra en el ejemplo siguiente:table Reference variables by name in a batch's FROM clause, as shown the following example:

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:Outside a FROM clause, table variables must be referenced by using an alias, as shown in the following example:

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:table variables provide the following benefits for small-scale queries that have query plans that don't change and when recompilation concerns are dominant:

  • Una variable table se comporta como una variable local.A table variable behaves like a local variable. Tiene un ámbito bien definido.It has a well-defined scope. Esta variable es la función, el procedimiento almacenado o el lote en los que se declara.This variable is the function, stored procedure, or batch that it's declared in.
    Dentro de su ámbito, la variable table se puede usar como una tabla normal.Within its scope, a table variable can be used like a regular table. Puede aplicarse en cualquier lugar de las instrucciones SELECT, INSERT, UPDATE y DELETE donde se utilice una tabla o expresión de tabla.It may be applied anywhere a table or table expression is used in SELECT, INSERT, UPDATE, and DELETE statements. Sin embargo, table no puede usarse en la siguiente instrucción:However, table can't be used in the following statement:
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.table variables are automatically cleaned up at the end of the function, stored procedure, or batch in which they're defined.

  • 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.table variables that are used in stored procedures cause fewer stored procedure recompilations than when temporary tables are used when there are no cost-based choices that affect performance.
  • Las transacciones con variables table existen solo mientras dura una actualización en la variable table.Transactions involving table variables last only for the duration of an update on the table variable. Por tanto, las variables table requieren menos recursos de registro y bloqueo.As such, table variables require less locking and logging resources.

Limitaciones y restriccionesLimitations and restrictions

Las variables table no tienen estadísticas de distribución.Table variables don't have distribution statistics. No desencadenan nuevas compilaciones.They won't trigger recompiles. En muchos casos, el optimizador compilará un plan de consulta sobre el supuesto de que la variable de tabla no tiene filas.In many cases, the optimizer will build a query plan on the assumption that the table variable has no rows. Por este motivo, las variables table deben usarse con precaución si se espera una gran cantidad de filas (más de 100).For this reason, you should be cautious about using a table variable if you expect a larger number of rows (greater than 100). En estos casos, las tablas Temp pueden representar una mejor solución.Temp tables may be a better solution in this case. 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.For queries that join the table variable with other tables, use the RECOMPILE hint, which will cause the optimizer to use the correct cardinality for the table variable.

Las variables table no se admiten en el modelo de razonamiento basado en costos del optimizador de SQL ServerSQL Server.table variables aren't supported in the SQL ServerSQL Server optimizer's cost-based reasoning model. Por lo tanto, no se deben usar cuando se requieren elecciones basadas en el costo para lograr un plan de consultas eficaz.As such, they shouldn't be used when cost-based choices are required to achieve an efficient query plan. Se prefieren las tablas temporales cuando se requieren opciones basadas en costos.Temporary tables are preferred when cost-based choices are required. Este plan incluye normalmente consultas con uniones, decisiones de paralelismo y opciones de selección de índice.This plan typically includes queries with joins, parallelism decisions, and index selection choices.

Las consultas que modifican variables table no generan planes de ejecución de consultas en paralelo.Queries that modify table variables don't generate parallel query execution plans. El rendimiento puede verse afectado cuando se modifican variables table muy grandes o variables table en consultas complejas.Performance can be affected when large table variables, or table variables in complex queries, are modified. Considere la posibilidad de usar tablas temporales en situaciones donde se modifican las variables table.Consider using temporary tables instead in situations where table variables are modified. Para obtener más información, vea CREATE TABLE (Transact-SQL).For more information, see CREATE TABLE (Transact-SQL). Las consultas que leen variables table sin modificarlas pueden generarse en paralelo.Queries that read table variables without modifying them can still be parallelized.

En las variables table no se pueden crear índices de forma explícita; en estas variables table tampoco se conserva ninguna estadística.Indexes can't be created explicitly on table variables, and no statistics are kept on table variables. A partir de SQL Server 2014 (12.x)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.Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x), new syntax was introduced which allows you to create certain index types inline with the table definition. Con esta nueva sintaxis, puede crear índices en las variables de tabla como parte de la definición de tabla.Using this new syntax, you can create indexes on table variables as part of the table definition. 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.In some cases, performance may improve by using temporary tables instead, which provide full index support and statistics. Para más información sobre la creación de índices alineados y las tablas temporales, consulteCREATE TABLE (Transact-SQL).For more information about temporary tables and inline index creation, see CREATE 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.CHECK constraints, DEFAULT values, and computed columns in the table type declaration can't call user-defined functions.

No se permite la operación de asignación entre variables table.Assignment operation between table variables isn't supported.

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.Because table variables have limited scope and aren't part of the persistent database, transaction rollbacks don't affect them.

Las variables table no se pueden modificar una vez creadas.Table variables can't be altered after creation.

Compilación diferida de variables de tablaTable variable deferred compilation

La compilación diferida de variables de tabla mejora la calidad del plan y el rendimiento general de las consultas que hacen referencia a las variables de tabla.Table variable deferred compilation improves plan quality and overall performance for queries referencing table variables. Durante la optimización y la compilación del plan inicial, esta característica propagará las estimaciones de cardinalidad que se basan en los recuentos de filas de variables de tabla reales.During optimization and initial plan compilation, this feature will propagate cardinality estimates that are based on actual table variable row counts. Esta información exacta de recuento de filas se usará para optimizar las operaciones del plan de bajada.This exact row count information will then be used for optimizing downstream plan operations.

Nota

La compilación diferida de variables de tabla es una característica en versión preliminar pública en Base de datos SQL de AzureAzure SQL Database y SQL Server 2019 (15.x)SQL Server 2019 (15.x).Table variable deferred compilation is a public preview feature in Base de datos SQL de AzureAzure SQL Database and SQL Server 2019 (15.x)SQL Server 2019 (15.x).

Con la compilación aplazada variable de tabla, la compilación de una instrucción que hace referencia a una variable de tabla se aplaza hasta que la primera ejecución real de la instrucción.With table variable deferred compilation, compilation of a statement that references a table variable is deferred until the first actual execution of the statement. Este comportamiento de compilación diferida es idéntico al comportamiento de las tablas temporales.This deferred compilation behavior is identical to the behavior of temporary tables. Este cambio se traduce en el uso de la cardinalidad real en lugar de la estimación de una fila original.This change results in the use of actual cardinality instead of the original one-row guess.

Para habilitar la versión preliminar pública de la compilación diferida de variables de tabla, habilite el nivel 150 de compatibilidad de la base de datos a la que se conecta cuando ejecuta la consulta.To enable the public preview of table variable deferred compilation, enable database compatibility level 150 for the database you're connected to when the query runs.

La compilación diferida de variables de tabla no cambia ninguna otra característica de las variables de tabla.Table variable deferred compilation doesn't change any other characteristics of table variables. Por ejemplo, esta característica no agrega estadísticas de columna a las variables de tabla.For example, this feature doesn't add column statistics to table variables.

La compilación diferida de variables de tabla no aumenta la frecuencia de recompilación.Table variable deferred compilation doesn't increase recompilation frequency. En su lugar, se desplaza a donde se produce la compilación inicial.Rather, it shifts where the initial compilation occurs. El plan en caché resultante se genera en función del recuento de filas de variables de tabla de la compilación diferida.The resulting cached plan generates based on the initial deferred compilation table variable row count. Este plan se reutiliza en consultas sucesivas, yThe cached plan is reused by consecutive queries. hasta que se expulsa o se vuelve a compilar.It's reused until the plan is evicted or recompiled.

El recuento de filas de variables de tabla que se usa para la compilación inicial del plan representa un valor típico que podría ser diferente de una estimación del recuento de filas fijo.Table variable row count that is used for initial plan compilation represents a typical value might be different from a fixed row count guess. Si es diferente, las operaciones de bajada saldrán beneficiadas.If it's different, downstream operations will benefit. Si el recuento de filas de variables de tabla varía considerablemente entre ejecuciones, es posible que esta característica no mejore el rendimiento.Performance may not be improved by this feature if the table variable row count varies significantly across executions.

Deshabilitación de la compilación diferida de variables de tabla sin cambiar el nivel de compatibilidadDisabling table variable deferred compilation without changing the compatibility level

Deshabilite la compilación diferida de variables de tabla en el ámbito de base de datos o de instrucción mientras se mantiene el nivel 150 o posterior de compatibilidad de base de datos.Disable table variable deferred compilation at the database or statement scope while still maintaining database compatibility level 150 and higher. Para deshabilitar la compilación diferida de variables de tabla para todas las ejecuciones de consultas que se originan en la base de datos, ejecute el siguiente ejemplo en el contexto de la base de datos aplicable:To disable table variable deferred compilation for all query executions originating from the database, execute the following example within the context of the applicable database:

ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = OFF;

Para volver a habilitar la compilación diferida de variables de tabla para todas las ejecuciones de consultas que se originan en la base de datos, ejecute el siguiente ejemplo en el contexto de la base de datos aplicable:To re-enable table variable deferred compilation for all query executions originating from the database, execute the following example within the context of the applicable database:

ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = ON;

También puede deshabilitar la compilación diferida de variables de tabla para una consulta específica mediante la designación de DISABLE_DEFERRED_COMPILATION_TV como una sugerencia de consulta USE HINT.You can also disable table variable deferred compilation for a specific query by assigning DISABLE_DEFERRED_COMPILATION_TV as a USE HINT query hint. Por ejemplo:For example:

DECLARE @LINEITEMS TABLE 
    (L_OrderKey INT NOT NULL,
     L_Quantity INT NOT NULL
    );

INSERT @LINEITEMS
SELECT L_OrderKey, L_Quantity
FROM dbo.lineitem
WHERE L_Quantity = 5;

SELECT  O_OrderKey,
    O_CustKey,
    O_OrderStatus,
    L_QUANTITY
FROM    
    ORDERS,
    @LINEITEMS
WHERE   O_ORDERKEY  =   L_ORDERKEY
    AND O_OrderStatus = 'O'
OPTION (USE HINT('DISABLE_DEFERRED_COMPILATION_TV'));

EjemplosExamples

A.A. Declarar una variable de tipo tableDeclaring a variable of type table

El ejemplo siguiente crea una variable table que almacena los valores especificados en la cláusula OUTPUT de la instrucción UPDATE.The following example creates a table variable that stores the values specified in the OUTPUT clause of the UPDATE statement. 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.Two SELECT statements follow that return the values in @MyTableVar and the results of the update operation in the Employee table. Los resultados de la columna INSERTED.ModifiedDate son diferentes de los valores de la columna ModifiedDate de la tabla Employee.Results in the INSERTED.ModifiedDate column differ from the values in the ModifiedDate column in the Employee table. 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.This difference is because the AFTER UPDATE trigger, which updates the value of ModifiedDate to the current date, is defined on the Employee table. Sin embargo, las columnas que devuelve OUTPUT reflejan los datos anteriores a la activación de los desencadenadores.However, the columns returned from OUTPUT reflect the data before triggers are fired. Para más información, vea Cláusula OUTPUT (Transact-SQL).For more information, see OUTPUT Clause (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.B. Crear una función alineada con valores de tablaCreating an inline table-valued function

En el siguiente ejemplo se devuelve una función alineada con valores de tabla.The following example returns an inline table-valued function. 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.It returns three columns ProductID, Name, and the aggregate of year-to-date totals by store as YTD Total for each product sold to the store.

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.To invoke the function, run this query.

SELECT * FROM Sales.ufn_SalesByStore (602);  

Vea tambiénSee also

COLLATE (Transact-SQL)COLLATE (Transact-SQL)
CREATE FUNCTION (Transact-SQL)CREATE FUNCTION (Transact-SQL)
Funciones definidas por el usuarioUser-Defined Functions
CREATE TABLE (Transact-SQL)CREATE TABLE (Transact-SQL)
DECLARE @local_variable (Transact-SQL)DECLARE @local_variable (Transact-SQL)
Usar parámetros con valores de tabla (motor de base de datos)Use Table-Valued Parameters (Database Engine)
Sugerencias de consulta (Transact-SQL)Query Hints (Transact-SQL)