Posted by Erland Sommarskog on 02/07/07 08:20

(vikram.mankar@gmail.com) writes:
> I'll give it a shot. Is it generally more efficient to check for
> duplicates through T-SQL like with WHERE NOT EXISTS? or use
> constraints on the table? I realize the latter is a little painful as
> it clogs the error logs for the job history.

If you want to prevent duplicates at all, you should use constraints.
And then you may use WHERE NOT EXISTS to avoid the errors to happen.
Note that if you only use WHERE NOT EXISTS, but does not have any index
at all, performance will be awful.

As for that log table, I would not put any constraints on that table
that prevents duplicates, nor any WHERE NOT EXISTS. It seems to me that
it may be better to permitt the application to log what it logs and
weed out duplicates later.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]