T-SQL筆記7_單(雙)井字號_暫存Table與@資料表變數的使用

- 9月 27, 2020

若到某些公司工作使用到MS SQL資料庫

在預存程序中時常看到有井字號Table名稱的出現

這類通常就是所謂的「暫存資料表」

最後往往都會搭配一個drop 井字號Table

暫存資料表就是暫存資料的儲存處，於系統執行過程中，

經常需要執行一些複雜的運算與處理，在run-time過程不免會產生

一些階段性與過渡性for後續處理的資料(資料在整個處理作業完成後便不再需要而必須刪除)

像此類資料就適合存放於暫存資料表，可以想像成寫在程式中就會是對應為DataTable，用於資料變數傳遞用途、後製處理等等。

暫存資料表由開發人員自行創建，不管您於任何資料庫中建立暫存資料表，

暫存資料表皆固定存放於系統資料庫的 tempdb中，而且

一旦不使用會自動被刪除。

於 MS SQL中又分為

第一種.區域性暫存資料表(臨時表)

以一個井字號（#）開頭

=> #[TableName]

區域性暫存資料表只有建立它之連線的使用者能夠存取它，而且一旦此位使用者切斷與 SQL Server 的連線，區域性暫存資料表會在該session 關閉時自動被刪除，

當然，此位使用者也可以在連線期間

以 DROP TABLE 陳述式來刪除他所建立的區域性暫存資料表。

暫存表存在於"tempdb"這個database 裡

好的寫作習慣, 應在暫存表使用完畢後, 下指令去 drop, 而不是讓系統自動回收。

因為區域性臨時表通常都匯存比較久怕又記錄到上一次填入的值

備註:在使用前跟使用後通常都要分別加上

先判斷是否有已存於tempdb的區域性臨時表

如上一次沒有成功釋放掉就要drop

IF Object_id('tempdb..#tmp_table') IS NOT NULL
BEGIN
      DROP TABLE #tmp_table
END

用於確保Temp Table有釋放掉，避免錯誤發生。

用法:

--Step1.先檢查是否沒有drop成功還有留存，或已經有取過相同名稱的
IF Object_id('tempdb..#temp_table_name') IS NOT NULL
BEGIN
      DROP TABLE #temp_table_name
END

--Step2.產生臨時表
--寫法1. select into
select * into [#temp_table_name] from [既有資料表名稱或子查詢] 

--寫法2. 先定義table structure再insert
CREATE TABLE [#temp_table_name]
(
    [Column1] DataType,
    [Column2] DataType,
	....
)

--INSERT INTO #temp_table_name ([Column1],[Column2]...) VALUES (Val1,Val2,...)
--或
INSERT INTO #temp_table_name ([Column1],[Column2]...) (子查詢)


Step3. 最後記得drop掉 (或有寫上面那一句就可以)
DROP TABLE #temp_table_name

第二種.全域性暫存資料表(臨時表)

以兩個井字號（##）開頭

=> ##[TableName]

全域性暫存資料表一旦被建立，後續連線至

此 SQL Server 的任何使用者都能夠對其進行讀寫存取，且不需要特定的權限。

當建立全域性暫存資料表的使用者切斷與 SQL Server 的連線，

SQL Server 會檢查是否有其他使用者正在使用此全域性暫存資料表，

如果沒有，便立即將全域性暫存資料表刪除

如果有，SQL Server 會讓這些正在存取中的作業繼續進行，但是

不允許任何使用者再去存取全域性暫存資料表，等到所有未完成的作業執行完畢後，

全域性暫存資料表會自動被刪除。

由於全域性暫存資料表能夠被所有的連線使用，因此必須注意

其名稱不能與其他連線所採用的名稱相同(相同表命名是不允許的)。

除非 DROP TABLE 陳述式將它刪除或是 SQL Server 重新啟動，否則它是不會被刪除的。

用法:

同上(就是將暫存表名稱前面改成兩個#)

第三種.資料表(表格)變數 / Table Variables

以一個at sign (@) 開頭

=> @[TableName]

這一用法就跟平時在C#或vb.net裡面在寫的DataTable一樣概念，

就只是在runtime時期的一個儲存資料查詢結果的變數。

而且會自動釋放掉不需要再自己drop掉，實際上仍存在於tempdb裡面，

但不會存很久，會自動被釋放掉，直接建立在記憶體中，所有有更好的效能。

但如果資料大到一定程度也會占用disk I/O

資料表變數(@temp_var)跟區域性臨時表(#temp_table)差異在哪?

1.若你是包在一個Transaction有寫Rollbak機制

那可能要注意到@temp_var沒辦法roll back

而#temp_table可以

table variable 不支援 rollback的SQL程式示範

declare @tab table (val int)

insert into @tab select 2
insert into @tab select 3
insert into @tab select 4

select * from @tab

begin tran
begin try
    update @tab set val = 1
    select 1/0;
    commit
end try
begin catch
    rollback
end catch

select * from @tab

改為temp table 可支援 rollback的SQL程式示範

--declare @tab table (val int)
create table [#tab]
(
    val int
)

insert into [#tab] select 2
insert into [#tab] select 3
insert into [#tab] select 4

select * from [#tab]

begin tran
begin try
    update [#tab] set val = 1
    select 1/0;
    commit
end try
begin catch
    rollback
end catch

select * from [#tab]