How to delete duplicate rows in a SQL table | Delete and Avoid duplicate entries in SQL | Ms SQL

One or more rows that have identical or same data values are considered to be Duplicate rows. Watch this video to delete duplicate entries in your SQL table and also how to aviod duplicate entries in a table Below are the scripts that are used in this video. ------------------------------------------- create table Table1(id int,name varchar(10)) insert into Table1 values ('101','Prasad') insert into Table1 values ('101','Prasad') insert into Table1 values ('101','Prasad') insert into Table1 values ('102','Keerthi') insert into Table1 values ('102','Keerthi') insert into Table1 values ('103','David') insert into Table1 values ('103','David') select * from Table1 ---To identify duplicate entries select *,count(*) from Table1 group by name, id having count(*) :- 1 --To delete duplicate entries --Method 1 : create and drop another table select distinct * from table1 select distinct * into temp from Table1 Truncate table table1 delete from Table1 insert into Table1 select * from temp drop table temp select * from table1 --Method 2 : Auto_id alter table Table1 add Auto_id int identity(1,1) select * from table1 delete from Table1 where auto_id not in (select min(auto_id) from Table1 group by name,id) alter table Table1 drop column Auto_id ---Method 3 : Using CTE -- CTE allows you to define a temporary named result set that is available temporarily in the execution scope of a statement such as SELECT, INSERT, UPDATE, DELETE, or MERGE. with EmpCte as ( select *, row_number() over(partition by id order by id) as rowno from Table1 ) delete from EmpCte where rowno :- 1 select * from Table1 Use the primary keys, constraint keys, identity columns, clustered and non-clustered indexes to eliminate the possibility of duplicate rows. --Aviod duplicate entries select * from employee alter table employee add constraint unique_email unique(email) alter table employee add constraint Pkey primary key(ID)

• #education
• #duplicate_rows_in_sql
• #how_to_delete_duplicate_rows_in_sql
• #avoid_duplicate_rows_in_sql
• #how_to_delete_duplicate_data_in_sql