有在事务中加任何锁
临时表的更新会加锁，表变量更新不会
4）事务处理中的不同
if OBJECT_ID('tempdb..#T') is not null drop table #T
create table #T (s varchar(128))
declare @T table (s varchar(128))
insert into #T select '临时表：原值'
insert into @T select '表变量：原值'
BEGIN TRANSACTION
update #T set s='临时表：被更新'
update @T set s='表变量：被更新'
ROLLBACK TRANSACTION
www.2cto.com
select * from #T
select * from @T
结果发现，临时表得值被回滚，表变量并没有回滚。可以得出，表变量不受事务影响。
5）UDF中的不同
--表变量
CREATE FUNCTION dbo.example1 ( ) RETURNS INT AS
BEGIN
DECLARE @t1 TABLE (i INT)
INSERT @t1 VALUES(1)
INSERT @t1 VALUES(2)
UPDATE @t1 SET i = i + 5
DELETE @t1 WHERE i < 7
DECLARE @max INT
SELECT @max = MAX(i) FROM @t1
RETURN @max
END www.2cto.com
GO;
--临时表
CREATE FUNCTION dbo.example2 ( ) RETURNS INT
AS
BEGIN
CREATE TABLE #t1 (i INT)
INSERT #t1 VALUES(1)
INSERT #t1 VALUES(2)
UPDATE #t1 SET i = i + 5
DELETE #t1 WHERE i < 7
DECLARE @max INT
SELECT @max = MAX(i) FROM #t1
RETURN @max
END
GO
--物理表
CREATE FUNCTION dbo.example3 ( ) RETURNS INT AS
BEGIN
CREATE TABLE table1 ( id INT IDENTITY, name VARCHAR(32) )
INSERT table1(name) VALUES('aaron')
RETURN SCOPE_IDENTITY()
END
www.2cto.com
运行后，可以发现，函数内无法访问临时表，也无法创建实体表，同理也无法更新新增删除实体表的记录，但可以使用表变量来进行运算和操作
6) 性能对比
if OBJECT_ID('tempdb..#T') is not null drop table #T
create table #T (s char(1024))
declare @i int = 0
while @i<100000
begin
insert into #T select '临时表：原值'
set @i=@i+1
END
SELECT * FROM #T --3秒左右
--3秒
go
declare @V table (s char(1024))
declare @i int = 0
while @i<100000
begin
insert into @V select '表变量：原值'
set @i=@i+1
END
SELECT * FROM @V --3秒左右，和临时表几乎一样
www.2cto.com
在插入性能上，两者基本一致
7）关联操作上，性能的不同表现
--构造数据
dbcc dropcleanbuffers; --从缓冲池中删除所有清除缓冲区
use TestDB
if object_id('tempdb..#temp') is not null drop table #temp
SELECT IDENTITY(INT, 1, 1) as _rowid, a.ppt, a.GoodsId
INTO #temp
FROM GraspFZDRPWrite001.dbo.Goods a --goods表是一个有记录16049的数据表
GO
--用临时表
SELECT * FROM #temp
WHERE _rowid IN (SELECT max(_rowid) FROM #temp GROUP BY Ppt)
--很快
--用表变量
DECLARE @PDTEMP TABLE (_rowid int, ppt CHAR(1), goodsid INT)
INSERT INTO @PDTEMP SELECT * FROM #temp
SELECT * FROM @PDTEMP
WHERE _rowid IN (SELECT max(_rowid) FROM @PDTEMP GROUP BY ppt)
go
--相当慢，记录几乎出不来
www.2cto.com
为啥会出现以上的结果呢，我们跟踪执行计划可以发现，前者执行计划选择的哈希匹配，后者则是相当缓慢的嵌套循环。
分析原因，因为聚合操作会利用表的统计信息来聚合，表变量没有统计信息，
系统默认只能选择嵌套循环，而这导致严重的慢查询的主要原因
我们强制查询使用hash join连接(哈希匹配)
DECLARE @PDTEMP TABLE (_rowid int, ppt CHAR(1), goodsid INT)
INSERT INTO @PDTEMP SELECT * FROM #temp
SELECT * FROM @PDTEMP
WHERE _rowid IN (SELECT max(_rowid) FROM @PDTEMP GROUP BY ppt)
option(hash join)
这时速度跟用临时表一样，但不推荐这样使用，因为一旦这样强制使用，SQLSERVER的自动优化则不会起作用
8）把存储过程中返回的数据集插入到临时表 用于保存存储过程中返回的数据集
CREATE TABLE #sp_who3
( www.2cto.com
SPID INT,
Status VARCHAR(32) NULL
)
go
create procedure pWho AS
select 1 as spid, 'Tomas' as status
union all
select 1 as spid, 'Viviy' as status
go
insert #sp_who3 execute pWho
select * from #sp_who3