SQL Server tests for repeating data insertions

Recently published scripts that have the kind of prevention of repeated insertions of data (including when there is an update, that there is no insert processing, the direction of the judgment may be the opposite of the following example)

Use SQL similar to the following

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;

Or the use of this kind of sentences

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;

or with a 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

;

These methods have been explained that can not prevent the insertion of duplicate data , but the probability of the occurrence of a certain difference between the high and low

For this kind of processing needs, I have done a number of tests, effective treatment methods are as follows, when you are dealing with such problems, please refer to this method:

declare @id int, @value int

BEGIN Tran--keep the update lock on the query to the end of the transaction

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

Recommended Use This type of sentence is handled without explicit transaction control, avoiding the need to consider interactivity with other places where transactions are explicitly used

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;

Not recommended this way, it's easy to come out dead lock

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

In addition,where This judge is repeating the condition column, need to create an index , otherwise because of the lock relationship, data processing efficiency will be very low

If there is no duplicate data in the table, you should create a unique index directly , which is very helpful for query optimization.

SQL Server tests for repeating data insertions