Ways to Find and Deleting Duplicate Records in SQL Server

Ways to find and deleting duplicate records in SQL Server.

Consider table emp
Column: ID,C1,C2

1. WE ARE CONSIDER IF ID AS IDENTITY COLUMN

DELETE FROM EMP WHERE ID NOT IN (SELECT MAX(ID) FROM EMP GROUP BY C1 ,C2)

2. USING ROW_NUMBER()

Consider table emp
Column: ID,name

DELETE EMP_T FROM (SELECT ROW_NUMBER() OVER PARTATION BY (ID,NAME) ORDER BY ID) AS ROWID FROM EMP) EMP_T
WHERE EMP_T.ROWID >1

3. USING CTE

WITH CTE AS {

SELECT ID,NAME , ROW_NUMBER() OVER (PARTATION BY ID,NAME )AS RANK FROM EMP)
}
DELTE FROM CTE WHERE RANK>1