Delete Duplicate Records Or Rows - Sql Server

Delete/Remove duplicate records from MS Sql Server      

  we have 3 methods to do this

like we have table emp with column fname,dept

 table:  emp

fname           dept 

aditya           mca

abhishek       mca

akhilesh        mca

anand           mba

ram niwas     mba

aditya          mca

abhishek      mca

arbind          btech

(aditya mca) & (abhishek mca)  rows are deuplicates in above table so we have three methods to remove duplicacy

Method 1:- 

by using Identity Column

step1.   create a identity column in our table

alter table emp add id int identity(1,1) 

step 2.  write this query 

delete from emp 
where id not in  (select min(id)

from  emp  group by fname,dept)

Method 2:-

Delete duplicate records using Row_Number()

 If you do not want to make any changes in table design or don't want to create identity column on table then you can remove duplicate records using Row_Number in sql server 2005 onwards.

WITH DuplicateRecords AS

(

SELECT *,row_number() OVER(PARTITION BY fname,dept ORDER BY fname 
)

AS RowNumber FROMemp

)

DELETE FROM DuplicateRecords WHERE RowNumber>1

  

Method 3:-

Remove duplicate rows/Records using temporary table

by using distinct

SELECT distinct * into TempTable FROM emp

GROUP BY fname,dept

HAVING COUNT(fname) > 1

DELETE emp WHERE fname

IN (SELECT fname FROM TempTable)

INSERT emp SELECT * FROM TempTable

DROP TABLE TempTable