I have a wide table (80 columns) with 70 million rows in it. I can currently trying to set a foreign key across all rows within the table.
Approach one An update statement over the entire table such as:
BEGIN TRAN UPDATE Cx SET Cx.CohortId = Chx.CohortId FROM Customers Cx INNER JOIN Cohorts Chx ON Chx.NameField = Cx.NameField COMMIT TRAN
I was informed this has the potential to fill up the transaction log (please note recovery mode is currently set to simple) and it would be better to batch the updates:
Approach two Update in batches
DECLARE @Rows INT, @BatchSize INT; SET @BatchSize = 20000; SET @Rows = @BatchSize; WHILE (@Rows = @BatchSize) BEGIN UPDATE TOP(@BatchSize) Cx SET Cx.CohortId = Chx.CohortId FROM Customers Cx INNER JOIN Cohorts Chx ON Chx.NameField = Cx.NameField WHERE Cx.CohortId IS NULL -- Do not update records that have already been uplifted to include the FK SET @Rows = @@ROWCOUNT END
Does the second approach yield any benefit in terms of the transaction log on large table updates?