Delete Duplicate Records/Rows from Table in Sql Server

Ans:-

use SubsDB

create table test(id int,name varchar(30),descrptn varchar(30))

--1.(it contain duplicate records)

select * from test

--here this table(test1) only keep unique records

select distinct * into test1 from test

--here drop the table test

drop table test

--here rename the table test1 to test

sp_rename test1,test

OR

--2.(it contain duplicate records)

select * from test

--here this table(test1) only keep unique records

select distinct * into test1 from test

select * from test1

--here delete all the records of table(test) test

truncate table test

select * from test

--here insert one whole table(test1) records into another table(test)

insert into test select * from test1

select * from test

-- then we can drop the table(test1)

drop table test1

OR

insert into test select * from test

--3.(it contain duplicate records)

select * from test

--select whole query given below and exucute,we will get unique records

with cte as(select id,name,descrptn,RN = ROW_NUMBER()OVER(PARTITION BY id ORDER BY id) FROM test

)

DELETE FROM CTE WHERE RN > 1