SQL Server: Removing duplicate rows from a table

In SQL Server 2008 we can remove the duplicate rows from the table by using the following Query:

In SQL Server 2008 we can remove the duplicate rows from the table by using the following Query:  

create table t1(col1 int, col2 int, col3 char(50))

insert into t1 values (1, 1, 'data value one')

insert into t1 values (1, 1, 'data value one')

insert into t1 values (1, 2, 'data value two')

 

declare @AffectedRow int

set @AffectedRow=0

while EXISTS(select count(*) from t1 group by col2 having count(*)> 1)

begin

 

 set @AffectedRow=@AffectedRow+1

 set rowcount 1

 delete from t1

 where col2  in  

 

 ( SELECT  col2

 FROM t1

 GROUP BY col1, col2

 HAVING count(*) > 1) 

end