Delete Duplicate Rows In SQL Server Using 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.