Поиск дубликатов в таблицах в MS SQL Server

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

Несмотря на то, что в реляционных БД таблица находиться в 1НФ (1 нормальная форма), это не избавляет от дубликатов, которые могут появляться из-за пользовательских действий, где системой не предусмотрено отслеживание уникальности данных либо практически невозможно, а отдано на рассмотрению пользователя, что из-за человеческого фактора может приводить к образованию в таблицах повторений.

Эти повторения на логическом уровне воспринимаются дубликатами, а на уровне БД это уникальные записи. Например, необходимо добавить новую компанию, например "Большой Слон", в список партнеров. При введении пользователь мог отвлечься и поставить знак пробела перед именем компании. Введя компанию, пользователь добавил данные о новом соглашении. Затем через какое-то время уже другому пользователю также нужно было добавить соглашение в базу для этой компании, но после поиска этой компании, на этот раз все введено было верно, совпадения не обнаружилось, и ему пришлось добавить ее еще раз. Получилось, что запись об одной и той же компании сохранена в базе 2 раза, хотя по сути это различные данные, а на деле 1-е соглашение утеряно, хотя и сохранено в базе.

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

Рассмотрим поиск дубликатов на следующих примерах:1. В БД data_base_1 в таблице company существуют записи " Большой Слон" и "Большой Слон"

Ниже представлен код для поиска повторов:

DECLARE @field_to_find as nvarchar(40) -- столбец для поиска повторов
DECLARE @table as nvarchar(40) -- таблицы, в которой искать
DECLARE @sql as nvarchar(255) -- переменная для sql-скриптаSET @table = 'dbo.company'
SET @field_to_find = 'company_name'-- при помощи функции LTRIM() удаляются все знаки пробела слева от фразы
set @sql = 'select * from ' + @table + ' where ' + @field_to_find
+ ' in (SELECT ' + 's1.' + @field_to_find
+ ' FROM ' + @table + ' s1 INNER JOIN ' + @table + ' s2 ON ' + 'LTRIM(s1.' + @field_to_find
+ ') = ' + 'LTRIM(s2.' + @field_to_find + ')'
+ ' GROUP BY ' + 's1.' + @field_to_find + ' HAVING COUNT(*)>1) order by ' + @field_to_find + ''exec (@sql)

С помощью функции LTRIM() удаляем все знаки пробела слева от выражения, тем самым приводя поля к общему виду.

2. В БД data_base_1 в таблице company существуют записи "Карандаш" и "кАРАНДАШ"

Поиск этих совпадений производится аналогично 1-му примеру, только без использования функции LTRIM().

3. В БД data_base_1 в таблице company существуют 2 записи "Сурок" и 2 записи "Глиссер", причем литеры "С" в одном случае являются кириллическим, а в другом латинскими.

В машинном восприятии это абсолютно разные слова, поэтому в данном случае не получится привести выражение к общему виду. Вместо этого будем искать выражения по их шаблонам, например, для слова "Сурок" это будет "_урок".

declare @sql as varchar(max); -- переменная для sql-скрипта
declare @table_to_find as nvarchar(40) -- таблицы, в которой искать
declare @table as table(company_name varchar(100)); -- временная таблица для выражений-дубликатов
declare @temp_name as varchar(200); -- переменная для временного хранения выражений-дубликатов
declare @field_to_find as nvarchar(40) -- столбец для поиска повторов

set @table_to_find = 'dbo.company'
set @field_to_find = 'company_name'
set @temp_name = '';

--поиск всех выражений содержащих латинские символы и преобразование их к шаблонному виду
set @sql = 'SELECT stuff(' + @field_to_find + ',
patindex(''%[A-z]%'', ' + @field_to_find + '),
len(' + @field_to_find + ') - patindex(''%[A-z]%'', reverse(' + @field_to_find + ')) + 1 - patindex(''%[A-z]%'', ' + @field_to_find + ') + 1,
replicate(''_'', len(' + @field_to_find + ') - patindex(''%[A-z]%'', reverse(' + @field_to_find + ')) + 1 - patindex(''%[A-z]%'', ' + @field_to_find + ') + 1))
from ' + @table_to_find + '
where ' + @field_to_find + ' like ''%[A-z]%''';insert @table exec(@sql);

set @sql = '';--поиск всех выражений удовлетворяющих условиям шаблона
DECLARE next_item CURSOR FOR select * from @table;
OPEN next_item;
FETCH NEXT FROM next_item into @temp_name;
WHILE (@@FETCH_STATUS = 0)
begin
set @sql = @sql + 'SELECT s1.id, s1.' + @field_to_find + ' FROM ' + @table_to_find + ' s1
INNER JOIN ' + @table_to_find + ' s2 ON s1.' + @field_to_find + ' like ''' + @temp_name +
''' GROUP BY s1.' + @field_to_find + ', s1.id HAVING COUNT(*)>1 UNION '; FETCH NEXT FROM next_item into @temp_name;
end

set @sql=left(@sql, len(@sql) - 6);
exec(@sql);

CLOSE next_item;
DEALLOCATE next_item;

Теперь зная id записей, базу данных можно привести к логическому виду. Основываясь на данном принципе, можно искать, таким образом, и другие выражения-дубликаты.