Remove Duplicate Records From A Table In SQL Server

In this blog i am going to explain how to remove duplicate records from a table in SQL Server. Sometimes while you working with any database like My SQL, MS SQL, Oracal and many others it is required to remove duplicate records from a table. So in this blog i'll show a best way to delete duplicate records from table in Sql Server.
 
Now, Consider you have a table with following columns.
  1.  CREATE TABLE #Employee  
  2. (   
  3. EmpID int IDENTITY(1,1) NOT NULL,   
  4. Name varchar(55) NULL,   
  5. Salary decimal(10, 2) NULL,   
  6. Designation varchar(20) NULL  
  7.  )   
 The data in this table is as shown below,
 
 
 
You can see there is some duplicate records so we will delete all the duplicate records from table by using ROW_NUMBER() in Sql Server. So Write following SQl Query.
  1. WITH TempEmp (Name,duplicateRecCount)  
  2. AS  
  3. (  
  4.    SELECT Name,ROW_NUMBER() OVER(PARTITION by Name, Salary ORDER BY Name)   
  5.    AS duplicateRecCount  
  6.    FROM dbo.#Employee  
  7. )  
  8. --Now Delete Duplicate Records  
  9. DELETE FROM TempEmp  
  10. WHERE duplicateRecCount > 1   
Now, see affected table
  1. SELECT * FROM  #Employee  
 
 
Summary
 
In this article, I expose how to remove duplicate records from a table in SQL Server. I hope this blog will helpful to you. Please post your questions or comments about this blog.