table (Transact-SQL)

Artículo
03/07/2014

Es un tipo de datos especial que se puede utilizar para almacenar un conjunto de resultados para procesar en otro momento. table se utiliza sobre todo para el almacenamiento temporal de un conjunto de filas vueltas como el conjunto de resultados de una función con valores de tabla. Las funciones y las variables se pueden declarar como del tipo table. Las variables table se pueden utilizar en funciones, procedimientos almacenados y lotes. Para declarar variables de tipo table, utilice DECLARE @local\_variable.

Se aplica a: SQL Server (SQL Server 2008 a versión actual), Windows Azure SQL Database (Versión inicial a versión actual).

Icono de vínculo a temas 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 ) 
     }

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 obtener más información acerca de la sintaxis, vea CREATE TABLE (SQL Server), CREATE FUNCTION (Transact-SQL) y DECLARE @local\_variable (Transact-SQL).
collation_definition
Es la intercalación de la columna que consiste en 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 generales

Se puede hacer referencia a las variables table por nombre en la cláusula FROM de un lote, según se muestra en el siguiente ejemplo:

SELECT Employee_ID, Department_ID FROM @MyTableVar;

Fuera de una cláusula FROM, se debe hacer referencia a las variables table mediante un alias, según se muestra en el siguiente 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 las 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. Dicho ámbito es la función, el procedimiento almacenado o el lote en que se declara.

Dentro de su ámbito, la variable table se puede utilizar 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. Como excepción, table no puede utilizarse en la siguiente instrucción:
```
SELECT select_list INTO table_variable;
```
Las variables table se limpian automáticamente al final de la función, procedimiento almacenado o lote en que están definidas.
Las variables table usadas en procedimientos almacenados causan menos recompilaciones de procedimientos almacenados que cuando se usan tablas temporales, cuando no hay opciones basadas en costo que afecten al rendimiento.
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 si no disponen de estadísticas de distribución, no desencadenarán recompilaciones. Por tanto, en muchos casos, el optimizador generará un programa de consultas basándose en que la variable table 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. Por otra parte, para las consultas que se unen a la variable table con otras tablas, puede utilizar la sugerencia RECOMPILE, que hará que el optimizador use la cardinalidad correcta para la variable table.

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 opciones basadas en costos para lograr un plan de consultas eficaz. Se prefieren las tablas temporales cuando se requieren opciones basadas en costos. Esto 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. En estas situaciones, puede optar por utilizar tablas temporales. Para obtener más información, vea CREATE TABLE (SQL Server). Las consultas que leen variables table sin modificarlas pueden generarse en paralelo.

En las variables table no se pueden crear índices de forma explícita; en estas variables table tampoco se conserva ninguna estadística. En determinados casos, el rendimiento puede mejorar si se utilizan tablas temporales, las cuales admiten índices y estadísticas. Para obtener más información acerca de las tablas temporales, vea CREATE TABLE (SQL Server).

Las restricciones CHECK, los valores DEFAULT y las columnas calculadas de la declaración del tipo table no pueden llamar a funciones definidas por el usuario.

No se permite la operación de asignación entre variables table.

Las variables table no se ven afectadas por las operaciones de reversión de transacciones debido a que tienen un ámbito limitado y no forman parte de la base de datos persistente.

Las variables de tabla no se pueden modificar una vez creadas.

Ejemplos

A.Declarar una variable de tipo table

En el ejemplo siguiente se 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. Tenga en cuenta que los resultados de la columna INSERTED.ModifiedDate son diferentes de los valores de la columna ModifiedDate de la tabla Employee . Esto se debe a que el desencadenador AFTER UPDATE, que actualiza el valor de ModifiedDate a 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 obtener más información, vea OUTPUT (cláusula de 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);

Vea también

Referencia

COLLATE (Transact-SQL)

CREATE FUNCTION (Transact-SQL)

CREATE TABLE (SQL Server)

DECLARE @local\_variable (Transact-SQL)

Sugerencias de consulta (Transact-SQL)

Conceptos

Funciones definidas por el usuario

Usar parámetros con valores de tabla (motor de base de datos)

Compartir a través de