SQL Server Concurrent Updlock Serializable vs Try Catch
Are both ways full proof, and will stop duplicate inserts?


Method 2 is not safe under concurrency as written. There is no guarantee that the row that caused the insert to fail will continue to exist when the select in the catch clause runs.

In addition, the catch clause could execute for errors other than a duplicate key violation, because the code does not check the error number.

You should also be aware of the potential for a doomed transaction.

Aaron Bertrand wrote about the overhead of try/catch. The overhead is usually higher than checking first.


  What is best coding practice wise, and performs better?


Method 1 is a common pattern, but needs a transaction to be safe. Performance depends on local factors, so you should conduct your own testing. As a side note, you can avoid one query by using the output clause instead:

DECLARE 
    @CustomerId uniqueidentifier = {guid '16D39773-9CC2-4CCF-A6A8-ACF1465030CC'},
    @Name varchar(255) = 'name';

BEGIN TRANSACTION;

    IF NOT EXISTS
    (
        SELECT * 
        FROM dbo.Customer WITH (UPDLOCK, SERIALIZABLE) 
        WHERE Name = @Name
    )
    BEGIN
        INSERT dbo.Customer(CustomerId, [Name])
        OUTPUT @CustomerId AS CustomerId
        VALUES (@CustomerId, @Name);
    END;
    ELSE
    BEGIN
        SELECT CustomerId FROM dbo.Customer WHERE [Name] = @Name;
    END;

COMMIT TRANSACTION;


As an alternative, you may want to compare the performance of a safe merge solution:

DECLARE 
    @CustomerId uniqueidentifier = {guid '16D39773-9CC2-4CCF-A6A8-ACF1465030CC'},
    @Name varchar(255) = 'name';

MERGE dbo.Customer WITH (SERIALIZABLE) AS C
USING (VALUES(@CustomerId, @Name)) AS I (CustomerId, [Name])
    ON I.Name = C.Name
WHEN NOT MATCHED 
    THEN INSERT (CustomerId, [Name])
    VALUES (I.CustomerId, I.[Name])
WHEN MATCHED THEN UPDATE 
    SET @CustomerId = C.CustomerId,
        @Name = C.[Name]
OUTPUT @CustomerId AS CustomerId;