ErcanOPAK.com

First, Let’s have the table and its records:

DECLARE @TestTable TABLE (ID int, name varchar(10), email varchar(50))

INSERT @TestTable VALUES (1,'Zidane','1@abc.com')
INSERT @TestTable VALUES (2,'Henry','1@abc.com')
INSERT @TestTable VALUES (3,'Ronald','1@abc.com')
INSERT @TestTable VALUES (4,'Messi','2@abc.com')
INSERT @TestTable VALUES (5,'Ronaldo','1@abc.com')
INSERT @TestTable VALUES (6,'Henry','1@abc.com')

It’s easy to find duplicates with one field:

SELECT name, COUNT(email) 
FROM @TestTable
GROUP BY email
HAVING COUNT(email) > 1

If we need to find duplicate records regarding both columns (name and email):

SELECT
    name,email, COUNT(*) AS CountOf
    FROM @YourTable
    GROUP BY name,email
    HAVING COUNT(*)>1

OUTPUT:

name       email       CountOf
---------- ----------- -----------
Henry      1@abc.com   2
Ronaldo    1@abc.com   2

(2 row(s) affected)