SQl server 關於重複插入資料的測試

標籤：

最近發布的指令碼，有那種防止重複插入資料（包括存在時更新，不存在是插入的處理，判斷的方向可能與下面的樣本相反）

使用類似下面的 SQL

declare @id int, @value int

if not exists( select * from tb where id = @id )

    insert tb values( @id, @value );

--else

--  update tb set value = @value where id = @id;

 

或者是使用這種單句的

declare @id int, @value int

insert tb select @id, @value

where not exists( select * from tb where id = @id )

--if @@rowcount = 0

--  update tb set value = @value where id = @id;

 

或者是用 MERGE 的

declare @id int, @value int

merge tb

    using( values(@id, @value) ) data( id, value)

        on data.id = tb.id

    when not matched by target then insert values( id, value )

    --when matched then update set value = data.value

;

         這幾種方法均已說明是無法防止插入重複資料的，只是發生的機率高低有一定的區別而已

 

 

針對這種處理需求，我專門做了一些測試，有效處理方法如下，大家在處理這類問題時，請參考此方法進行：

declare @id int, @value int

begin tran  -- 使查詢的更新鎖定保留到事務結束

if not exists( select * from tb with(holdlock, updlock) where id = @id )

    insert tb values( @id, @value );

--else

--  update tb set value = @value where id = @id;

commit tran

 

推薦使用這種單句的處理方式，不用顯式的事務控制，避免考慮與其他會顯式使用事務的地方的互動性

declare @id int, @value int

insert tb select @id, @value

where not exists( select * from tb with(holdlock, updlock) where id = @id )

--if @@rowcount = 0

--  update tb set value = @value where id = @id;

 

不推薦這種方式，容易出來死結

declare @id int, @value int

merge tb with(holdlock, updlock)

    using( values(@id, @value) ) data( id, value)

        on data.id = tb.id

    when not matched by target then insert values( id, value )

    --when matched then update set value = data.value

 

另外，where 這個判斷是否重複的條件列，需要建立索引，否則因為鎖的關係，資料處理的效率會極低

         如果表中本來就沒有重複資料，應該直接建立唯一索引（UNIQUE INDEX），這個對於查詢最佳化有很大的協助

 

SQl server 關於重複插入資料的測試