sql - Delete Duplicates in Table with huge amount of rows -

i have table 19 1000000 records. want delete duplicates, query using taking long while , connection timing out.

this query using:

delete [tablename] id not in (select max(id) [tablename] grouping field)

where id primary key , auto increment. want delete duplicates in field.

is there faster alternative query?

any help appreciated.

i suggest temporarily adding index onto field speed things up. maybe utilize statement delete (even though yours should work fine index).

my statement generates list of ids should deleted. assuming id primary key indexed, faster. should perform little improve not in.

with candidates (     select id           , row_number() on (partition field order id desc) rn       [tablename] ) delete    candidates  rn > 1

sql sql-server performance duplicates

Three