表变量:和使用局部表变量一样,表变量和tempdb数据库中也有对应的表作为其物理表示,而不是像通常所理解的那样,以为表变量只存在内存中。和局部临时表类似,表变量也只是对创建它的会话可见,但允许访问的范围更有限,它只对当前批处理乐见。
表类型:SQL Server 2008中引入了对表类型的支持。通过创建表类型,可以把表的定义保存到数据库中,以后在定义表变量、存储过程和用户定义的输入参数时,可以将表类型作为表的定义而重用。
定义表类型(TVP):
USE TSQLFundamentals2008;
IF TYPE_ID('dbo.OrderTotalsByYear') IS NOT NULL
DROP TYPE dbo.OrderTotalsByYear;
CREATE TYPE dbo.OrderTotalsByYear AS TABLE
(
orderyear INT NOT NULL PRIMARY KEY,
qty INT NOT NULL
);
使用表类型(TVP):
DECLARE @MyOrderTotalsByYear AS dbo.OrderTotalsByYear;
INSERT INTO @MyOrderTotalsByYear(orderyear,qty)
SELECT YEAR(O.orderdate) as orderyear,SUM(OD.qty) AS qty
FROM Sales.Orders AS O JOIN Sales.OrderDetails AS OD ON O.orderid=OD.orderid
GROUP BY YEAR(O.orderdate);
SELECT * FROM @MyOrderTotalsByYear;