Check For Duplicate Records VS try/catch Unique Key Constraint

Question

I have a database table that has a Unique Key constraint defined to avoid duplicate records from occurring.

I'm curious if it is bad practice to NOT manually check for duplicate records prior to running an INSERT statement on the table.

In other words, should I run a SELECT statement using a WHERE clause that checks for duplicate values of the record that I am about to INSERT. If a record is found, then do not run the INSERT statement, otherwise go ahead and run the INSERT....

OR

Just run the INSERT statement and try/catch the exception that may be thrown due to a Unique Key violation.

I'm weighing the two perspectives and can't decide which is best- 1. Don't waste a SELECT call to check for duplicates when I can just trap for an exception VS 2. Don't be lazy by implementing ugly try/catch logic VS 3. ???Your thoughts here??? :)

Answer 1

You really have to use the try..catch method. It may be less elegant, but it's foolproof.

If there is more than one client updating this table, then another client may insert a record between your check and your insert. You can still check if you want to, to save trying to do the insert needlessly. It might be a small performance increase if that's what you're worried about, but only if there is a duplicate. But every time there isn't a duplicate, you've paid a performance penalty by doing both a SELECT and an INSERT.

Anyway, what are the odds that there will be a constraint violation? Probably small, so why bother with the check?

Answer 2

Try/catch is safer, and more scalable because you'll only touch the table once. The try/catch removes the frankly erratic error handling in earlier versions

See lesson 4 from this article too