RiCo技術農場

很多開發人員或DBA很愛使用資料表變數，我得到答案大部分都說小資料量(小於1000)都存在記憶體，

同session又可重複使用並自動釋放資源且不會影響交易紀錄檔，整體來說，比暫存資料表來的好用太多了，

重點是很多技術blog文章都這樣寫，但....讓我一一來釐清這些網路謠言

 

大部分說資料表變數無法建立索引，這是和暫存資料表最大差異(乾脆說寫法不同..XD)，

但應該是資料表變數不存在統計值，暫存資料表有統計值，這才是兩者最大差異。

雖然有人會說無法建立索引應該也算最大差異(SQL2014資料表變數才開始支援建立索引)，

但SQL2014以前的版本真的都不能建立索引嗎?

所有的疑問，就讓我使用SQL2008 R2 一一幫大家釐清

目前沒有任何暫存資料表和索引。

我用一個簡單範例，回答所有問題

set statistics io,time on
set nocount on

--確認目前所有temptable和index
select t.name, i.name, i.index_id
from tempdb.sys.tables t
join tempdb.sys.indexes i ON t.object_id = i.object_id

--SQL 2005~20012的資料表變數當然可以建立索引
declare @mytest table
(
id int identity(1,1) not null primary key
,c1 int
,c2 varchar(10)
,unique (c1,id)
,unique (c2,id)
)

--只新增4筆資料，網路技術blog文章都說小於1000筆都會存在記憶體(如果你用in-memory table這當然是真的)
insert into @mytest
values (10,'rico'),(20,'sherry'),(30,'fifi'),(40,'pcdou')

--確認是否存在索引
select t.name, i.name, i.index_id
from tempdb.sys.tables t
join tempdb.sys.indexes i ON t.object_id = i.object_id

--確認是否可以使用索引搜尋
select t1.c1 
from @mytest t1 
where c2='rico' and id>0

--確認是否會寫交易紀錄檔
select Operation, AllocUnitName,[End Time] 
from fn_dblog(NULL, NULL)

我建立資料表變數，我也同時宣告了primary key(預設會建立clustered index)和2個unique nonclustered index，

毫無疑問的SQL2014前的資料表變數當然可以建立索引，第一個結果集真實呈現(PK_xxx和2個UQ_xxx)。

資料表變數我只新增4筆資料，但從第一和第三結果集來看，SQL SERVER幫我建立了暫存資料表，並將交易過程所有資料立即寫入交易紀錄檔，

所以小資料量的資料表變數依然存放在disk，並且會耗用交易紀錄檔案空間(你甚至可以kill sqlserver.exe進一步驗證記憶體)。

 

下面執行計畫更可以確認SQL2014前的版本，資料表變數不只可以建立索引，也可以正常使用索引。

答案:

1.資料表變數和暫存資料表最大差異?  Ans:統計值

2.SQL2005~SQL2012的資料表變數是否可以建立索引? Ans:可以，也可正常使用。

3.小資料量的資料表變數是否存於記憶體? Ans:均存在disk(使用tempdb資源)

4使用資料表變數是否會影響交易紀錄檔? Ans:會耗用交易紀錄檔空間。