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.

  • 4
    You do it in batches, then you add a primary key so you don't have to do it again. Do you have a clustered index at least? Aug 12 '21 at 12:19
  • all three columns in table make it unique row. no clustered index but non cluster index. Aug 12 '21 at 12:29
  • What would be the best way to identify duplicates in the table when data size is huge? Aug 12 '21 at 12:40
  • If you have the time, then doing them in very small batches while monitoring the size of the transaction log and basically pausing as it starts to fill would work. If time is a factor, you may need to switch to SIMPLE recovery model. You should post, obfuscated if you want, a table definition and the nonclustered index definition, so people can give more targeted advice.
    – BCM
    Aug 12 '21 at 13:16
  • Start by adding a clustered index on your table. Having a heap with non-custered index is not a good design for 99% of the cases (and I bet you are not in this 1%). Don't create the index as "unique" for the implementation. Once all your data is ordered it will be easier for SQL to run the query to delete the duplicate in small batch. Aug 12 '21 at 13:54

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.

  • I have xml file for an account, I pulled key/value pairs of that file into table with account number indicating the file name. this has been repeated for 1000 accounts and the table is backup table now for all accounts. since I load files incrementally through SSIS looping account number I created account number as non clustered index. Now I could see some of the files/Account number are loaded multiple times that I want to delete. How could I find duplicates on this huge table is something I am looking for, also once identified how to run delete probably in batches? Aug 12 '21 at 14:45
  • insert into t2 ( a, b, c ) select distinct a, b, c from t1 --would this work on this huge table? Aug 12 '21 at 14:49
  • 1
    The first sentence is not true. Foreign keys can reference any unique constraint, not just the primary key.
    – Zikato
    Aug 12 '21 at 16:01
  • 1
    ... and an FK can even reference a column with a unique index (without a constraint definition at all). Aug 12 '21 at 16:06
  • Since I had the non clustered index on Account number, Created following procedure to loop through and delete duplicate data in batches using SSIS FELC container. create proc deleteDups @Accountnumber nvarchar(30) as begin WITH table_nameCTE AS ( SELECT*,ROW_NUMBER() OVER(PARTITION BY Accountnumber, [key], [value] ORDER BY Accountnumber, [key], [value]) alias_name FROM XML_Doc_bkp where accountnumber=@Accountnumber ) delete table_nameCTE WHERE alias_name >1 end Aug 13 '21 at 13:04

Not the answer you're looking for? Browse other questions tagged or ask your own question.