Remove duplicate records from a table in
SQL Server
Sometimes we required to remove duplicate records from a table although table
has a UniqueID Column with identity. In this article, I would like to share a
best way to delete duplicate records from a table in SQL Server.
Suppose we have below Employee table in SQL Server.
CREATE
TABLE dbo.Employee
(
EmpID
int IDENTITY(1,1)
NOT NULL,
Name
varchar(55)
NULL,
Salary
decimal(10,
2) NULL,
Designation
varchar(20)
NULL
)
The data in this table is as shown below:
![Image-1.jpg]()
Remove Duplicate Records by using ROW_NUMBER()
WITH
TempEmp (Name,duplicateRecCount)
AS
(
SELECT
Name,ROW_NUMBER()
OVER(PARTITION
by Name, Salary
ORDER BY Name)
AS
duplicateRecCount
FROM
dbo.Employee
)
--Now
Delete Duplicate Records
DELETE
FROM TempEmp
WHERE
duplicateRecCount > 1
--See
affected table
Select
* from Employee
![Image-2.jpg]()