局部临时表与全局临时表区别与示例

局部临时表与全局临时表区别与示例

1、局部临时表（#开头）只对当前连接有效，当前连接断开时自动删除。
2、全局临时表（##开头）对其它连接也有效，在当前连接和其他访问过它的连接都断开时自动删除。
3、不管局部临时表还是全局临时表，只要连接有访问权限，都可以用drop table #Tmp(或者drop table ##Tmp)来显式删除临时表。

示例：

主存储过程 CreateScheduleConference.sql

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CreateScheduleConference]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[CreateScheduleConference]
GO
CREATE PROC dbo.CreateScheduleConference
(
@ResouceIds VARCHAR(MAX)
)
AS
BEGIN
 SET NOCOUNT ON
 CREATE TABLE #TmpTbResourceSelect(ResourceId INT)

 INSERT #TmpTbResourceSelect SELECT * FROM dbo.fn_split(@ResouceIds,',')

 EXEC dbo.CheckPhysicsResourceIsValid @ResouceIds

DROP TABLE #TmpTbResourceSelect ---使用完成后删除临时表
 SET NOCOUNT OFF
END

子存储过程  CheckPhysicsResourceIsValid.sql

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CheckPhysicsResourceIsValid]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[CheckPhysicsResourceIsValid]
GO
CREATE PROC dbo.CheckPhysicsResourceIsValid
(
 @ResourceIds VARCHAR(MAX)
)
AS
BEGIN
 SELECT * FROM #TmpTbResourceSelect
END

调用示例

DECLARE @ResourceIds VARCHAR(MAX)
SET @ResourceIds = '1,2,3'
EXEC [dbo].[CreateScheduleConference] @ResourceIds

结果显示为：

ResourceId

1
2
3

如果再用 SELECT * FROM #TmpTbResourceSelect 则出现

消息 208，级别 16，状态 0，第 1 行
对象名  '#TmpTbResourceSelect' 无效。

即使在再改成 USE tempdb
SELECT * FROM #TmpTbResourceSelect运行后错误提示一样

如果将#TmpTbResourceSelect定义成##TmpTbResourceSelect时，则再次运行SELECT * FROM ##TmpTbResourceSelect可以得到正确结果