use tempdb;
go

create table dbo.t (id int identity primary key, f1 numeric(18,2), f2 varchar(100), f3 int);

insert into dbo.t
 (f1, f2, f3)
select
 f1, f2, f3
from
 (
  select top (20000)
   rand(checksum(newid())) * 1000, cast(newid() as varchar(100)), rand(checksum(newid())) * 10000
  from
   master.dbo.spt_values a cross join
   master.dbo.spt_values b
 ) t1(f1, f2, f3) cross apply
 (select top (cast(rand(checksum(t1.f1, t1.f2, t1.f3)) * 100 + 1 as int)) Number from master.dbo.spt_values) t2;
go

declare @rows int, @ms int, @dt datetime2, @show_stat bit = 0, @create_index bit = 0, @show_result bit = 0;

declare @result table (description varchar(100), elapsed_time int, rows_deleted int);

/*Индекс, настоятельно рекомендованный aleks222*/
if @create_index = 1
 create index IX_t on dbo.t (f1, f2, f3, id);

/*Супер быстрый запрос от aleks222*/
set @dt = sysdatetime();
begin tran;

if @show_stat = 1 set statistics xml, io on;
delete a
from
 dbo.t a
where
 exists(select 1 from dbo.t b where b.f1 = a.f1 and b.f2 = a.f2 and b.f3 = a.f3 and b.id < a.id)
option
 (maxdop 1);
select @rows = @@rowcount, @ms = datediff(ms, @dt, sysdatetime());
if @show_stat = 1 set statistics xml, io off;

insert into @result values ('Супер быстрый запрос от aleks222', @ms, @rows);
if @show_result = 1 select * from dbo.t order by id;
rollback;

/*Супер медленный запрос от invm*/
set @dt = sysdatetime();
begin tran;

if @show_stat = 1 set statistics xml, io on;
with t as
(
 select row_number() over (partition by f1, f2, f3 order by id) as rn from dbo.t
)
delete t where rn > 1
option
 (maxdop 1);
select @rows = @@rowcount, @ms = datediff(ms, @dt, sysdatetime());
if @show_stat = 1 set statistics xml, io off;

insert into @result values ('Супер медленный запрос от invm', @ms, @rows);
if @show_result = 1 select * from dbo.t order by id;
rollback;

/*Исходный вариант*/
set @dt = sysdatetime();
begin tran;

if @show_stat = 1 set statistics xml, io on;
delete from dbo.t where id not in (select min(id) from dbo.t group by f1, f2, f3) option (maxdop 1);
select @rows = @@rowcount, @ms = datediff(ms, @dt, sysdatetime());
if @show_stat = 1 set statistics xml, io off;

insert into @result values ('Исходный вариант', @ms, @rows);
if @show_result = 1 select * from dbo.t order by id;
rollback;

/*Та-дам!*/
select * from @result order by elapsed_time;
go

drop table dbo.t;
go