Duplicate Records in SQL Server

see the record without duplicate value

select name,emp_id,City from MyTable

Count Duplicate Records

Syntax:

SELECT YourColumn, COUNT(*) TotalCount

FROM YourTable

GROUP BY YourColumn

HAVING COUNT(*) > 1

ORDER BY COUNT(*) DESC

Example:

SELECT name, 

 COUNT(email) 

FROM users

GROUP BY email

HAVING ( COUNT(email) > 1 )

Delete Duplicate Records – Rows multipul column

DELETE

FROM MyTable

WHERE ID NOT IN

(

SELECT MAX(ID)

FROM MyTable

GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3 HAVING COUNT(*)>1))

Delete Duplicate Records – Rows Single column

DELETE

FROM MyTable

WHERE ID NOT IN

(

SELECT MAX(ID)

FROM MyTable

GROUP BY DuplicateColumn HAVING COUNT(*)>1))