table (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 (Transact-SQL), 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.

Procedimientos recomendados

No utilice variables de tabla para almacenar una gran cantidad de datos (más de 100 filas). Las opciones del plan pueden no ser óptimas o estables cuando una variable de tabla contiene una gran cantidad de datos. Considere la posibilidad de reescribir estas consultas de modo que utilicen tablas temporales o de usar la sugerencia de consulta USE PLAN para asegurarse de que el optimizador usa un plan de consultas existente que funciona correctamente en su escenario.

Comentarios 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 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 (Transact-SQL). 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 (Transact-SQL).

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.

Ejemplos

A. Declarar una variable de tipo table

USE AdventureWorks2008R2;
GO
DECLARE @MyTableVar table(
    EmpID int NOT NULL,
    OldVacationHours int,
    NewVacationHours int,
    ModifiedDate datetime);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25,
    ModifiedDate = GETDATE() 
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.
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO

B. Crear una función insertada con valores de tabla

USE AdventureWorks2008R2;
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

SELECT * FROM Sales.ufn_SalesByStore (602);

Vea también

Referencia

Conceptos