SQL Server-如何插入记录并确保它是唯一的 [英] SQL Server - How to insert a record and make sure it is unique

问题描述

我试图找出将记录插入单个表的最佳方法，但前提是该项目尚不存在.在这种情况下，KEY是NVARCHAR(400)字段.在此示例中，假设它是牛津英语词典中 word 的名称/在此处插入您的fav词典.另外，我猜测我将需要使Word字段为主键. (该表还将具有唯一的标识符PK.)

所以..我可能会得到需要添加到表中的这些单词...

例如

• 猫
• 狗
• Foo
• 酒吧
• PewPew
• 等...

所以传统上，我会尝试以下(伪代码)

SELECT WordID FROM Words WHERE Word = @Word
IF WordID IS NULL OR WordID <= 0
    INSERT INTO Words VALUES (@Word)

即. 如果该词不存在，则将其插入.

现在..我担心的问题是，我们遇到了很多打击..因此，有可能在另一个过程中将单词插入到SELECT和INSERT之间.约束错误? (即种族条件).

然后我以为我可以做到以下事情...

INSERT INTO Words (Word)
SELECT @Word
WHERE NOT EXISTS (SELECT WordID FROM Words WHERE Word = @Word)

基本上，在不存在时插入一个单词.

除了语法不好之外，我不确定这是好是坏，因为它如何锁定表(如果确实如此)，并且在表上的性能并不高，因此会产生大量读取和大量写入操作. /p>

那么-您的Sql专家怎么想/做什么?

我希望有一个简单的插入并捕获"到抛出的任何错误.

解决方案

您的解决方案:

INSERT INTO Words (Word)
    SELECT @Word
WHERE NOT EXISTS (SELECT WordID FROM Words WHERE Word = @Word)

...效果差不多.您可以将其简化为:

INSERT INTO Words (Word)
    SELECT @Word
WHERE NOT EXISTS (SELECT * FROM Words WHERE Word = @Word)

...因为EXISTS实际上不需要返回任何记录，所以查询优化程序不会打扰您要查找的字段.

但是，正如您提到的那样，这并不是特别有效，因为它将在INSERT期间锁定整个表.除此之外，如果您向Word添加唯一索引(不必是主键)，则只需要锁定相关页面即可.

您最好的选择是模拟预期的负载，并使用SQL Server Profiler查看性能.与任何其他领域一样，过早的优化是一件坏事.定义可接受的性能指标，然后再进行其他操作.

如果仍然不能为您提供足够的性能，那么数据仓库领域提供了很多技术可以帮助您.

I'm trying to figure out the best way to insert a record into a single table but only if the item doesn't already exist. The KEY in this case is an NVARCHAR(400) field. For this example, lets pretend it's the name of a word in the Oxford English Dictionary / insert your fav dictionary here. Also, i'm guessing i will need to make the Word field a primary key. (the table will also have a unique identifier PK also).

So .. i might get these words that i need to add to the table...

eg.

• Cat
• Dog
• Foo
• Bar
• PewPew
• etc...

So traditionally, i would try the following (pseudo code)

SELECT WordID FROM Words WHERE Word = @Word
IF WordID IS NULL OR WordID <= 0
    INSERT INTO Words VALUES (@Word)

ie. If the word doesn't exist, then insert it.

Now .. the problem i'm worried about is that we're getting LOTS of hits .. so is it possible that the word could be inserted from another process in between the SELECT and the INSERT .. which would then throw a constraint error? (ie. a Race Condition).

I then thought that i might be able to do the following ...

INSERT INTO Words (Word)
SELECT @Word
WHERE NOT EXISTS (SELECT WordID FROM Words WHERE Word = @Word)

basically, insert a word when it doesn't exist.

Bad syntax aside, i'm not sure if this is bad or good because of how it locks down the table (if it does) and is not that performant on a table that it getting massive reads and plenty of writes.

So - what do you Sql gurus think / do?

I was hoping to have a simple insert and 'catch' that for any errors thrown.

解决方案

Your solution:

INSERT INTO Words (Word)
    SELECT @Word
WHERE NOT EXISTS (SELECT WordID FROM Words WHERE Word = @Word)

...is about as good as it gets. You could simplify it to this:

INSERT INTO Words (Word)
    SELECT @Word
WHERE NOT EXISTS (SELECT * FROM Words WHERE Word = @Word)

...because EXISTS doesn't actually need to return any records, so the query optimiser won't bother looking at which fields you asked for.

As you mention, however, this isn't particularly performant, because it'll lock the whole table during the INSERT. Except that, if you add a unique index (it doesn't need to be the primary key) to Word, then it'll only need to lock the relevant pages.

Your best option is to simulate the expected load and look at the performance with SQL Server Profiler. As with any other field, premature optimisation is a bad thing. Define acceptable performance metrics, and then measure before doing anything else.

If that's still not giving you adequate performance, then there's a bunch of techniques from the data warehousing field that could help.

这篇关于SQL Server-如何插入记录并确保它是唯一的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！