到底該使用 資料表變數 還是 暫存資料表 來存放一些暫時性處理的資料呢 ?
這幾天有朋友詢問到一個問題，她看書上說「在 SQL Server 內，使用暫存資料表時可以建立索引來加速查詢，但資料表變數不支援這項作法」。於是她詢問我說，為什麼還是這麼大量地使用資料表變數在我的專案內 ?
這樣的說法是比較容易誤解，在讀的時候可能要注意一下，在上面的描述是指資料表變數，不允許後來再建立 Index，但我們可以在建立資料表變數的時候，就指定好相關的索引在資料表變數上，這樣就可以讓該資料表變數在使用的時候，搭配索引來做相關搜尋。
因此有興趣的朋友可以試試看這一段語法
SET NOCOUNT ON
DECLARE @MyData TABLE( Id INT, ItemName NVARCHAR(100)) ;
DECLARE @Ptr INT = 0 ;
WHILE @Ptr < 100000
BEGIN
INSERT INTO @MyData VALUES ( @Ptr , LEFT( CAST( ABS( CHECKSUM( CAST( NEWID() AS VARCHAR(128) ) )) AS VARCHAR ) + '000000000' , 10 ));
SET @Ptr += 1 ;
END ;
SET STATISTICS IO,TIME,XML ON
SELECT * FROM @MyData WHERE ItemName BETWEEN '2056' AND '2057'
SET STATISTICS IO,TIME,XML OFF
這段語法執行後，我們可以看到，當去查找資料的時候，是完全採用 Table Scan 的方式去找資料
如同一開始所談到的，這個資料表變數我們並不能再去建立索引，但我們依然可以換個做法，我們把上述的語法在建立資料表變數的時候調整一下
SET NOCOUNT ON
DECLARE @MyData TABLE( Id INT Primary Key, ItemName NVARCHAR(100), Index IX NONCLUSTERED(ItemName) ) ;
DECLARE @Ptr INT = 0 ;
WHILE @Ptr < 100000
BEGIN
INSERT INTO @MyData VALUES ( @Ptr , LEFT( CAST( ABS( CHECKSUM( CAST( NEWID() AS VARCHAR(128) ) )) AS VARCHAR ) + '000000000' , 10 ));
SET @Ptr += 1 ;
END ;
SET STATISTICS IO,TIME,XML ON
SELECT * FROM @MyData WHERE ItemName BETWEEN '2056' AND '2057'
SET STATISTICS IO,TIME,XML OFF
此時我們看一下結果，會發現已經從資料表掃描，轉換為索引搜尋了
雖然在上述的範例中，看不出來索引搜尋對整體的效能幫助有多大，但在我個人所遇到的一些案例中，特別是這些暫存資料，如有後續又有跟一些實體資料表去關聯的時候，那麼有沒有建立索引，效能上就會有不小的差異了。而資料表變數在使用上算是非常的便利，但可能大家要注意一下，他還是會去使用 Tempdb 來存放資料，並不是所謂的 In-Memory Table ，這裡可不要搞錯了。當然還有比較重要的一點，那就是定序的問題了，暫存資料表的定序在一般狀況下是使用系統資料庫的定序，而不是使用者資料庫的定序，而到了 SQL Server 2012 之後，因為有 contained database ，因此會使暫存資料表在 contained database 下是跟使用者資料庫相同的定序，所以使用上就要特別注意當定序不同的時候，彼此之間的關聯就會造成一些問題或者是要特別做處理。