SQLServer进阶（11）临时表、表变量序⾔
为什么需要临时表？
  临时表利⽤了数据库临时表空间，由数据库系统⾃动进⾏维护，因此节省了物理表空间。并且由于临时表空间⼀般利⽤虚拟内存，⼤⼤减少了硬盘的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 leid=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
derdate) AS orderyear,
SUM(OD.qty) AS qty
FROM Sales.Orders AS O
JOIN Sales.OrderDetails AS OD
derid = O.orderid
GROUP BY YEAR(orderdate);
deryear, Cur.qty AS curyearqty, Prv.qty AS prvyearqty
FROM@MyOrderTotalsByYear AS Cur
LEFT OUTER JOIN@MyOrderTotalsByYear AS Prv
deryear = deryear +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
derdate) AS orderyear,
SUM(OD.qty) AS qty
FROM Sales.Orders AS O
JOIN Sales.OrderDetails AS OD
derid = 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
derdate) AS orderyear,
SUM(OD.qty) AS qty
FROM Sales.Orders AS O
JOIN Sales.OrderDetails AS OD
derid = 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命令
tabletotal函数-- 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开销，因此，表变量的⽅式并不太适合数据量⼤且频繁查询的情况。
资料

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。