CODEWITHSUNDEEP
sql-servert-sql
Pure.Krome
Pure.Krome

Reputation: 86957

Can I write a SQL script to update all the rows for a single table in SQL Server, but in batches?

I have a large amount of rows in a single table, in our Microsoft SQL Server.

I need to add a new column (DateTime) to the table. Fine. The column needs to be NOT NULL. Ok, fine. Now, this is the problem:

It takes too long to set all column values for all the rows

I tried to create the NULLABLE column. That took a nanosecond. Then UPDATE all the rows by setting the value to GETUTCDATE().

eg. 

DECLARE @foo = GETUTCDATE();

UPDATE PewPew 
SET NewField = @Foo

After 2 hours, I had to cancel the query. Yeah, 2 hours. I also did NOT do this in a TRANSACTION. I then dropped the new field and we were back to where we started.

I was thinking could we

  1. Create column NULLABLE
  2. In batches of 1000 or something, UPDATE the top 1000 rows where the NewField is NULL.

Once all is done, then alter the column to make it NOT NULLABLE

The SQL Server is on Azure - it's a Standard D13 (8 Cores, 56 GB memory) VM and I think we put it on SSD's, so I'd like to think the hardware isn't too bad.

Footnote: large amount of rows = I think it's about ~20 million. That's sorta large to us, but not large to some. We get that.

Upvotes: 2

Views: 325

Answers (2)

marc_s
marc_s

Reputation: 754518

What if you add the new column as NOT NULL and with a default constraint?

ALTER TABLE dbo.YourTable
ADD NewColumn DATETIME2(3) NOT NULL
    CONSTRAINT DF_YourTable_NewColumn DEFAULT (SYSUTCDATETIME())

That should add the column, as NOT NULL, and immediately fill in the default value. Not sure if that'll run any faster than your UPDATE, though - worth a shot.

Upvotes: 1

Akshey Bhat
Akshey Bhat

Reputation: 8545

select 1;
while(@@RowCount<>0)
begin

   update top(1000) PewPew set NewField = getutcdate() WHERE NewField IS NULL;
end

This will update 1000 rows at a time.

Upvotes: 2

Related Questions