SQL SERVER表變數使用的一個樣本

（這是一個虛構的例子,請忽略事務）

首先，我們來看一個簡化單據修改的例子，單據資料表結構如下：
1. MOrder（id,remark,createtime）
id：單號
createtime：建立時間
2. MOrderDetail (MOrderid,pku,qty)
MOrderid：單號
pku：商品編號
qty：數量

面對這個簡單的需求，我們有第一版的SQL

View Code

update MOrder set remark=@remark
where id=@id
delete from MOrderDetail
where MOrderid=@id
insert into MOrderDetail  values(@id,@pku0,@qty0)
insert into MOrderDetail  values(@id,@pku1,@qty1)
……

針對需求，目前的處理沒有什麼問題。

演化一個新的需求：
1. MOrder（id,remark,createtime）
id：單號
createtime：建立時間
2. MOrderDetail (MOrderid,pku,qty)
MOrderid：單號
pku：商品編號
qty：數量
remark：備忘

某些員工可以在商品上加一個備忘，而制單人允許隨時修改數量，或調整商品（增刪）
此時，我們不能通過先delete，然後再insert的方式插入MOrderDetail資料，因為那會刪掉別人添加的東西
（假設允許制單人連商品帶注釋一起刪掉，但不能只刪注釋）
那麼制單人在修改單據後，就隱含了幾種變動：
1. 新增了一些商品
2. 修改了一些商品
3. 刪除了一些商品

一個直接SQL實現：

--存在則更新，不存在則插入
If  exists(select  1 from MOrder with(nolock) where MOrderid=@id and pku=@pku0)
update MOrderDetail set  qty=@qty0
where MOrderid=@id and pku=@pku0
Else
insert into MOrderDetail  values(@id,@pku0,@qty0)
……

--刪除
Delete from MOrderDetail where MOrderid=@id and pku not in(@pku0,@pku1……);

比如有100個以上的商品，我們的sql代碼量就很可觀了，而且商品越多效率將越低下。

我們來用表變數最佳化一下實現：

View Code

declare @tb table
(
   pku int,
       qty    int
)
Insert into @tb values(@pku0,@qty0)
Insert into @tb values(@pku1,@qty1)
……

Update a set qty=b.qty
From MOrderDetail as a
inner join @tb as b on a.pku=b.pku
Where a.MOrderid=@id


Delete from MOrderDetail
Where pku not in(select pku from @tb) and MOrderid=@id


Insert into MOrderDetail
Select @id,a.pku,b.qty from @tb
Where pku not in
(select pku from MOrderDetail where MOrderid=@id)

首先，我們將要更新的資料插入了表變數@tb

然後：

先關聯更新資料資料表已存在的資料，

刪除@tb的不存在商品

再將新增的商品插入資料表。

表變數的使用方式，與暫存資料表類似，但是不需要你顯式刪除暫存資料表。

表變數是在記憶體中，暫存資料表是物理表。再使用的時候，視情況而定，表變數處理小量資料更好，但是任務多、資料量大的操作對記憶體是個考驗。

經過反覆測試，表變數做這種批次更新效率更好。