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