SQL SERVER临时表的使用

SQL SERVER支持3种临时表：

类别
本地临时表
全局临时表
表变量

1.本地临时表（#表名）：
只在当前链接中有效，当前链接断开时自动删除表

select * into #LSORDERS FROM ORDERS;
select * from #LSORDERS
--在一个查询窗口中创建临时表后可以检索到，但是如果关闭该查询新建查询，或者另外新建查询检索都会失败

2.全局临时表（##表名）：
对当前和其他链接都有效，当前和其他访问过他的链接都断开时自动删除表

CREATE TABLE DBO.##GLOBALS
(
ID SYSNAME NOT NULL PRIMARY KEY,
VAL SQL_VARIANT NOT NULL
);
INSERT INTO DBO.##GLOBALS(ID,VAL) VALUES(N'I',CAST(10 AS INT))
SELECT VAL FROM DBO.##GLOBALS WHERE ID=N'I';

3.表变量：
是一个物理存在的表，表变量仅对创建会话可见，只是对当前批可见。既不对调用堆栈内的内部批可见，也不对会话中后续批可见。

DECLARE @MyOrderTotalsByYear TABLE
(
  orderyear INT NOT NULL PRIMARY KEY,
  qty       INT NOT NULL
);
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 OD.orderid = O.orderid
  GROUP BY YEAR(orderdate);
SELECT Cur.orderyear, Cur.qty AS curyearqty, Prv.qty AS prvyearqty
FROM @MyOrderTotalsByYear AS Cur
  LEFT OUTER JOIN @MyOrderTotalsByYear AS Prv
    ON Cur.orderyear = Prv.orderyear + 1;
GO