Delete Duplicate Rows in SQL Server using Common Table Expression

Introduction:

Sometimes, some duplicate data may be inserted in the SQL table. But it's harmful to you and your application too. So how do you remove the duplicate rows in your table?

Description

Take a look at the following table content. It has duplicate rows.

Id	Name	Department
1	Arka	.NET
2	Ashok	.NET
3	Anubhav	PHP
4	Arka	.NET
5	Sudeshna	Graphics

Row 1 and 4 are duplicates. So we have to remove one row and keep one in the table. Now the question is how to do this?

Using the Code

To solve this problem, take a look at the following code. This will solve this problem.

WITH tblTemp as

(

SELECT ROW_NUMBER() Over(PARTITION BY Name,Department ORDER BY Name)

   As RowNumber,* FROM <table_name>

)

DELETE FROM tblTemp where RowNumber >1

After running, this code shows the table data, it will ensure that all unique rows are present.