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