SQL Server进阶（十一）临时表、表变量

序言

为什么需要临时表？

　　临时表利用了数据库临时表空间，由数据库系统自动进行维护，因此节省了物理表空间。并且由于临时表空间一般利用虚拟内存，大大减少了硬盘的I/O次数，因此也提高了系统效率。
　　临时表在事务完毕或会话完毕数据库会自动清空，不必记得用完后删除数据。

临时表

什么是临时表

　　临时表属于会话级的，会话结束的时候，临时表被释放，其创建、使用、删除都和普通表一样，临时表空间一般利用虚拟内存，不必进行磁盘I/O，因此效率较高。

　　临时表有两种：普通临时表 (#TbName)和全局临时表(##TbName)

　　普通临时表属于创建该临时表的会话，会话结束时被释放，其他的会话不能使用

　　全局临时表属于所有的会话，在所有会话结束时被释放

　　适用场合：高并发的场合（操作频繁，查询又多）

本地临时表

　　适合开销昂贵结果集是个非常小的集合

　　本地临时表就是用户在创建表的时候添加了"#"前缀的表，其特点是根据数据库连接独立。只有创建本地临时表的数据库连接有表的访问权限，其它连接不能访问该表；

　　不同的数据库连接中，创建的本地临时表虽然"名字"相同，但是这些表之间相互并不存在任何关系；在SQLSERVER中，通过特别的命名机制保证本地临时表在数据库连接上的独立性，意思是你可以在不同的连接里使用相同的本地临时表名称。

--创建临时表
    create table #MyUserInfo
    (
    　　id int primary key identity(1,1),
    　　username nvarchar(20)
    )
--使用临时表
    select * from #MyUserInfo

--释放资源
    drop table #MyUserInfo

--临时表的常用方式，把用户表的数据存入一个临时表（#MyUserInfo）中
    select * into #MyUserInfo from Tb_UserInfo
    select * from #MyUserInfo  

--全局临时表，用法和普通临时表一样，用##TbName标识(开发中尽量不要自己创建，其他人也可能创建一个相同的全局临时表造成冲突)
    select into ##myUserInfoG from Tb_UserInfo
    drop table ##myUserInfoG

View Code

全局临时表

　　全局临时表是用户在创建表的时候添加"##"前缀的表，其特点是所以数据库连接均可使用该全局临时表，当所有引用该临时表的数据库连接断开后自动删除。
　　全局临时表相比本地临时表，命名上就需要注意了，与本地临时表不同的是，全局临时表名不能重复。

CREATE TABLE ##Temp
(
    id int,
    customer_name nvarchar(50),
    age int
)

INSERT INTO ##Temp VALUES(1,'老王',20),(2,'老张',30),(3,'老李',25)

View Code

临时表创建索引

select * into #HRU  from v_hruserinfo
CREATE UNIQUE INDEX TMPUNIQUEHRU ON #HRU (employeenumber) 
CREATE  INDEX TMPHRU ON #HRU (DepartmentId)

View Code

临时表用途？

　　临时表的优化一般使用在子查询较多的情况下，也称为嵌套查询。我们写如下子查询：

SELECT * FROM sales.Temp_Salesorder
WHERE SalesOrderDetailID IN 
(SELECT SalesOrderDetailID FROM sales.SalesOrderDetail
WHERE UnitPrice IN
(SELECT UnitPrice FROM sales.SalesOrderDetail WHERE UnitPrice>0)
)

　　我们用临时表重新来看下执行情况如何，我们将第一二层的查询结果插入到#temp中，然后从临时表中查询结果。

SELECT SalesOrderDetailID INTO #temp FROM sales.SalesOrderDetail
WHERE UnitPrice IN (SELECT UnitPrice FROM sales.SalesOrderDetail WHERE UnitPrice>0)

SELECT * FROM sales.Temp_Salesorder
WHERE SalesOrderDetailID IN 
(SELECT SalesOrderDetailID FROM #temp)

　　因此我们可以看出临时表在比较复杂的嵌套查询中是可以提高查询效率的。

临时表改善高并发

　　用临时表对高并发进行优化时，优化的原则是尽早释放表中的锁，如我们在对表进行连接查询时，会对这两种表都添加S锁，其他用户对这两张表进行增删改操作时，要等待查询完成。我们通过临时表实现优化，就是让用户在查询时通过临时表来查询，尽早释放原表的S锁。

--临时表改善高并发
select * into #userinfo from Tb_UserInfo
select * into #roleinfo from Tb_RoleInfo

--执行下边查询时，原userinfo表和roleinfo表的s锁已经被释放了
select username,rolename from #userinfo as u
join #roleinfo as r on u.roleid=r.rid

小结：

　　临时表不管是在SQL Server还是其他平台都有使用，其在查询优化方面可以极大的提高查询效率，而SQL Server平台的临时表相比其他平台更容易创建和使用，其优越性不言而喻。所以如果平时工作或学习过程中，临时表可以作为一个必备技能经常使用。

表变量

-- Table Variables
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

View Code

LAG函数

-- with the LAG function
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 orderyear, qty AS curyearqty,
  LAG(qty) OVER(ORDER BY orderyear) AS prvyearqty
FROM @MyOrderTotalsByYear;
GO

View Code

表类型

-- Table Types
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
);
GO

-- Use table type
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 OD.orderid = O.orderid
  GROUP BY YEAR(orderdate);

SELECT orderyear, qty FROM @MyOrderTotalsByYear;
GO

View Code

动态SQL

-- Simple example of EXEC
DECLARE @sql AS VARCHAR(100);
SET @sql = 'PRINT ''This message was printed by a dynamic SQL batch.'';';
EXEC(@sql);
GO

View Code

EXEC命令

-- Simple example using sp_executesql
DECLARE @sql AS NVARCHAR(100);

SET @sql = N'SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE orderid = @orderid;';

EXEC sys.sp_executesql
  @stmt = @sql,
  @params = N'@orderid AS INT',
  @orderid = 10248;
GO

View Code

使用动态SQL的PIVOT

-- Static PIVOT
SELECT *
FROM (SELECT shipperid, YEAR(orderdate) AS orderyear, freight
      FROM Sales.Orders) AS D
  PIVOT(SUM(freight) FOR orderyear IN([2006],[2007],[2008])) AS P;

View Code

例程

用户自定义函数

存储过程

触发器

错误处理

　　表变量创建的语法类似于临时表，区别就在于创建的时候，必须要为之命名。表变量是变量的一种，表变量也分为本地及全局的两种，本地表变量的名称都是以“@”为前缀，只有在本地当前的用户连接中才可以访问。全局的表变量的名称都是以“@@”为前缀，一般都是系统的全局变量，像我们常用到的，如@@Error代表错误的号，@@RowCount代表影响的行数。

DECLARE @News Table 
　　( 
　　News_id int NOT NULL, 
　　NewsTitle varchar(100), 
　　NewsContent varchar(2), 
　　NewsDateTime datetime 
　　)

临时表和表变量的选择

--表变量：  
  DECLARE @tb  table(id   int   identity(1,1), name   varchar(100))   
  INSERT @tb  

  SELECT id, name 
  FROM mytable 

  WHERE name like ‘zhang%’ 

--临时表： 
  SELECT name, address
  INTO #ta   FROM mytable  
  WHERE name like ‘zhang%’

　　临时表是利用了硬盘(tempdb数据库) ，表名变量是占用内存，因此小数据量当然是内存中的表变量更快。当大数据量时，就不能用表变量了，太耗内存了。大数据量时适合用临时表。

　　表变量缺省放在内存，速度快，所以在触发器，存储过程里如果数据量不大，应该用表变量。

　　临时表缺省使用硬盘，一般来说速度比较慢，那是不是就不用临时表呢？也不是，在数据量比较大的时候，如果使用表变量，会把内存耗尽，然后使用 TEMPDB的空间，这样主要还是使用硬盘空间，但同时把内存基本耗尽，增加了内存调入调出的机会，反而降低速度。这种情况建议先给TEMPDB一次分配合适的空间，然后使用临时表。

　　临时表相对而言表变量主要是多了I/O时间，但少了对内存资源的占用。数据量较大的时候，由于对内存资源的消耗较少，使用临时表比表变量有更好的性能。

　　建议：触发器、自定义函数用表变量；存储过程看情况，大部分用表变量；特殊的应用，大数据量的场合用临时表。

　　表变量有明确的作用域，在定义表变量的函数、存储过程或批处理结束时，会自动清除表变量。

　　在存储过程中使用表变量与使用临时表相比，减少了存储过程的重新编译量。

　　涉及表变量的事务只在表变量更新期间存在。这样就减少了表变量对锁定和记录资源的需求。

　　表变量需要事先知道表结构，普通临时表，只在当前会话中可用与表变量相同into一下就可以了,方便；全局临时表：可在多个会话中使用存在于temp中需显示的drop。（不知道表结构情况下临时表方便一些）

　　全局临时表的功能是表变量没法达到的。

　　表变量不必删除，也就不会有命名冲突，临时表特别是全局临时表用的时候必须解决命名冲突。

　　应避免频繁创建和删除临时表，减少系统表资源的消耗。

　　在新建临时表时，如果一次性插入数据量很大，那么可以使用select into代替create table，避免log，提高速度；如果数据量不大，为了缓和系统表的资源，建议先create table，然后insert。

　　如果临时表的数据量较大，需要建立索引，那么应该将创建临时表和建立索引的过程放在单独一个子存储过程中，这样才能保证系统能够很好的使用到该临时表的索引。

　　如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先truncate table，然后drop table，这样可以避免系统表的较长时间锁定。

　　慎用大的临时表与其他大表的连接查询和修改，减低系统表负担，因为这种操作会在一条语句中多次使用tempdb的系统表。

　　使用表变量主要需要考虑的就是应用程序对内存的压力，如果代码的运行实例很多，就要特别注意内存变量对内存的消耗。

　　我们对于较小的数据或者是通过计算出来的推荐使用表变量。

　　如果数据的结果比较大，在代码中用于临时计算，在选取的时候没有什么分组的聚合，就可以考虑使用表变量。

　　一般对于大的数据结果，或者因为统计出来的数据为了便于更好的优化，我们就推荐使用临时表，同时还可以创建索引，由于临时表是存放在Tempdb中，一般默认分配的空间很少，需要对tempdb进行调优，增大其存储的空间。

　　CTE和WITH AS短语结合使用提高SQL查询性能：

　　CET要比表变量效率高得多！

　　表变量实际上使用了临时表，从而增加了额外的I/O开销，因此，表变量的方式并不太适合数据量大且频繁查询的情况。

资料

https://blog.csdn.net/lishimin1012/article/details/54137787

https://www.cnblogs.com/Jessy/archive/2013/06/13/3133958.html