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.