Delete Duplicates in MS SQL Server

Duplicate values in any table might be due to the poor table design or unwanted data from other sources. To delete the duplicate data from the table in SQL Server, follow the below steps –

1. Find duplicate rows.
2. Use DELETE statement to remove the duplicate rows.

Let us create a table named Geek –

CREATE TABLE Geek(
Name NVARCHAR(100) NOT NULL,
Email NVARCHAR(255) NOT NULL,
City NVARCHAR(100) NOT NULL);

Let us insert some values into the table Geek –

INSERT INTO Geek (Name, Email, City) VALUES
('Nisha', 'nisha@gfg.com', 'Delhi'),
('Megha', 'megha@gfg.com', 'Noida'),
('Khushi', 'khushi@gfg.com', 'Jaipur'),
('Khushi', 'khushi@gfg.com', 'Jaipur'),
('Khushi', 'khushi@gfg.com', 'Jaipur'),
('Hina', 'hina@gfg.com', 'Kanpur'),
('Hina', 'hina@gfg.com', 'Kanpur'),
('Misha', 'misha@gfg.com', 'Gurugram'),
('Misha', 'misha@gfg.com', 'Gurugram'),
('Neha', 'neha@gfg.com', 'Pilani');

Let us display the contents of table Geek –

SELECT * 
FROM Geek;

Table – Geek

Name	Email	City
Nisha	nisha@gfg.com	Delhi
Megha	megha@gfg.com	Noida
Khushi	khushi@gfg.com	Jaipur
Khushi	khushi@gfg.com	Jaipur
Khushi	khushi@gfg.com	Jaipur
Hina	hina@gfg.com	Kanpur
Hina	hina@gfg.com	Kanpur
Hina	hina@gfg.com	Kanpur
Misha	misha@gfg.com	Gurugram
Misha	misha@gfg.com	Gurugram
Neha	neha@gfg.com	Pilani

SQL Server query to delete duplicate records from the table Geek :

WITH CTE AS (
SELECT Name, Email, City
ROW_NUMBER() OVER (
PARTITION BY Name, Email. City
ORDER BY Name, Email. City
) row_num
FROM Geek
)
DELETE FROM CTE
WHERE row_num > 1;

Output –
(5 rows affected)

SELECT * 
FROM Geek;

Table – Geek

Name	Email	City
Nisha	nisha@gfg.com	Delhi
Megha	megha@gfg.com	Noida
Khushi	khushi@gfg.com	Jaipur
Hina	hina@gfg.com	Kanpur
Misha	misha@gfg.com	Gurugram
Neha	neha@gfg.com	Pilani