Delete duplicate records from table (Sql Server Interview Question)

delete duplicate records from table (Sql Server Interview Question)

1.delete duplicate records in Sql Server  with identity column

create table Student (RollNo int  identity(1,1) ,Name nvarchar(50),Course nvarchar(50))

insert into Student values('Kush','Mca')

insert into Student valueS ('Somesh','B. Tech')

Select * from Student

select min (RollNo) from Student  group by Name,Course

select min (RollNo) from Student  group by Name,Course

--delete duplicate records in Sql Server  with identity column...................

delete from Student where RollNo not in (select min (RollNo) from Student  group by Name,Course)

2.delete duplicate records in Sql Server  with identity column..

--delete duplicate records in Sql Server  with identity column...................

Delete table1 from Student table1 join Student table2 on table1.Name=table2.Name

and table1.course=table2.Course and table1.RollNo >table2.RollNo

3.delete duplicate records in Sql Server  without identity column..

--Delete column RollNo (identity column) ...................

alter table Student drop column RollNo

Select * from Student

--delete duplicate records in Sql Server  with identity column...................

Select distinct * into #temp from Student truncate table Student insert into Student select * from #temp