Recently I was trying to find duplicate records in a table. The records are considered duplicate if two columns in different records has the same value. For example if first name and last name is same in record1 and record2, they considered as duplicate.

The below query will help to find the duplicate records.

SELECT fname, lname, COUNT(*) AS DuplicateCount FROM [Users]
GROUP BY fname, lname HAVING COUNT(*) > 1
ORDER BY COUNT(*), fname.

(I know this is pretty simple one, but most of the time you struggle for the simplest query :) so thought of sharing it)