SQL Server 2008中：INSERT如果不退出，保持独特的列 [英] SQL Server 2008: INSERT if not exits, maintain unique column

问题描述

我使用的是SQL Server 2008中。

I'm using SQL Server 2008.

我有为nvarchar（max）中，我要确保是独一无二的表中的列。 该表有60万条记录和50,000条记录成长每一天。

I've got a column NVARCHAR(MAX) in a table which I want to make sure is unique. The table has 600,000 records and grows every day by 50,000 records.

目前将项目添加到表之前我检查它是否存在于表中，如果没有我将其插入。

Currently before adding an item to the table I check if it exists in the table and if not I insert it.

IF NOT EXISTS (SELECT * FROM Softs Where Title = 'example example example.')
BEGIN
INSERT INTO Softs (....)
VALUES (...)
END

我没有一个索引标题列

I don't have a index on the Title column

最近，我开始插入项目表时，得到超时。

Recently, I started getting timeouts when inserting items to the table.

什么是保持独有的正确方法是什么？

What would be the correct way to maintain the uniques?

如果这将真正帮助我可以为nvarchar（max）更改为NVARCHAR（450）

推荐答案

这是疯狂不是有一个索引。

It's madness not to have an index.

这将有助于但指数密钥长度只能是900字节。

It would help but the index key length can only be 900 bytes.

不过，很可能你已经有重复的，因为第2的可能性存在运行一号存在，但后第一次插入之前。

However, it's likely you already have duplicates because the potential for a 2nd EXISTS to run after the 1st EXISTS but before the 1st INSERT.

创建索引会告诉你，并随后防止这一点。

The index creation will tell you, and subsequently protect against this.

不过，你可以得到重负载下的错误。

However, you can get errors under heavy load.

我赞成高刀片/低复制的方法是JFDI模式。高并发

My favoured approach for high inserts/low duplicates is the JFDI pattern. Highly concurrent

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

这篇关于SQL Server 2008中：INSERT如果不退出，保持独特的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！