How to find duplicate record in the table in MS Sql.

First we create a table without having any primery key.

Create table ExamResult(name varchar(50),Subject varchar(20),Marks int)

Now insert some dummy data into this table.

I have inserted 10 records in this table.

insert into ExamResult values('Adam','Maths',70)

insert into ExamResult values ('Adam','Science',80)

insert into ExamResult values ('Adam','Social',60)

insert into ExamResult values('Rak','Maths',60)

insert into ExamResult values ('Rak','Science',50)

insert into ExamResult values ('Rak','Social',70)

insert into ExamResult values('Sam','Maths',90)

insert into ExamResult values ('Sam','Science',90)

insert into ExamResult values ('Sam','Social',80)

insert into ExamResult values ('Sus','Bio',30)

Now it’s time to find Duplicate values in a table use of the following query with actual values.

SELECT name, COUNT(*) AS DupeCount

FROM ExamResult

GROUP BY name

HAVING COUNT(*) > 1

Second Method:-

If you find duplicate values and list of rows that contain them , use the following query.

SELECT * FROM ExamResult WHERE name  IN (SELECT name FROM ExamResult GROUP BY name HAVING COUNT(*)>1) ORDER BY name