How to Delete Duplicate Records in SQL Server

Usually on daily basis we usually need to perform removing duplicate records from a table. This post can help you to understand “How to Delete Duplicate Records in SQL Server”.

Here consider an example for removing duplicate records.

IF EXISTS(SELECT 1 FROM sys.sysobjects WHERE TYPE = 'U' AND NAME = 'Test')
BEGIN
	DROP TABLE Test; 
END;
GO
CREATE TABLE Test(id INT,name VARCHAR(100),age INT);
GO
INSERT INTO Test
VALUES (1,'A',34),
(1,'A',34),
(2,'B',37),
(3,'C',21),
(3,'C',21),
(3,'C',21);
GO
SELECT id,name,age FROM Test;

-- Now delete the duplicate records
WITH CTE(id,name,age,Duplicates)
AS
(
SELECT id,name,age,
ROW_NUMBER() OVER (PARTITION BY id, name, age ORDER BY id) AS Duplicates
FROM Test
)
DELETE FROM CTE
WHERE Duplicates > 1
GO

Now check the table to make sure duplicates are being removed from table.