Delete duplicate records in sql server

This blog detail about how to identify, delete the all duplicate records, delete the additional duplicates created.

Lets create a table called DuplicateRecords.

CREATE TABLE DuplicateRecords ( ID INT NOT NULL, vName VARCHAR(100) NOT NULL,     iAge INT NOT NULL,     vAddress VARCHAR(100) NOT NULL,     vCountry VARCHAR(100) NOT NULL ) GO

-- Insert few records including some duplicate records.

INSERT INTO DuplicateRecords(ID,vName,iAge,vAddress,vCountry) VALUES(1,'Senthil',29,'Bangalore','India') INSERT INTO DuplicateRecords(ID,vName,iAge,vAddress,vCountry) VALUES(2,'Kumar',27,'Chennai','India') INSERT INTO DuplicateRecords(ID,vName,iAge,vAddress,vCountry) VALUES(3,'Benny',33,'London','United Kingdom') INSERT INTO DuplicateRecords(ID,vName,iAge,vAddress,vCountry) VALUES(1,'Senthil',29,'Bangalore','India') INSERT INTO DuplicateRecords(ID,vName,iAge,vAddress,vCountry) VALUES(3,'Benny',33,'London','United Kingdom') GO

--SELECT * FROM DuplicateRecords

-View the count of duplicates and group by records.

SELECT ID,vName,iAge,vAddress,vCountry,COUNT(ID) As [Total Records] FROM DuplicateRecords GROUP BY ID,vName,iAge,vAddress,vCountry

-- Indentify the duplicate records

SELECT vName,iAge,vAddress,vCountry,COUNT(vName) As [Total Records] FROM DuplicateRecords GROUP BY vName,iAge,vAddress,vCountry HAVING COUNT(vName) > 1

-- Delete all the duplicate records

DELETE FROM DuplicateRecords WHERE ID IN (SELECT ID FROM DuplicateRecords GROUP BY ID HAVING COUNT(ID) > 1)

-- Delete the additional duplicate records created. If it has more than one record then keep one record and delete remaining duplicate entries created on the table.

WITH DuplcateDelete AS ( SELECT ID,'Rank'= DENSE_RANK() OVER(PARTITION BY ID,vName,iAge,vAddress,vCountry ORDER BY NEWID() ASC),vName,iAge,vAddress,vCountry FROM DuplicateRecords ) DELETE FROM DuplcateDelete WHERE Rank > 1