将值设置为超过 500 万行的表中的新日期时间列 [英] Set value to a new datetime column in a table with over 5 million rows

问题描述

我有一个名为 Example 的表，它有超过 500 万行.我想知道创建不允许空值且默认值为 Now 的新 DateTime 列的最有效方法.由于行数，仅设置值会失败.

I have a Table named Example that has a over 5M rows. I want to know the most efficient way to create a new DateTime column that does not allow nulls and has a default value of Now. just setting the value would fail due to the amount of rows.

我想到的计划将涉及:

1) 创建一个允许空值的新列.

1) creating a new column that allows nulls.

ALTER TABLE Example
ADD RecordDate datetime
GO

2) 将列的值设置为 GETDATE() 一次 1000(或更多)行.

2) set the value of the column to GETDATE() 1000 (or more if possible) rows at a time.

3) 一旦所有行都有一个值，我将更改列以不允许空值.

3) once all rows have a value, I would alter the column to not allow nulls.

ALTER TABLE Example
ALTER COLUMN RecordDate datetime NOT NULL

我不确定完成第 2 步的最有效方法是什么.所以这就是我想要的一些提示.

I am not sure on what would be the most efficient way of completing step number 2. so that is what I would like some tips on.

推荐答案

要处理具有顺序 ID 的大表，批量应用更新，此方法将有效:

To work though a large table with a sequential ID, applying updates in batches, this approach will work:

DECLARE @startID bigint
DECLARE @endID bigint

SELECT @startID=min(ID) from Example

WHILE @startID IS NOT NULL BEGIN
  SELECT @endID=MAX(ID) FROM (
    SELECT top(1000) ID from Example where ID>=@startID ORDER BY ID
  ) t

  update Example
  set RecordDate = GETDATE()
  where ID between @startID and @endID AND RecordDate IS NULL

  IF @@ROWCOUNT=0 BEGIN
    SET @startID=NULL
  END ELSE BEGIN
    SET @startID=@endID
  END
END

批量大小由

SELECT top(1000) ID from Example where ID>=@startID ORDER BY ID

根据需要调整 1000 以确保每个 UPDATE 快速完成.我已经使用这种技术以每次更新约 100000 行的速度批量更新数亿行.

Adjust the 1000 as necessary to ensure each UPDATE completes quickly. I've used this technique to update hundreds of millions of rows in batches of around 100000 per update.

这篇关于将值设置为超过 500 万行的表中的新日期时间列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！