Which is more expensive? Trapping an Exception to prevent duplicate entries
in a database, or using IF EXISTS in T-SQL?

Also, how would I go about testing which is more expensive myself?

Thanks

On Feb 4, 2:23 pm, "Mike" <bl...@blank.com> wrote:
> Which is more expensive? Trapping an Exception to prevent duplicate entries
> in a database, or using IF EXISTS in T-SQL?
>
> Also, how would I go about testing which is more expensive myself?
>
> Thanks

For a single insert in your stored procedure when IF EXISTS is true
and/or an index violation is thrown, I don't know.

But if you are talking across the breadth of many rows, each with
their own invocation of your stored procedure - and there were few to
no duplicates then it would definitely be slower to test before
inserting. Only you know how often the circumstance of duplication is
likely to occur. Write two sets of test code that reflects your
assumption on quantity and quality of data - one that uses TRY and the
other IF EXISTS - and then keep a close look at your watch.

Michael O
http:/blog.crisatunity.com

"Michael O" <(E-Mail Removed)> wrote in message
news:c8e32dda-14ee-4ed4-8ce7-(E-Mail Removed)...
> On Feb 4, 2:23 pm, "Mike" <bl...@blank.com> wrote:
>> Which is more expensive? Trapping an Exception to prevent duplicate
>> entries
>> in a database, or using IF EXISTS in T-SQL?
>>
>> Also, how would I go about testing which is more expensive myself?
>>
>> Thanks
>
> For a single insert in your stored procedure when IF EXISTS is true
> and/or an index violation is thrown, I don't know.
>
> But if you are talking across the breadth of many rows, each with
> their own invocation of your stored procedure - and there were few to
> no duplicates then it would definitely be slower to test before
> inserting. Only you know how often the circumstance of duplication is
> likely to occur. Write two sets of test code that reflects your
> assumption on quantity and quality of data - one that uses TRY and the
> other IF EXISTS - and then keep a close look at your watch.
>
> Michael O
> http:/blog.crisatunity.com

I should have been clearer. I was wanting to compare Try... Catch... in the
database method code, such as in an ASP.NET app, with IF EXISTS in T-SQL.
So the location of the code is different for either case.