Delete Duplicate Rows In SQL Server Using Common Table Expression

How we can check and delete duplicate rows from a SQL server table using CTE (Common Table Expression)

It is a common exercise for all  developers to delete duplicate records from a SQL table for many reasons. One of the main reasons is Excel or CSV data provided by your client. So here are a few lines of code for all developers to detect and remove those extra records from the SQL server database table with ease. 

 

Let's take an example of a simple table with duplicate records.

 

Id	EmployeeName	Technology
1	Arkadeep	Sitecore
2	Ratul	.NET
3	Soumi	Azure
4	Arkadeep	Sitecore

 

Out of 4 records, 1 & 4 are duplicates. 

 

Now let's remove the duplicate using SQL queries. 

 

Let's say the table name is  [Account].[Users], so the query to remove the duplicate will be 

WITH tblTempEmp as
(
SELECT ROW_NUMBER() Over(PARTITION BY [EmployeeName], [Technology] ORDER BY Name)
   As RowNumber, * FROM [Account].[Users]
)
DELETE FROM tblTempEmp WHERE RowNumber >1

SELECT * FROM [Account].[Users]

After running the query, run a select query to get the records and you will get only 3 unique records.