How to Remove/Delete duplicate records/Rows from a table in SQL Server

Hi friends, in this article i will explain about How to remove duplicate rows in SQL Server 2008.
I already explained in the previous articles about How to Remove Special Characters in a String , STUFF and PATINDEX Examples,How to alter primary key column for existing table in sql server 2008 and How to Get List of Stored Procedures based on table name search, using sql server 2008?  

Create a table as shown below.

CREATE TABLE Employee (    Emp_ID int,    Emp_Name VARCHAR(50) )

Insert the rows as shown below.

INSERT INTO Employee VALUES(1,'Kishore') INSERT INTO Employee VALUES(1,'Kishore') INSERT INTO Employee VALUES(2,'Lakshmi') INSERT INTO Employee VALUES(2,'Lakshmi') INSERT INTO Employee VALUES(3,'Shiva') INSERT INTO Employee VALUES(3,'Shiva') INSERT INTO Employee VALUES(4,'Balaji') INSERT INTO Employee VALUES(4,'Balaji') INSERT INTO Employee VALUES(5,'Durga') INSERT INTO Employee VALUES(5,'Durga')

Run Select * from Employee then the output as shown below figure.

How to get the duplicate records:

WITH CTE(Row_number,Emp_ID,EMP_Name) AS ( SELECT ROW_NUMBER() OVER(PARTITION by Emp_ID,EMP_Name ORDER BY Emp_ID) as Row_number,Emp_ID,EMP_Name from Employee ) SELECT * from CTE

Run the above query.If Row_number is greater than 1 then that record is a duplicate record.
The output of the above query as shown in the below figure.

DELETE the rows by run the below Query.

WITH CTE(Row_number,Emp_ID,EMP_Name) AS ( SELECT ROW_NUMBER() OVER(PARTITION by Emp_ID,EMP_Name ORDER BY Emp_ID) AS Row_number,Emp_ID,EMP_Name FROM Employee ) DELETE FROM CTE WHERE ROW_NUMBER>1 SELECT * FROM Employee

When you the above query you will get the unique records.The output of the above query as shown in the below figure.