Transact-SQL / Check if a name already exists

Question

Simple question here.

Context: A Transact-SQL table with an int primary key, and a name that also must be unqiue (even though it's not a primary key). Let's say:

TableID INT,
TableName NVARCHAR(50)

I'm adding a new rows to this able through a stored procedure (and, thus, specifying TableName with a parameter).

Question: What's the best/simplest way to verify if the provided TableName parameter already exist in the table, and to prevent the add of a new row if it's the case?

Is possible to do this directly within my AddNewRow stored procedure?

Answer 1

To add a unique constraint on TableName and handle the error if you try and insert a duplicate.

This avoids any issues with concurrent transactions inserting a duplicate in between you reading that it is not there and trying your insert.

See this related question.

Answer 2

I would prefer using Unique Constraint on the column and then explicitly checking on for its existance.

Handling an exception will result into Identity increment if present,

Secondly exception can be avoided by checking for existence before insertion which other wise is more expensive operation.

IF EXISTS (SELECT TOP(1) ColName FROM MyTable WHERE ColName=@myParameter)

If using Unique constraint you can also apply Unique Nonclustured index resulting into fast retrieval alongwith.

Answer 3

If you're using SQL Server 2008 then you could use a MERGE statement in your sproc:

MERGE INTO YourTable AS target
USING (VALUES (@tableName)) AS source (TableName)
    ON target.TableName = source.TableName
WHEN NOT MATCHED THEN
    INSERT (TableName) VALUES (TableName)

You should still ensure that the TableName column has a UNIQUE constraint.