Удаление дублей с сохранением правильной записи

Сложность удаления дублей не в синтаксисе DELETE. Нужно определить одинаковую бизнес-сущность, сохраняемую информацию и корректность ссылок. Две строки с одной электронной почтой не обязательно обозначают одного клиента. Наибольшее значение identity также не делает запись автоматически самой достоверной.

Равенство и выбор сохраняемой строки

Опишите ключ дубля как явное бизнес-правило. При необходимости включите арендатора и систему-источник. Уточните значение регистра, акцентов, пробелов и отсутствующих полей. Сравнения следуют правилам сортировки. Объединение NULL может быть неправильным, если это неизвестная идентичность, а не одна общая сущность.

Выберите детерминированное правило. В упражнении ключ состоит из TenantId и ExternalKey, выигрывает последнее наблюдение, а RowId разрешает совпадение времени. Последний уникальный критерий необходим: без него разные выполнения могут сохранить разные строки с одинаковым временем.

Строки без ExternalKey намеренно исключены. Каждая неизвестная запись остаётся самостоятельной до отдельного сопоставления. Это допустимая политика, но не универсальный смысл NULL.

CREATE TABLE #CustomerStage
(RowId int PRIMARY KEY, TenantId int NOT NULL,
 ExternalKey varchar(20) NULL, SeenAt datetime2(0) NOT NULL);
INSERT #CustomerStage VALUES
(1,1,'A','2020-01-01'),(2,1,'A','2020-02-01'),
(3,2,'A','2020-01-01'),(4,1,NULL,'2020-01-01'),
(5,1,NULL,'2020-02-01');

;WITH ranked AS
( SELECT *, ROW_NUMBER() OVER
  (PARTITION BY TenantId, ExternalKey
   ORDER BY SeenAt DESC, RowId DESC) AS rn
  FROM #CustomerStage WHERE ExternalKey IS NOT NULL )
SELECT * FROM ranked WHERE rn > 1 ORDER BY RowId;

Ожидаемый кандидат на удаление имеет RowId 1. Строка 2 новее для арендатора 1 и ключа A. Строка 3 относится к другому арендатору, а 4 и 5 имеют неизвестный внешний ключ. Согласуйте эти результаты с владельцем данных до настоящей записи.

Контролируемый переход к удалению

Следующий пример безопасен как упражнение: он меняет только временную таблицу и откатывает удаление. OUTPUT показывает фактически удалённые строки. После rollback исходные данные остаются целыми. Для производственной таблицы сначала проверьте правило, количество, восстановление и стратегию конкурентной записи.

BEGIN TRAN;
;WITH ranked AS
( SELECT *, ROW_NUMBER() OVER
  (PARTITION BY TenantId, ExternalKey
   ORDER BY SeenAt DESC, RowId DESC) AS rn
  FROM #CustomerStage WHERE ExternalKey IS NOT NULL )
DELETE FROM ranked
OUTPUT deleted.RowId, deleted.TenantId, deleted.ExternalKey
WHERE rn > 1;
ROLLBACK;

Предварительная выборка, сделанная несколько часов назад, не гарантирует тот же результат DELETE. Могут появиться записи и измениться время наблюдения. Используйте контролируемое окно или продуманную изоляцию, охватывающую выбор, перенос ссылок, удаление и защиту уникальности. Большая serializable-транзакция способна сильно блокировать приложение и не является бесплатным решением.

До удаления родителей найдите внешние ключи и отношения без объявленных ограничений. Если дочерние записи ссылаются на разные идентификаторы дублей, создайте отображение удаляемого ключа в сохраняемый и перенесите ссылки по бизнес-правилам. После переноса дочерние записи могут столкнуться по собственному уникальному ключу. Это требует решения, а не отключения ограничений.

Сохранение смысла и защита от повторения

Новая строка может иметь пустой адрес, а старая проверенный. Простой выбор победителя потеряет полезную информацию. Сначала объедините поля по документированному приоритету, сохранив происхождение там, где оно необходимо. Итоговая запись может быть консолидацией, а не неизменённой исходной строкой.

Храните проверяемое отображение и достаточно удалённых данных в течение согласованного срока восстановления. Резервная копия обязательна, но может быть неудобна для возврата нескольких связей после последующих законных изменений. Запись восстановления должна содержать исходные ключи и выполненное преобразование с подходящей защитой персональных данных.

После очистки обеспечьте реальную уникальность ограничением или подходящим фильтрованным уникальным индексом. Защита должна соответствовать выбранной обработке неизвестных ключей. Исправьте также гонку загрузки или отсутствие идемпотентности, создавшее дубли. Постоянная очистка не заменяет надёжную архитектуру приёма данных.

Проверьте количества по арендаторам, значения сохранённых полей, дочерние ссылки и повтор проблемного импорта. Включите одинаковые времена, пустые атрибуты и неизвестные ключи. Отдельно проверьте восстановление одной удалённой связи в копии базы после новых изменений: процедура не должна уничтожать законные записи, появившиеся позднее. Такой тест выявляет недостаточный состав журнала преобразований. Успешная очистка удаляет только подтверждённые дубли, сохраняет информацию и отклоняет следующий дубль сразу, не ожидая нового задания обслуживания.

Техническая документация: Microsoft Learn: ROW_NUMBER · Microsoft Learn: Unique indexes.