Delete duplicate rows in SQL Server

Introduction

Some time some duplicate data may inserted in the SQL table. But its harmful for you and your application too. So how to remove the duplicate rows in your table.

Description

Take a look in the following table content. Its having duplicate rows.

Row 1,2,3 and 4 are duplicate. So we have to remove one row and keep one in the table.

Now question is how to do?

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 emp

)

DELETE FROM tblTemp where RowNumber >1

After run these code show the table data, it will all unique rows present.