Find Duplicate Fields in a Table

A common scenario when querying tables is the need to find duplicate fields within the same table. To do this is simple; it requires utilizing the GROUP BY clause and counting the number of recurrences. For example, let’s take a employee table. Within the customers table, we want to find all the records where the Employee Names are the same. We also want to find which Employee Names are the same and count them.

Use below sql script

SELECT EmpName     ,DuplicateCount = COUNT(1) FROM [Emp] GROUP BY EmpName HAVING COUNT(1) > 1  -- more than one value ORDER BY COUNT(1) DESC