If I'm regularly going to to attempt to add a duplicate row in a table and duplicates aren't allowed, should I attempt the insert and catch an exception or manually check for the row's existence and only try the insert if the row isn't found?

In both cases, the unique constraint exists.

  • 1
    My opinion is that you should use INSERT...WHERE NOT EXISTS when the common case is that the row already exits. – Dan Guzman Nov 5 '17 at 17:10
  • Show the T-SQL table definition, any existing indexes, some sample data, and an example insert statement. As it stands now, your question is too broad. – Max Vernon Nov 5 '17 at 19:46

You need the unique index (or constraint, I prefer indexes because of includes and filters) anyway to guarantee data integrity.

So the question is one of

  • do check-as-part-of-insert? OR
  • try insert-and-catch?

Note, checking before insert is not reliable, for at least these reasons

  • if someone does it manually
  • assumes you have a consistent code path to insert
  • 2 concurrent IF checks can potentially both see that the row does not exist and try to insert the same value

Using INSERT...WHERE NOT EXISTS (As mentioned in comments) will use the index, so will be efficient anyway.

There is no benefit in dealing with exceptions unless you have a real need to.

| improve this answer | |
  • The exception would be there as a fallback when there're two concurrent IF checks. But it would be just that - exceptional. – Ian Warburton Nov 6 '17 at 12:17

Not the answer you're looking for? Browse other questions tagged or ask your own question.