约束检查:TRY/CATCH vs Exists() [英] Constraints check: TRY/CATCH vs Exists()

问题描述

我有一个带有唯一约束的表:

I have a table with unique constraint on it:

create table dbo.MyTab
(
    MyTabID int primary key identity,
    SomeValue nvarchar(50)
);
Create Unique Index IX_UQ_SomeValue 
On dbo.MyTab(SomeValue);
Go

哪个代码更适合检查重复项(如果找到重复项，则成功 = 0)?

Which code is better to check for duplicates (success = 0 if duplicate found)?

选项 1

Declare @someValue nvarchar(50) = 'aaa'
Declare @success bit = 1;
Begin Try 
    Insert Into MyTab(SomeValue) Values ('aaa');
End Try
Begin Catch
    -- lets assume that only constraint errors can happen
    Set @success = 0;
End Catch
Select @success

选项 2

Declare @someValue nvarchar(50) = 'aaa'
Declare @success bit = 1;
IF EXISTS (Select 1 From MyTab Where SomeValue = @someValue)
    Set @success = 0;
Else 
    Insert Into MyTab(SomeValue) Values ('aaa');
Select @success

从我的角度来看 - 我确实相信 Try/Catch 是针对错误的，这不是预期的(例如死锁或什至不期望重复时的约束).在这种情况下 - 有时用户可能会尝试提交重复项，因此会出现错误.

From my point of view- i do believe that Try/Catch is for errors, that were NOT expected (like deadlock or even constraints when duplicates are not expected). In this case- it is possible that sometimes a user will try to submit duplicate, so the error is expected.

我发现 Aaron Bertrand 的文章 指出 - 即使大多数插入成功，检查重复项也不会慢很多.

I have found article by Aaron Bertrand that states- checking for duplicates is not much slower even if most of inserts are successful.

网上也有很多关于使用 Try/Catch 的建议(以避免 2 个语句而不是 1 个语句).在我的环境中，可能只有 1% 的不成功案例，所以这也是有道理的.

There is also loads of advices over the net to use Try/Catch (to avoid 2 statements not 1). In my environment there could be just like 1% of unsuccessful cases, so that kind of makes sense too.

你有什么看法?使用选项 1 或选项 2 的其他原因是什么?

What is your opinion? Whats other reasons to use option 1 OR option 2?

更新:我不确定在这种情况下它是否重要，但表具有而不是更新触发器(出于审计目的-行删除也通过更新语句发生).

UPDATE: I'm not sure it is important in this case, but table have instead of update trigger (for audit purposes- row deletion also happens through Update statement).

推荐答案

我看过那篇文章，但请注意，对于低失败率，我更喜欢JFDI"模式.我以前在大容量系统上使用过它(40k 行/秒).

I've seen that article but note that for low failure rates I'd prefer the "JFDI" pattern. I've used this on high volume systems before (40k rows/second).

在 Aaron 的代码中，在高负载和大量写入的情况下进行第一次测试时，您仍然可以得到重复.(此处在 dba.se 上进行了解释)这很重要:您的重复仍然会发生，只是频率较低.您仍然需要异常处理并知道何时忽略重复错误 (2627)

In Aaron's code, you can still get a duplicate when testing first under high load and lots of writes. (explained here on dba.se) This is important: your duplicates still happen, just less often. You still need exception handling and knowing when to ignore the duplicate error (2627)

Remus 在另一个答案中简洁地解释了

explained succinctly by Remus in another answer

但是，我将有一个单独的 TRY/CATCH 来测试仅是否有重复的错误

However, I would have a separate TRY/CATCH to test only for the duplicate error

BEGIN TRY

-- stuff

  BEGIN TRY
     INSERT etc
  END TRY
  BEGIN CATCH
      IF ERROR_NUMBER() <> 2627
        RAISERROR etc
  END CATCH

--more stuff

BEGIN CATCH
    RAISERROR etc
END CATCH

这篇关于约束检查:TRY/CATCH vs Exists()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！