При разработке объектов в SQL Server мы должны следовать определенным передовым методам. Например, таблица должна иметь первичные ключи, столбцы идентификаторов, кластеризованные и некластеризованные индексы, целостность данных и ограничения производительности. Таблица SQL Server не должна содержать повторяющихся строк в соответствии с лучшими практиками проектирования баз данных. Однако иногда нам нужно иметь дело с базами данных, в которых эти правила не соблюдаются или где возможны исключения, когда эти правила намеренно игнорируются. Несмотря на то, что мы следуем лучшим практикам, мы можем столкнуться с такими проблемами, как повторяющиеся строки.
Например, мы также можем получить этот тип данных при импорте промежуточных таблиц, и мы хотели бы удалить избыточные строки перед фактическим добавлением их в производственные таблицы. Более того, мы не должны оставлять перспективу дублирования строк, потому что дублирующаяся информация позволяет многократно обрабатывать запросы, неверные результаты отчетов и многое другое. Однако, если у нас уже есть повторяющиеся строки в столбце, нам нужно следовать определенным методам для очистки повторяющихся данных. Давайте рассмотрим в этой статье некоторые способы устранения дублирования данных.
Как удалить повторяющиеся строки из таблицы SQL Server?
В SQL Server есть несколько способов обработки повторяющихся записей в таблице на основе конкретных такие обстоятельства, как:
Удаление повторяющихся строк из таблицы уникального индекса SQL Server
Вы можете использовать индекс для классификации повторяющихся данных в уникальных индексных таблицах, а затем удалить дубликаты записи. Сначала нам нужно создать базу данных с именем «test_database», а затем создать таблицу «Сотрудник» с уникальным индексом, используя приведенный ниже код.
USE masterGOCREATE DATABASE test_databaseGOUSE [test_database] GOCREATE TABLE Employee ([ID] INT NOT NULL IDENTITY (1,1), [Dep_ID] INT, [Name] varchar (200), [email] varchar (250) NULL, [city] varchar (250) NULL, [адрес] varchar ( 500) NULLCONSTRAINT Primary_Key_ID PRIMARY KEY (ID))
Результат будет таким, как показано ниже.
Теперь вставьте данные в таблицу. Мы также вставим повторяющиеся строки. «Dep_ID» 003 005 и 006 – это повторяющиеся строки с аналогичными данными во всех полях, кроме столбца идентификаторов с уникальным индексом ключа. Выполните приведенный ниже код.
ИСПОЛЬЗУЙТЕ [test_database] GOINSERT INTO Сотрудник (Dep_ID, имя, электронная почта, город, адрес) ЗНАЧЕНИЯ (001, 'Aaaronboy Gutierrez','aronboy.gutierrez@gmail.com ',' HILLSBORO ',' 5840 Ne Cornell Rd Hillsboro Or 97124 '), (002,' Aabdi Maghsoudi ',' abdi_maghsoudi@gmail.com ',' BRENTWOOD ',' 987400 Nebraska Medical Center Omaha Ne 681987400 '), (003 , 'Aabharana, Sahni', 'abharana.sahni@gmail.com', 'HYATTSVILLE', '2 Barlo Circle Suite A Dillsburg Pa 170191'), (003, 'Aabharana, Sahni','abharana.sahni@gmail. com ',' HYATTSVILLE ',' 2 Barlo Circle Suite A Dillsburg Pa 170191 '), (004,' Aabish Mughal ',' abish_mughal@gmail.com ',' OMAHA ',' 2975 Crouse Lane Burlington NC 272150000 '), ( 005, 'Aabram Howell', 'aronboy.gutierrez@gmail.com', 'DILLSBURG', '868 York Ave Atlanta Ga 303102750'), (005, 'Aabram Howell', 'aronboy.gutierrez@gmail.com', ' DILLSBURG ',' 868 York Ave Atlanta Ga 303102750 '), (006,' Humbaerto Acevedo ',' humbaerto.acevedo@gmail.com ',' SAINT PAUL ',' 895 E 7th St Saint Paul Mn 551063852 '), (006 , 'Humbaerto Acevedo', 'humbaerto.acevedo@gmail.com', 'SAINT PAUL', '895 E 7th St Paul Mn 551063852'), (007, 'Pilar Ackaerman', 'pilar.ackaerman@gmail.com' , 'ATLANTA', '5813 Eastern Ave Hyattsville Md 207822201'); SELECT * FROM Employee
Результат будет следующим.
Теперь найдите количество строк в таблице, выполнив следующий код. Функция count (*) подсчитает количество строк.
ВЫБРАТЬ Dep_ID, имя, адрес электронной почты, город, адрес, COUNT (*) AS duplicate_rows_count FROM EmployeeGROUP BY Dep_ID, Name, email, city, address
Результат будет таким, как показано ниже. Строки № (3, 4), (6, 7), (8, 9), выделенные в красном поле, являются дубликатами.
Наша задача – обеспечить уникальность, удалив дубликаты повторяющихся столбцов. Немного проще удалить повторяющиеся значения из таблицы с уникальным индексом, чем удалить строки из таблицы без него. Ниже приведены два метода достижения этого. Первый метод дает вам повторяющиеся строки из таблицы с помощью функции «row_number ()», тогда как второй метод использует функцию «NOT IN». Эти два метода имеют свою собственную стоимость, которая будет обсуждаться позже.
Метод 1: Выбор повторяющихся записей с помощью функции «ROW_NUMBER ()»
select * from (SELECTDep_ID, Имя, адрес электронной почты, город, адрес, ROW_NUMBER () OVER (РАЗДЕЛ ПО Dep_ID, имя, электронная почта, город, адрес ORDER BY Dep_ID, Name, email, city, address) row_no FROM test_database.dbo.Employee) x, где row_no> 1
Метод 2: Выбор повторяющихся записей с помощью функции «NOT IN ()»
SELECT * FROM test_database.dbo.EmployeeWHERE ID NOT IN (SELECT MAX (ID) FROM test_database .dbo.EmployeeGROUP BY Dep_ID, Name, email, city, address)
Выполните приведенный выше код, и вы увидите следующий результат. Оба метода дают одинаковый результат, но имеют разную стоимость.
Теперь мы удалим выбранное выше дублировать строки с помощью CTE, используя следующий код. Следующий код выбирает повторяющиеся строки для удаления с помощью функции «ROW_NUMBER ()»..
Метод 1: Удаление повторяющихся записей с помощью функции «ROW_NUMBER ()»
С cte_delete AS (SELECTDep_ID, имя, электронная почта, город, адрес, ROW_NUMBER () OVER ( РАЗДЕЛЕНИЕ ПО Dep_ID, имени, электронной почте, городу, адресу ORER BY Dep_ID, Name, email, city, address) row_noFROM test_database.dbo.Employee) DELETE FROM cte_delete WHERE row_no> 1;
Результатом будет как показано ниже.
Метод 2: Удаление повторяющихся записей с помощью функции «НЕ В ()»
Теперь, чтобы протестировать другой метод, нам нужно усечь таблицу, что приведет к удалению всех строк из таблицы. Затем команда вставки добавит значения в таблицу. Выполните следующий код прямо сейчас.
USE [test_database] GOtruncate table test_database.dbo.EmployeeINSERT INTO Employee (Dep_ID, Name, email, city, address) VALUES (001, 'Aaaronboy Gutierrez', 'aronboy .gutierrez @ gmail.com ',' HILLSBORO ',' 5840 Ne Cornell Rd Hillsboro Or 97124 '), (002,' Aabdi Maghsoudi ',' abdi_maghsoudi@gmail.com ',' BRENTWOOD ',' 987400 Nebraska Medical Center Omaha Ne 681987400 '), (003,' Aabharana, Sahni ',' abharana.sahni@gmail.com ',' HYATTSVILLE ',' 2 Barlo Circle Suite A Dillsburg Pa 170191 '), (003,' Aabharana, Sahni ',' abharana .sahni @ gmail.com ',' HYATTSVILLE ',' 2 Barlo Circle Suite A Dillsburg Pa 170191 '), (004,' Aabish Mughal ',' abish_mughal@gmail.com ',' OMAHA ',' 2975 Crouse Lane Burlington NC 272150000 '), (005,' Aabram Howell ',' aronboy.gutierrez@gmail.com ',' DILLSBURG ',' 868 York Ave Atlanta Ga 303102750 '), (005,' Aabram Howell ','aronboy.gutierrez@gmail .com ',' DILLSBURG ',' 868 York Ave Atlanta Ga 303102750 '), (006,' Humbaerto Acevedo ',' humbaerto.acevedo@gmail.com ',' SAINT PAUL ',' 895 E 7 th St Saint Paul Mn 551063852 '), (006,' Humbaerto Acevedo ',' humbaerto.acevedo@gmail.com ',' SAINT PAUL ',' 895 E 7th St Saint Paul Mn 551063852 '), (007,' Pilar Ackaerman ',' pilar.ackaerman@gmail.com ',' ATLANTA ',' 5813 Eastern Ave Hyattsville Md 207822201 '); ВЫБРАТЬ * ОТ сотрудника
Результат будет таким, как показано ниже.
Выполните приведенный ниже код, чтобы удалить все повторяющиеся строки из таблицы «Сотрудник».
Удалить FROM test_database.dbo.EmployeeWHERE ID NOT IN (SELECT MAX (ID) FROM test_database. dbo.EmployeeGROUP BY Dep_ID, Name, email, city, address)
Результат будет следующим.
План выполнения и стоимость запроса для удаления повторяющихся строк из индексированной таблицы:
Теперь мы должны проверить, какой метод будет рентабельным и потребует меньше ресурсов. Выберите код и щелкните план выполнения.. Появится следующий экран, показывающий все планы выполнения вместе с процентной стоимостью.
Мы видим, что метод 1 «удаление повторяющихся записей с помощью функции« ROW_NUMBER () »имеет стоимость 33%, а метод 2« удаление » дублирование записей с использованием функции NOT IN () »имеет 67% стоимости. Таким образом, первый метод является наиболее экономичным по сравнению со вторым.
Удаление дубликатов из SQL Таблица сервера без уникального индекса:
Немного сложнее удалить повторяющиеся строки или таблицы без уникального индекса. В этом сценарии использование общего табличного выражения (CTE) и функции ROW NUMBER () помогает нам удалить повторяющиеся записи. Чтобы удалить дубликаты из таблицы без уникального индекса, нам нужно сгенерировать уникальные идентификаторы строк.
Выполните следующий код, чтобы создать таблицу без уникального индекса.
ИСПОЛЬЗУЙТЕ [test_database] GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo]. [Employee_with_out_index] ([Dep_ID] [int] NULL, [Имя] [varchar] (200) NULL, [электронная почта] [varchar] (250) NULL, [ city] [varchar] (250) NULL, [адрес] [varchar] (500) NULL,) GO
Результат будет следующим.
Теперь вставьте записи в созданную таблицу с именем «Employee_with_out_index», выполнив следующий код.
USE [test_database] GOINSERT INTO Employee_with_out_index (Dep_ID, имя, адрес электронной почты, город, адрес) ЗНАЧЕНИЯ (001, 'Aaaronboy Gutierrez', 'aronboy.gutierrez@gmail.com', 'HILLSBORO', '5840 Ne Cornell Rd Hillsboro Or 97124'), (002, 'Aabdi Maghsoudi', 'abdi_maghsoudi@gmail.com', 'BRENTWOOD', '987400 Nebraska Medical Center Omaha Ne 681987400'), (003, 'Aabhara na, Sahni ',' abharana.sahni@gmail.com ',' HYATTSVILLE ',' 2 Barlo Circle Suite A Dillsburg Pa 170191 '), (003,' Aabharana, Sahni ',' abharana.sahni@gmail.com ', 'HYATTSVILLE', '2 Barlo Circle Suite A Dillsburg Pa 170191'), (004, 'Aabish Mughal', 'abish_mughal@gmail.com', 'OMAHA', '2975 Crouse Lane Burlington NC 272150000'), (005, ' Aabram Howell ',' aronboy.gutierrez@gmail.com ',' DILLSBURG ',' 868 York Ave Atlanta Ga 303102750 '), (005,' Aabram Howell ',' aronboy.gutierrez@gmail.com ',' DILLSBURG ', '868 York Ave Atlanta Ga 303102750'), (006, 'Humbaerto Acevedo', 'humbaerto.acevedo@gmail.com', 'SAINT PAUL', '895 E 7th St Saint Paul Mn 551063852'), (006, 'Humbaerto Acevedo ',' humbaerto.acevedo@gmail.com ',' SAINT PAUL ',' 895 E 7th Saint Paul Mn 551063852 '), (007,' Pilar Ackaerman ',' pilar.ackaerman@gmail.com ',' ATLANTA ',' 5813 Eastern Ave Hyattsville Md 207822201 '); SELECT * FROM Employee_with_out_index
Результат будет следующим.
Метод 1: Удаление повторяющихся строк из таблицы с помощью функции «ROW_NUMBER ()» и JOINS.
Выполните следующий код, который использует функцию ROW_NUMBER () и JOIN для удаления повторяющихся строк из таблицы без индекса. Сначала ИТ-служба создает уникальный идентификатор для присвоения row_no всем строкам и сохраняет только одну строку, удаляя повторяющиеся.
WITH temp_tablr_with_row_ids AS (SELECT ROW_NUMBER () OVER (ORDER BY Dep_ID, Name, email, city, address) AS row_no, Dep_ID, Name, email, city, addressFROM test_database.dbo.Employee_with_out_index) УДАЛИТЬ ОТ temp_tablr_with_row_ids aWHERE row_noРезультат будет выглядит следующим образом.
Метод 2: Удаление повторяющихся строк из таблицы с помощью «ROW_NUMBER ()» function и PARTITION BY.
Теперь в этом методе мы используем функцию ROW_NUMBER вместе с предложением partition by, чтобы присвоить row_no всем строкам, а затем удалить повторяющиеся. Прежде всего, нам нужно усечь ту же таблицу, которую мы создали ранее, чтобы все данные были удалены из таблицы. Затем вставьте записи в таблицу, включая повторяющиеся записи. Третий запрос удалит повторяющиеся строки из таблицы с именем «Employee_with_out_index».
усечь таблицу Employee_with_out_indexINSERT INTO Employee_with_out_index (Dep_ID, Name, email, city, address) VALUES (001, 'Aaaron', 'Aaaron', 'Aaaron', 'Aaaron', 'Gutierrez' aronboy.gutierrez@gmail.com ',' HILLSBORO ',' 5840 Ne Cornell Rd Hillsboro Or 97124 '), (002,' Aabdi Maghsoudi ',' abdi_maghsoudi@gmail.com ',' BRENTWOOD ',' 987400 Небраска Медицинский центр Омаха Ne 681987400 '), (003,' Aabharana, Sahni ',' abharana.sahni@gmail.com ',' HYATTSVILLE ',' 2 Barlo Circle Suite A Dillsburg Pa 170191 '), (003,' Aabharana, Sahni ',' abharana.sahni@gmail.com ',' HYATTSVILLE ',' 2 Barlo Circle Suite A Dillsburg Pa 170191 '), (004,' Aabish Mughal ',' abish_mughal@gmail.com ',' OMAHA ',' 2975 Crouse Lane Burlington Nc 272150000 '), (005,' Aabram Howell ',' aronboy.gutierrez@gmail.com ',' DILLSBURG ',' 868 York Ave Atlanta Ga 303102750 '), (005,' Aabram Howell ',' aronboy.gutierrez @ gmail.com ',' DILLSBURG ',' 868 York Ave Atlanta Ga 303102750 '), (006,' Humbaerto Acevedo ',' humbaerto.acevedo@gmail.com ',' SAINT PAUL ',' 895 E 7th St Saint Paul Mn 551063852 '), (006,' Humbaerto Acevedo ',' humbaerto.acevedo@gmail.com ',' SAINT PAUL ',' 895 E 7th St Saint Paul Mn 551063852 '), (007,' Pilar Ackaerman ','pilar.ackaerman@gmail. com ',' ATLANTA ',' 5813 Eastern Ave Hyattsville Md 207822201 ');Выбор повторяющихся записей во временной таблице
; WITH temp_tablr_with_row_ids AS (SELECT ROW_NUMBER () OVER (PARTITION BY Dep_ID, Name, email, city, addressORDER BY Dep_ID, Name, email, city, address) AS row_no, Dep_ID, Name, email, city, addressFROM Employee_with_out_index)Удаление повторяющихся записей из временной таблицы
DELETE a FROM temp_tablr_with_row_ids a WHERE row_no> 1Результат будет следующим.
Кроме того, нам нужно знать о затратах на выполнение запроса, чтобы понять, какое из них является оптимизированным решением. Итак, вам нужно выбрать все соответствующие запросы и нажать на план выполнения. На изображении ниже показан план выполнения запросов вместе со стоимостью выполнения. Запросы на удаление выделены красным окном. Первый запрос, который использует «ROW_NUMBER ()» и предложение JOIN, имеет стоимость выполнения 56%, тогда как второй запрос использует «ROW_NUMBER ()» и «PARTITION BY» имеет 31% затрат. Таким образом, второй метод является более оптимизированным, и мы должны следовать оптимизированному решению.
