How to delete duplicate from 140 GB SQL server table where 30% records are duplicate without causing overheads like transaction logs full?
table doesn't have primary key.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
If you have no Primary Key then you have no Foreign Keys referencing it.
That leaves you in the privileged position of being able to remove the table and completely recreate it or, rather, create a new table with only the rows you want, then drop the original table and rename the new table, something like this.
create table t2 ( a ... , b ... , c ... ) primary key ( a, b, c ); insert into t2 ( a, b, c ) select a, b, c from t1 where ... ; drop table t1 ; EXEC sp_rename 't2', 't1' ;
Of course, how you identify the rows that you want to take across is up to you.