I think it most cases the differences between that two are going to be small enough that the choice should mostly be driven by picking the implementation that ends up being most understandable to someone looking at the code for the first time.
However, I think exception handling has a few small advantages:
-
Exception handling avoids a potential race condition. The ‘check, then insert’ method might fail if another process inserts a record between your check and your insert. So, even if you’re doing ‘check then insert’ you still want exception handling on the insert and if you’re already doing exception handling anyways then you might as well do away with the initial check.
-
If your code is not a stored procedure and has to interact with the database via the network (i.e. the application and the db are not on the same box), then you want to avoid having two separate network calls (one for the check and the other for the insert) and doing it via exception handling provides a straightforward way of handling the whole thing with a single network call. Now, there are tons of ways to do the ‘check then insert’ method while still avoiding the second network call, but simply catching the exception is likely to be the simplest way to go about it.
On the other hand, exception handling requires a unique constraint (which is really a unique index), which comes with a performance tradeoff:
- Creating a unique constraint will be slow on very large tables and it will cause a performance hit on every single insert to that table. On truly large databases you also have to budget for the extra disk space consumed by the unique index used to enforce the constraint.
- On the other hand, it might make selecting from the table faster if your queries can take advantage of that index.
I’d also note that if you’re in a situation where what you actually want to do is ‘update else insert’ (i.e. if a record with the unique value already exists then you want to update that record, else you insert a new record) then what you actually want to use is your particular database’s UPSERT method, if it has one. For SQL Server and Oracle, this would be a MERGE statement.