How to eliminate duplicate records into result set in SQL? | Clear duplicate records from SQL server table | SQL drop duplicate in table

by

This article is about,

How to eliminate duplicate records into result set in SQL? or Clear duplicate records from SQL server table

As DBA we get requirement to clean up or remove duplicate records from SQL server database. This is required to free up some disk space or may be due to SQL server database performance improvement.

Best way to keep your SQL server table free from duplicate records is improve your database design. You can add Primary Key on table so that every record will have unique identity.

But in some requirement this is not possible and we have to keep SQL server table which will keep duplicate records. In such scenario if SQL database is highly transactional then SQL table size grows rapidaly and also increse disk space utilization.

In this case we have to do the duplicate records cleanup or SQL drop duplicate in table. Below Script will remove duplicate records from the SQL server table.

NOTE : Take database or Table backup before removing data from SQL Server table.

How do I delete duplicate rows in SQL? or How to eliminate duplicate records into result set in SQL?

Step 1: Created Sample table test using below script

CREATE TABLE contacts (
id INT PRIMARY KEY identity(1,1),
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(255) NOT NULL
);

Step 2: Inserted duplicate records in table using below SQL script

INSERT INTO testRemoveduplicate (first_name,last_name,email)
VALUES ('Carine ','Schmitt','carine.schmitt@verizon.net'),
('Jean','King','jean.king@me.com'),
('Peter','Ferguson','peter.ferguson@google.com'),
('Janine ','Labrune','janine.labrune@aol.com'),
('Jonas ','Bergulfsen','jonas.bergulfsen@mac.com'),
('Janine ','Labrune','janine.labrune@aol.com'),
('Susan','Nelson','susan.nelson@comcast.net'),
('Zbyszek ','Piestrzeniewicz','zbyszek.piestrzeniewicz@att.net'),
('Roland','Keitel','roland.keitel@yahoo.com'),
('Julie','Murphy','julie.murphy@yahoo.com'),
('Kwai','Lee','kwai.lee@google.com'),
('Jean','King','jean.king@me.com'),
('Susan','Nelson','susan.nelson@comcast.net'),
('Roland','Keitel','roland.keitel@yahoo.com');

Step 3 : Remove Duplicate records from SQL server table test.

NOTE: You need to modify script before executing on your environment. Get duplicate records column and change it in your script. In this script I have used first_name.

With CTE_Duplicates as

(select id,first_name,last_name,email, row_number() over(partition by first_name order by first_name ) rownumber from dbo.contacts )

delete from CTE_Duplicates where rownumber!=1

This script will delete all duplicate records from your table.

How To Create SQL Login on SQL Server Always ON | SQL User Creation on A sync Replica in Always ON

Leave a Comment