0

Our Server is SQL Server 2016 Database with Full Recovery model.

We have table "A" - heap which has 280 millions of records and size 90 GB with 50 columns. This table contains around 5 millions of duplicates rows. Our goal is to have this table without duplicates values (so tables with 275 millions of records). At the end this table will have primary key for 6 columns ( NVARCHAR column and 5 VARBINARY columns ).

We have also table (heap) "B" which contains only duplicated values from table A (5 milions of records) with columns which will be PK in table "A". (NVARCHAR column and 5 VARBINARY columns ).

What is the best approach to remove duplicates from table "A" ?

We were thinking about below options:

1.

INSERT INTO dbo.NEW_TABLE_WITHOUT_DUPLICATES WITH (TABLOCK)
SELECT DISTINCT * FROM dbo.A
INSERT INTO dbo.NEW_TABLE_WITHOUT_DUPLICATES WITH (TABLOCK)
SELECT * FROM dbo.A WHERE NOT EXISTS
(
SELECT * FROM dbo.B where b.column1 = a.column1 and b.column2=a.column2 and b.column3=a.column3...
)

and then insert records from table A with outer apply with TOP1 with table B

or other option which will minimalize transaction log?

1 Answer 1

0
  • Move 5 millions of rows with duplicates to separate table (copy and delete)
  • Remove duplicates in separate table
  • Insert into main table from separate table without duplicates

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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