临时表-表变量-CTE Tempdb使用

SET STATISTICS IO ON

--查看临时表Tempdb使用结果
SELECT '第一次__临时表' AS 'FID',*
FROM sys.dm_db_session_space_usage WITH(NOLOCK)
WHERE session_id =@@SPID

GO

CREATE TABLE #tempTest1
(
    FAddTime DATETIME,
    FGuid UNIQUEIDENTIFIER,
    FSessionID INT
)

INSERT INTO #tempTest1 (FAddTime,FGuid,FSessionID)
VALUES (GETDATE(),NEWID(),@@SPID)

GO


--插入一条数据到临时后
SELECT '第二次__临时表' AS 'FID',*
FROM sys.dm_db_session_space_usage WITH(NOLOCK)
WHERE session_id =@@SPID

GO

DROP TABLE #tempTest1
GO


--删除临时表后
SELECT '第三次__临时表' AS 'FID',*
FROM sys.dm_db_session_space_usage WITH(NOLOCK)
WHERE session_id =@@SPID

GO

SET STATISTICS IO OFF

(1 行受影响)
表 '#tempTest1__________________________________________________________________________________________________________00000000001D'。扫描计数 0，逻辑读取 1 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(1 行受影响)

(1 行受影响)

(1 行受影响)

 

 

SET STATISTICS IO ON

SELECT '第一次__表变量' AS 'FID',*
FROM sys.dm_db_session_space_usage WITH(NOLOCK)
WHERE session_id =@@SPID

GO


DECLARE @tTest2 TABLE
(
    FAddTime DATETIME,
    FGuid UNIQUEIDENTIFIER,
    FSessionID INT
)

INSERT INTO @tTest2 (FAddTime,FGuid,FSessionID)
VALUES (GETDATE(),NEWID(),@@SPID)

GO

--插入一条数据到表变量后
SELECT '第二次__表变量' AS 'FID',*
FROM sys.dm_db_session_space_usage WITH(NOLOCK)
WHERE session_id =@@SPID

GO

SELECT '第三次__表变量' AS 'FID',*
FROM sys.dm_db_session_space_usage WITH(NOLOCK)
WHERE session_id =@@SPID

GO

SET STATISTICS IO OFF

(1 行受影响)
表 '#B7BD05BA'。扫描计数 0，逻辑读取 1 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(1 行受影响)

(1 行受影响)

(1 行受影响)

 

 

SET STATISTICS IO ON

SELECT '第一次__CTE' AS 'FID',*
FROM sys.dm_db_session_space_usage WITH(NOLOCK)
WHERE session_id =@@SPID

GO


;WITH cteTest3(FAddTime,FGuid,FSessionID)
AS
(
    SELECT GETDATE(),NEWID(),@@SPID
)

SELECT '第二次__CTE' AS 'FID',*
FROM sys.dm_db_session_space_usage WITH(NOLOCK)
WHERE session_id =@@SPID

GO


SELECT '第三次__CTE' AS 'FID',*
FROM sys.dm_db_session_space_usage WITH(NOLOCK)
WHERE session_id =@@SPID

GO

SET STATISTICS IO OFF

(1 行受影响)

(1 行受影响)

(1 行受影响)

 

总结：

表变量和临时表一样，都是存储在Tempdb中的。
表变量是为了处理临时表造成的执行计划频繁重新编译而引进的新功能。（解决临时表造成重编译的问题）

以上测试临时表和表变量是否使用了Tempdb的存储空间。
如果有，就说明临时表和表变量会产生IO。

可以使用 sys.dm_db_session_space_usage 获取当前会话使用Tempdb的空间数量。
user_objects_alloc_page_count(0=,1=使用了Tempdb的存储空间),
user_objects_dealloc_page_count(表示回收的用户空间的数据页个数)。

测试临时表时，首先执行了查询 sys.dm_db_session_space_usage，
user_objects_alloc_page_count字段对应的值是0，
然后创建一个临时表，并插入一条数据。user_objects_alloc_page_count字段的值是1，说明使用可1个数据页。

测试表变量时，表变量确实也使用了Tempdb作为存储。（user_objects_alloc_page_count = 1）。
user_objects_dealloc_page_count字段的值是1，说明表变量已经被回收了。

临时表和表变量的区别之一是作用域不同。
表变量是批处理级的，当批处理结束后，表变量就会回收，
临时表是会话级的，只能显式地删除或者会话关闭后才会回收。