How to delete duplicate records from table in MS SQL database

 Description: To delete duplicate records present in database, we need take care of two things.

1)   We need to have one AutoNo field in Table

If there is no AutoNo field Present in Table we need to create one extra Auto no Field on temporary basis.

Eg. Consider below given table (Name: StudentResult) having duplicate records of Student marks for different subjects

            StudentResult Table (Containing duplicate records shown in colors)

StudentID	StudentName	Subject	Marks	Comments
11	Shrikant	DotNet	98	Duplicate
12	Asim	DotNet	60	Duplicate
11	Shrikant	DotNet	98	Duplicate
12	Asim	Java	80
12	Asim	DotNet	60	Duplicate
13	Paul	C++	99

           Expected Result for StudentResult

StudentID	StudentName	Subject	Marks	Comments
11	Shrikant	DotNet	98	Duplicate
12	Asim	DotNet	60	Duplicate
12	Asim	Java	80
13	Paul	C++	99

Since there is no AutoNo column present in StudentResult, it’s difficult to identify

Which row of duplicated rows to be deleted?

Table After adding AutoNo.

StudentID	StudentName	Subject	Marks	Comments	AutoNo
11	Shrikant	DotNet	98	Duplicate	1
12	Asim	DotNet	60	Duplicate	2
11	Shrikant	DotNet	98	Duplicate	3
12	Asim	Java	80		4
12	Asim	DotNet	60	Duplicate	5
13	Paul	C++	99		6

2)   After confirming AutoNo field, we need to use Group by Query for Fields that we want to be unique in database.

In StudentResult table, we want delete duplicate records for based on StudentID, Subject.

Use Group by query to select Max of AutoNo of duplicated records as given below

 select MAX(AutoNo) from StudentTable

           Group by StudentID, Subject

 having COUNT(*) >1

AutoNo

3

5

 (Note: we can use more no of table fields on which we want duplication to be checked)

Now we have unique Id of duplicated records and can delete duplicated records using query as given below  

Delete * from StudentTable where AutoNo in (

select MAX(AutoNo) from StudentTable

Group by StudentID, Subject

having COUNT(*) >1

)

At last delete temporary field AutoNo from StudentResult table