表 (Transact-SQL)

语法

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

参数

• table_type_definition
  与在 CREATE TABLE 中定义表时所用的信息子集相同的信息子集。表声明包括列定义、名称、数据类型和约束。允许的约束类型仅为 PRIMARY KEY、UNIQUE KEY 和 NULL。

  有关语法的详细信息，请参阅 CREATE TABLE (Transact-SQL)、CREATE FUNCTION (Transact-SQL) 和 DECLARE @local_variable (Transact-SQL)。

• collation_definition
  由 Microsoft Windows 区域设置和比较样式、Windows 区域设置和二进制表示法或 Microsoft SQL Server 排序规则组成的列的排序规则。如果未指定 collation_definition，则此列将继承当前数据库的排序规则。另外，如果将此列定义为公共语言运行时 (CLR) 用户定义类型，则它将继承用户定义类型的排序规则。

最佳做法

不要使用表变量来存储大量数据（超过 100 行）。在表变量包含大量数据时，计划选择可能不是最佳的或稳定的。考虑重新撰写此类查询以便使用临时表或使用 USE PLAN 查询提示来确保优化器使用适合于您的情况的现有查询计划。

一般备注

可以在批处理的 FROM 子句中按名称引用 table 变量，如下例所示：

SELECT Employee_ID, Department_ID FROM @MyTableVar;

在 FROM 子句外，必须使用别名来引用 table 变量，如下例所示：

SELECT EmployeeID, DepartmentID 
FROM @MyTableVar m
JOIN Employee on (m.EmployeeID =Employee.EmployeeID AND
   m.DepartmentID = Employee.DepartmentID);

对于具有不更改的查询计划的小规模查询以及在主要考虑重新编译时，table 变量提供以下好处：

• table 变量的行为类似于局部变量，有明确定义的作用域。这就是在其中声明该变量的函数、存储过程或批处理。

  在其作用域内，table 变量可像常规表那样使用。该变量可应用于 SELECT、INSERT、UPDATE 和 DELETE 语句中用到表或表的表达式的任何地方。但是，table 不能用于以下语句中：

  SELECT select_list INTO table_variable;
  

  在定义 table 变量的函数、存储过程或批处理结束时，会自动清除此变量。

• 在存储过程中使用 table 变量与使用临时表相比，减少了存储过程的重新编译量，并且没有影响性能的基于成本的选择。

• 涉及 table 变量的事务只在 table 变量更新期间存在。因此减少了 table 变量对锁定和记录资源的需求。

限制和局限

在 SQL Server 优化器的基于成本的原因模型中，不支持 table 变量。因此，在需要基于成本的选择来实现高效的查询计划时，不应使用这些变量。在需要基于成本的选择时，临时表是首选。这通常包含具有联接、并行度决策和索引选择选项的查询。

修改 table 变量的查询不会生成并行查询执行计划。修改特大型 table 变量或复杂查询中的 table 变量时，可能会影响性能。在这种情况下，请考虑改用临时表。有关详细信息，请参阅 CREATE TABLE (Transact-SQL)。还可以并行执行读取 table 变量而不对变量进行修改的查询。

不能显式创建 table 变量的索引，也不保留 table 变量的任何统计信息。在某些情况下，可以通过改用支持索引和统计信息的临时表来改善性能。有关临时表的详细信息，请参阅 CREATE TABLE (Transact-SQL)。

table 类型声明中的 CHECK 约束、DEFAULT 值和计算列不能调用用户定义函数。

不支持在 table 变量之间进行赋值操作。

由于 table 变量作用域有限，并且不是持久数据库的一部分，因而不受事务回滚的影响。

示例

A. 声明一个表类型的变量

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. 创建内联表值函数

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

请参阅

参考

概念