Best Practice

You receive some data from UI and need to populate table Customer if the name received from UI does not exist in database. Which of options below should be used for this purpose:

1.

IF NOT EXISTS ( SELECT * FROM Customer
WHERE Name = @CustomerName )
INSERT INTO [Customer]
( [Name])
VALUES
( @CustomerName)

2.

INSERT INTO [Customer]
( [Name])
SELECT @CustomerName
WHERE NOT EXISTS ( SELECT * FROM Customer
WHERE Name = @CustomerName )

Correct answer:

Option 2 is the one to use

Explanation:

Option 1 has 2 separate statements. The first one applies locks relevant table resources in order to perform check for existence. As soon as it's completed SQL Server releases all applied locks. At the moment parallel SPID may insert a record into the table BEFORE following INSERT statement has applied its locks. Therefore initial check may be irrelevant at the moment when following INSERT starts. IF there is a unique constraint on Customer.Name INSERT would fail despite you've done the check for existence. The second option does everything in a single transaction. It applies the locks and holds it until INSERT transaction has finished its job. Another SPID cannot insert another row until all locks on the object applied by INSERT statement are released. So, the first option is unacceptable, it relies on a user's luck not to have another user doing same thing at the same time. Of course, probability of the failure is quite low but it's a possible event. In active transactional systems with hundreds of transactions per second it does not look so improbable. Script to run the test:

USE pubs
GO
CREATE TABLE Customer (
 ID int IDENTITY(1,1) NOT NULL,
 Name nvarchar(100) NOT NULL,
 UNIQUE (Name)
 )
GO
DECLARE @CustomerName nvarchar(100)
SET @CustomerName = 'Customer2'

IF NOT EXISTS ( SELECT * FROM Customer
 WHERE Name = @CustomerName )
BEGIN
 WAITFOR DELAY '00:00:05'
/* this 5 sec pause lets you insert same row from another QA window:
  DECLARE @CustomerName nvarchar(100)
  SET @CustomerName = 'Customer2'
   INSERT INTO [Customer]
    ( [Name])
   VALUES
    ( @CustomerName)
 */  

 INSERT INTO [Customer]
  ( [Name])
 VALUES
  ( @CustomerName)

END

SET @CustomerName = 'Customer3'
INSERT INTO [Customer]
 ( [Name])
SELECT  @CustomerName
WHERE NOT EXISTS ( SELECT * FROM Customer
 WHERE Name = @CustomerName )

GO
select Object_Id('Customer')

DROP TABLE Customer