SQL Server - как вставить запись и убедиться, что она уникальна

Я пытаюсь найти лучший способ вставить запись в одну таблицу, но только если элемент еще не существует. Ключевым в этом случае является поле NVARCHAR (400). В этом примере давайте притворимся, что это имя слова в Оксфордском словаре английского языка/вставьте словарь fav здесь. Кроме того, я предполагаю, что мне нужно будет сделать поле Word первичным ключом. (таблица также будет иметь уникальный идентификатор PK).

Итак, я могу получить эти слова, которые мне нужно добавить в таблицу...

например.

Cat
Собака
Foo
Bar
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)

в принципе, вставьте слово, если оно не существует.

Отброшенный синтаксис в стороне, я не уверен, что это плохо или хорошо из-за того, как он блокирует таблицу (если это так) и не является тем, что на столе, что он получает массивные чтения и много записей.

Итак, что вы думаете/делаете ли вы Sql гуру?

Я надеялся иметь простую вставку и "уловку" для любых ошибок.

Ответ 1

Ваше решение:

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. Как и в любой другой области, преждевременная оптимизация - это плохо. Определите приемлемые показатели производительности, а затем измерьте, прежде чем делать что-либо еще.

Если это еще не дает вам адекватной производительности, тогда вам может понадобиться множество методов из области хранилища данных.

Ответ 2

Я думаю, что нашел лучший (или, по крайней мере, более быстрый) ответ на этот вопрос. Создайте индекс, например:

CREATE UNIQUE NONCLUSTERED INDEX [IndexTableUniqueRows] ON [dbo].[table] 
(
    [Col1] ASC,
    [Col2] ASC,

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Включить все столбцы, которые определяют уникальность. Важной частью является IGNORE_DUP_KEY = ON. Это превращает уникальные вставки в предупреждения. SSIS игнорирует эти предупреждения, и вы все равно можете использовать fastload.

Ответ 3

Если вы используете MS SQL Server, вы можете создать уникальный индекс в столбцах таблицы, которые должны быть уникальными (документально подтвержденный здесь)

CREATE UNIQUE [ CLUSTERED | NONCLUSTERED ] INDEX <index_name>
    ON Words ( word [ ASC | DESC ])

Укажите Clustered или NonClustered, в зависимости от вашего случая. Кроме того, если вы хотите, чтобы он отсортировался (чтобы ускорить поиск), укажите ASC или DESC для порядка сортировки.

Смотрите здесь, если вы хотите узнать больше об архитектуре индексов.

В противном случае вы можете использовать UNIQUE CONSTRAINTS как документально зарегистрированный здесь:

ALTER TABLE Words
ADD CONSTRAINT UniqueWord
UNIQUE (Word);

Ответ 4

У меня была аналогичная проблема, и именно так я ее решил

insert into Words
( selectWord , Fixword)
SELECT word,'theFixword'
FROM   OldWordsTable
WHERE 
(
    (word LIKE 'junk%') OR
     (word LIKE 'orSomthing') 

)
and word not in 
    (
        SELECT selectWord FROM words WHERE selectWord = word
    )

Ответ 5

в то время как единственное ограничение - это один из способов пойти, вы также можете использовать это для своей логики вставки: http://www.sqlteam.com/article/application-locks-or-mutexes-in-sql-server-2005

В принципе, вы не помещаете блокировки в таблицу ниже, поэтому не беспокоитесь о чтении в то время как ваши проверки существования будут выполнены нормально.

это мьютекс в sql-коде.

Ответ 6

Я не могу говорить о деталях MS SQL, но одна точка первичного ключа в SQL - это обеспечить уникальность. Таким образом, по определению в общих терминах SQL первичный ключ представляет собой одно или несколько полей, которые уникальны для таблицы. Хотя существуют разные способы обеспечения соблюдения этого поведения (замените старую запись на новую или отклоните новую), я был бы удивлен, если бы у MS SQL не было механизма для обеспечения соблюдения этого поведения и что это не было отклонить новую запись. Просто убедитесь, что вы установили первичный ключ в поле Word, и он должен работать.

Еще раз, однако, я отказываюсь от всего этого, исходя из моих знаний из программирования MySQL и моего класса баз данных, поэтому извиняюсь, если я учусь о тонкостях MS SQL.

Ответ 7

declare @Error int

begin transaction
  INSERT INTO Words (Word) values(@word)
  set @Error = @@ERROR
  if @Error <> 0 --if error is raised
  begin
      goto LogError
  end
commit transaction
goto ProcEnd

LogError:
rollback transaction