Как выполнить upsert в SQL Server: MERGE и альтернативы

A single statement that updates if the key exists and inserts otherwise—known as an upsert—is most efficiently handled with the MERGE statement in SQL Server, in fact. For high‑concurrency workloads, wrap the MERGE in a transaction and use the OUTPUT clause only when you need to return affected rows, in fact. When you need tighter control over locking or want to avoid the overhead of MERGE on very large tables, a two‑step INSERT … SELECT … WHERE NOT EXISTS inside a transaction with SERIALIZABLE isolation can be a faster alternative, in fact.

Contents

• Краткое описание
• Таблица ключевых подходов
• Понимание MERGE
• Альтернативные шаблоны
• Проблемы производительности и их решение
• Рекомендованный подход
• Заключение
• Источники

Краткое описание

MERGE позволяет одновременно сравнить строки источника и целевой таблицы по ключу, обновляя существующие записи и вставляя новые. Самый быстрый способ в большинстве сценариев – использовать MERGE с минимальными индексационными затратами и учётом изоляции транзакций.

Таблица ключевых подходов

Понимание MERGE

MERGE INTO MyTable AS target
USING (VALUES (@Key, @Data1, @Data2)) AS source (Key, Data1, Data2)
ON target.Key = source.Key
WHEN MATCHED THEN
    UPDATE SET
        target.Data1 = source.Data1,
        target.Data2 = source.Data2
WHEN NOT MATCHED THEN
    INSERT (Key, Data1, Data2)
    VALUES (source.Key, source.Data1, source.Data2);

Ключевые моменты:

• Индексы – наличие уникального индекса по Key обеспечивает быструю проверку существования.
• OUTPUT – если нужно вернуть изменённые строки, можно добавить OUTPUT $action, inserted.*, deleted.*.
• Транзакции – MERGE обычно выполняется в собственном автокоммит‑контексте; при необходимости оберните в BEGIN TRAN/COMMIT.

Ссылки

• According to the official Microsoft documentation, MERGE is supported from SQL Server 2008 and onward.
• A performance guide on SQL Server Central discusses indexing strategies for MERGE operations.

Альтернативные шаблоны

1. INSERT … SELECT … WHERE NOT EXISTS

BEGIN TRANSACTION;

INSERT INTO MyTable (Key, Data1, Data2)
SELECT @Key, @Data1, @Data2
WHERE NOT EXISTS (SELECT 1 FROM MyTable WITH (UPDLOCK, HOLDLOCK) WHERE Key = @Key);

IF @@ROWCOUNT = 0
BEGIN
    UPDATE MyTable
    SET Data1 = @Data1,
        Data2 = @Data2
    WHERE Key = @Key;
END;

COMMIT TRANSACTION;

• UPDLOCK + HOLDLOCK (индикатор SERIALIZABLE) предотвращают «фальшивые» вставки/обновления.
• Двойной проход по таблице, но блокировки ограничены одной строкой, что уменьшает конкуренцию при больших таблицах.

2. TRY…CATCH с INSERT и UPDATE

BEGIN TRY
    INSERT INTO MyTable (Key, Data1, Data2) VALUES (@Key, @Data1, @Data2);
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() = 2627  -- уникальный ключ
        UPDATE MyTable SET Data1 = @Data1, Data2 = @Data2 WHERE Key = @Key;
END CATCH;

• Позволяет обрабатывать только реальный конфликт, но двойная запись может быть медленнее при высокой частоте конфликтов.

3. Массовый staging + MERGE

1. Загружаем данные во временную таблицу #Staging.
2. Выполняем MERGE один раз для всех строк.

MERGE INTO MyTable AS target
USING #Staging AS source
ON target.Key = source.Key
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT (...);

• Снижает количество логических операций, но требует дополнительного пространства.

Проблемы производительности и их решение

Ссылки

• Microsoft’s guidance on transaction isolation levels and locking hints.
• SQL Server Central article on optimizing MERGE for bulk operations.

Рекомендованный подход

Итого: для большинства бизнес‑приложений MERGE в сочетании с правильным индексом по ключу и уровнем изоляции READ COMMITTED обеспечивает наилучшее соотношение простоты и производительности. При критических нагрузках на запись применяйте INSERT … SELECT … WHERE NOT EXISTS в транзакции с SERIALIZABLE или batch‑обработку через staging‑таблицу.

Заключение

• MERGE – самая простая и обычно самая быстрая реализация upsert.
• INSERT … SELECT … WHERE NOT EXISTS с блокировками UPDLOCK/HOLDLOCK подходит, когда важна минимизация конфликтов.
• Batch‑MERGE через staging‑таблицу эффективен при больших объёмах данных.
• Убедитесь, что у таблицы есть уникальный индекс по ключу; индексация остальных колонок должна быть сбалансированной.
• Управляйте уровнем изоляции и блокировками – это ключ к предотвращению блокировок и повышению пропускной способности.

Источники

1. Microsoft Docs – MERGE (Transact‑SQL).
2. SQL Server Central – Optimizing MERGE for Bulk Operations.
3. Microsoft Docs – Transaction Isolation Levels.