Sqlserver 關於臨時表和表變數的總結

結論
1、一旦建立本地臨時表的會話退出，本地臨時表將被自動刪除；當建立全域性臨時表的會話結束時，並且最後一條引用此表的Transact-SQL語句完成後，全域性臨時表將被自動刪除
2、本地臨時表只對當前會話可見，全域性臨時表的表結構和表裡的資料同時對所有會話可見，其他會話可以查詢和修改全域性臨時表
3、不管本地臨時表還是全域性臨時表，就算指定了資料庫，都是建立建立到了tempdb資料庫下
4、表變數的作用域就是可以引用該變數的 Transact-SQL 語句的範圍。 表變數的作用域從宣告變數的地方開始到宣告變數的批處理或儲存過程的結尾，一個表變數宣告後使用GO再查詢該表變數，則查詢報錯，因為在GO後沒有在再宣告表變數，所以表變數自身沒有鎖的概念，就像oracle的PGA，自己私有的東西沒有共享概念就沒有鎖


實驗1
會話1

use testdb1

CREATE TABLE #localtemp1(Column1 INT NOT NULL,Column2 NVARCHAR(4000));
CREATE TABLE #localtemp2(Column1 INT NOT NULL,Column2 NVARCHAR(4000));
CREATE TABLE ##globaltemp1(Column1 INT NOT NULL,Column2 NVARCHAR(4000));
CREATE TABLE ##globaltemp2(Column1 INT NOT NULL,Column2 NVARCHAR(4000));
insert into #localtemp1 values (1,'1');
insert into #localtemp2 values (1,'1');
insert into ##globaltemp1 values (1,'1');
insert into ##globaltemp2 values (1,'1');
select * from #localtemp1;--有結果
select * from #localtemp2;--有結果
select * from ##globaltemp1;--有結果
select * from ##globaltemp2;--有結果

備註：發現表並不是建立在testdb1庫下面，而是建立在tempdb這個庫下面

會話2

select * from #localtemp1;--報錯，表不存在
select * from #localtemp2;--報錯，表不存在
select * from ##globaltemp1;--有結果
select * from ##globaltemp2;--有結果

會話1退出
會話2

select * from #localtemp1;--報錯，表不存在
select * from #localtemp2;--報錯，表不存在
select * from ##globaltemp1;--報錯，表不存在
select * from ##globaltemp2;--報錯，表不存在

結論：建立本地臨時表的會話一旦退出，本地臨時表自動銷燬了，建立全域性臨時表的會話一旦退出，並且沒有會話引用該全域性臨時表，則該全域性臨時表也自動銷燬了


實驗2
會話1

CREATE TABLE #localtemp1(Column1 INT NOT NULL,Column2 NVARCHAR(4000));
CREATE TABLE #localtemp2(Column1 INT NOT NULL,Column2 NVARCHAR(4000));
CREATE TABLE ##globaltemp1(Column1 INT NOT NULL,Column2 NVARCHAR(4000));
CREATE TABLE ##globaltemp2(Column1 INT NOT NULL,Column2 NVARCHAR(4000));
insert into #localtemp1 values (1,'1');
insert into #localtemp2 values (1,'1');
insert into ##globaltemp1 values (1,'1');
insert into ##globaltemp2 values (1,'1');
select * from #localtemp1;--有結果，1行資料
select * from #localtemp2;--有結果，1行資料
select * from ##globaltemp1;--有結果，1行資料
select * from ##globaltemp2;--有結果，1行資料

會話2

CREATE TABLE ##globaltemp1(Column1 INT NOT NULL,Column2 NVARCHAR(4000));--報錯，表已經存在
CREATE TABLE ##globaltemp2(Column1 INT NOT NULL,Column2 NVARCHAR(4000));--報錯，表已經存在

會話2

CREATE TABLE #localtemp1(Column1 INT NOT NULL,Column2 NVARCHAR(4000));
CREATE TABLE #localtemp2(Column1 INT NOT NULL,Column2 NVARCHAR(4000));
insert into #localtemp1 values (1,'1');
insert into #localtemp2 values (1,'1');
insert into ##globaltemp1 values (1,'1');
insert into ##globaltemp2 values (1,'1');
select * from #localtemp1;--有結果，1行資料
select * from #localtemp2;--有結果，1行資料
select * from ##globaltemp1;--有結果，2行資料
select * from ##globaltemp2;--有結果，2行資料

結論2：本地臨時表只對當前會話可見，全域性臨時表的表結構和表裡的資料同時對所有會話可見

實驗3

CREATE TABLE testdb1.dbo.#localtemp12(Column1 INT NOT NULL,Column2 NVARCHAR(4000));
Database name 'testdb1' ignored, referencing object in tempdb.
CREATE TABLE testdb1.dbo.##localtemp13(Column1 INT NOT NULL,Column2 NVARCHAR(4000));
Database name 'testdb1' ignored, referencing object in tempdb.

結論3：不管本地臨時表還是全域性臨時表，就算指定了資料庫，都是建立建立到了tempdb資料庫下

實驗4.1

DECLARE @LOCALTB1 TABLE( ID INT ,ID2 INT )
DECLARE @@GLOBALTB1 TABLE( ID INT ,ID2 INT )
INSERT INTO @LOCALTB1 SELECT id1,id2 from [dbo].[table1]
INSERT INTO @@GLOBALTB1 SELECT id1,id2 from [dbo].[table1]
select * from  @LOCALTB1 --有結果
select * from  @@GLOBALTB1--有結果

實驗4.2

DECLARE @LOCALTB1 TABLE( ID INT ,ID2 INT )
DECLARE @@GLOBALTB1 TABLE( ID INT ,ID2 INT )
INSERT INTO @LOCALTB1 SELECT id1,id2 from [dbo].[table1]
INSERT INTO @@GLOBALTB1 SELECT id1,id2 from [dbo].[table1]
GO
select * from  @LOCALTB1 --報錯Must declare the table variable "@LOCALTB1".
select * from  @@GLOBALTB1 --報錯Must declare the table variable "@@GLOBALTB1".

實驗4.3

DECLARE @LOCALTB1 TABLE( ID INT ,ID2 INT )
DECLARE @@GLOBALTB1 TABLE( ID INT ,ID2 INT )
GO
INSERT INTO @LOCALTB1 SELECT id1,id2 from [dbo].[table1] --報錯Must declare the table variable "@LOCALTB1".
INSERT INTO @@GLOBALTB1 SELECT id1,id2 from [dbo].[table1] --報錯Must declare the table variable "@@GLOBALTB1".
GO
select * from  @LOCALTB1 --報錯Must declare the table variable "@LOCALTB1".
select * from  @@GLOBALTB1 --報錯Must declare the table variable "@@GLOBALTB1".