gpt4 book ai didi

sql - 如何向具有大量行的现有数据库表添加标识列

转载 作者:行者123 更新时间:2023-12-02 20:29:29 24 4
gpt4 key购买 nike

我有一个大约有 40 000 000 行的数据库表。我想向该表添加一个标识列。如何以日志友好的方式做到这一点?

当我执行以下操作时:

ALTER TABLE table_1
ADD id INT IDENTITY

这只会填满整个日志空间。

有什么方法可以以日志友好的方式做到这一点吗?数据库位于 SQL Server 2008 上。

谢谢,莫汉。

最佳答案

由于整体锁定开销较多,整个过程可能会慢很多,但如果您只关心事务日志大小,您可以尝试以下操作。

  1. 添加可为空整数非标识列(仅元数据更改)。
  2. 编写代码以使用唯一的连续整数批量更新此值。这将减少每个单独事务的大小并减少日志大小(假设简单的恢复模型)。我下面的代码以 100 个为批处理执行此操作,希望您有一个现有的 PK,您可以利用它来从上次中断的地方继续,而不是重复扫描,这样会花费越来越长的时间到最后。
  3. 使用ALTER TABLE ... ALTER COLUMN 将列标记为NOT NULL。这将需要锁定并扫描整个表以验证更改,但不需要太多日志记录。
  4. 使用ALTER TABLE ... SWITCH使该列成为标识列。这只是元数据的更改。

下面的示例代码

/*Set up test table with just one column*/

CREATE TABLE table_1 ( original_column INT )
INSERT INTO table_1
SELECT DISTINCT
number
FROM master..spt_values



/*Step 1 */
ALTER TABLE table_1 ADD id INT NULL



/*Step 2 */
DECLARE @Counter INT = 0 ,
@PrevCounter INT = -1

WHILE @PrevCounter <> @Counter
BEGIN
SET @PrevCounter = @Counter;
WITH T AS ( SELECT TOP 100
* ,
ROW_NUMBER() OVER ( ORDER BY @@SPID )
+ @Counter AS new_id
FROM table_1
WHERE id IS NULL
)
UPDATE T
SET id = new_id
SET @Counter = @Counter + @@ROWCOUNT
END


BEGIN TRY;
BEGIN TRANSACTION ;
/*Step 3 */
ALTER TABLE table_1 ALTER COLUMN id INT NOT NULL

/*Step 4 */
DECLARE @TableScript NVARCHAR(MAX) = '
CREATE TABLE dbo.Destination(
original_column INT,
id INT IDENTITY(' + CAST(@Counter + 1 AS VARCHAR) + ',1)
)

ALTER TABLE dbo.table_1 SWITCH TO dbo.Destination;
'

EXEC(@TableScript)


DROP TABLE table_1 ;

EXECUTE sp_rename N'dbo.Destination', N'table_1', 'OBJECT' ;


COMMIT TRANSACTION ;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION ;
PRINT ERROR_MESSAGE() ;
END CATCH ;

关于sql - 如何向具有大量行的现有数据库表添加标识列,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/7163275/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com