[SQL] MSSQL暫存資料表

當JOIN太多資料表或使用UNION時有時效能會變得很差，語法也相對變得複雜也難閱讀，這時暫存資料表就派上用場


1.使用DECLARE @TEMPTABLE TABLE
資料存放在記憶體，速度快但不太適合存太多筆資料，生命週期為一個批次

DECLARE @TEAMPTABLE TABLE(
  book VARCHAR(30),
  price int,
)

INSERT INTO @TEAMPTABLE (book, price) SELECT book, price FROM books WHERE (price = 100)

SELECT * FROM @TEAMPTABLE


2.使用CREATE TABLE #TEMPTABLE
資料存放在tempdb，生命週期為多個批次，session結束後自動刪除

CREATE TABLE #TEMPTABLE(
  book VARCHAR(30),
  price int,
)

INSERT INTO #TEMPTABLE (book, price) SELECT book, price FROM books WHERE (price = 100)

SELECT * FROM #TEMPTABLE


另外在語法前後加上以下語法可確保#TEMPTABLE有被DROP掉

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


小技巧1：若有需要從TEMPTABLE回寫原資料表，且要新的ID以免重複時可使用ROW_NUMBER()增加一個新ID欄位

ROW_NUMBER() OVER(ORDER BY RowID) + (SELECT MAX(RowID) FROM book))


小技巧2：快速複製資料表結構
SELECT TOP 0 * INTO #TEMPTABLE FROM book