Imagine a table (Dim) with surrogate identifier (SID) and with duplicate records that share same values on alternate key (Key1, Key2).

 The soluton is "set-oriented" and therefore very fast even for millions of records. It is also nice that it does not use Temp table.

-- delete duplicate records from Dim
-- there are records with duplacates on alternate key (Key1+ Key2)

delete from Dim
where SID < ( < ...