这个ALTER语句需要多长时间？

Question

Alter TABLE [XXX] Alter column [YYY] [varchar](max) NULL

假设

• 数据空间为45 GB，索引空间为2GB；
• 这张表中大约有300万行；
• YYY列现在是varchar(8000)，可以更新(是可写的)。
• 这张表还有大约30列。
• 这台机器上有来自其他数据库和表格的3000 G数据。

其他一些资料：

• 99.99%的行在此NULL中包含varchar(8000)；
• web应用程序可按每分钟5次访问此表；
• 硬件是企业级的(8核心CPU和256 is )。
• 这台机器上还有其他的表和数据库，大约3000 on的数据。

相关@@VERSION详细信息：

Microsoft 2014-12.0.4422.0 (X64) 企业版(64位)

Answer 1

没有人能以任何程度的确定性或准确性告诉你，这个操作将花费你的系统多长时间。然而，我认为这种变化的影响可能会比你想象的要小。我设置了一个快速测试来比较从int -> bigint和varchar(8000) -> varchar(max)更改表。首先，有两个简单的表格：

CREATE TABLE dbo.t0(a int primary key, b int);
GO
CREATE TABLE dbo.t1(a int primary key, b varchar(8000));
GO

现在，至少插入一个非空值，然后超过500万行(在我的系统上；YMMV)：

INSERT dbo.t0 VALUES(0,1);
INSERT dbo.t0 SELECT rn, NULL FROM 
( SELECT rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
  FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2
) AS x;
GO -- 5,299,204 rows for me

INSERT dbo.t1 VALUES(0,'what');
INSERT dbo.t1 SELECT rn, NULL FROM 
( SELECT rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
  FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2
) AS x;
GO -- 5,299,204 rows for me

然后我测试了ALTER更改，启用了stats /O：

SET STATISTICS IO ON;
GO
ALTER TABLE dbo.t0 ALTER COLUMN b bigint NULL; 
GO -- 9 seconds, 122,506 reads
SET STATISTICS IO OFF;

这一过程在9秒内完成，需要122 506次读取。

SET STATISTICS IO ON;
GO
ALTER TABLE dbo.t1 ALTER COLUMN b varchar(max) NULL; 
GO -- 5 seconds, 8,562 logical reads
SET STATISTICS IO OFF;

这项工作在5秒内完成，只需要8 562次读取。

因此，虽然两种操作都不在网上进行，而且您的实际结果可能因硬件和表结构的不同而有所不同，但这是在MacBook上的低级别Windows上进行的，因此我希望您的时间会比这更好。

我还测试了更多在可空列中填充的数据。删除这些表，用上面的脚本重新创建和填充这些表，然后运行这个命令将某些内容放入100,000行：

;WITH x AS (SELECT TOP (100000) a,b FROM dbo.t1 ORDER BY NEWID())
UPDATE x SET b = a;
GO
;WITH x AS (SELECT TOP (100000) a,b FROM dbo.t1 ORDER BY NEWID())
UPDATE x SET b = REPLICATE(RTRIM(a), 1000);
GO

这比后来的ALTERs花了更长的时间(超过3分钟)。第一个ALTER仍然用了9秒，而第二个只花了17秒，仅仅是因为读取的次数增加了(到189,854次)。这类似于如果您在这个表中有其他30列的话。我仍然认为17秒对于实际发生的事情是非常好的，而且，这也与企业级环境相去甚远。

正如Paul所指出的，从Server 2016开始，您可以在网上实现这一点，但是也有限制和限制。有关更多信息，看医生。

ALTER TABLE dbo.t1 ALTER COLUMN b varchar(max) NULL WITH (ONLINE = ON);

不过，Server 2014中没有此选项。

让这种情况在网上发生的一种方法是简单地将该列移动到一个独立的相关表。这里有一些复杂的问题，比如如何切换，它可能会影响一堆代码，但是您可以用视图来解决大多数问题(至少是暂时的)。

Answer 2

VARCHAR(8000)列不会被索引，因为在Server 2016之前只能索引多达900个字节，所以不需要担心索引。

当您进行更改时，您需要考虑访问表的对象和内容。就Server而言，这种更改被认为是数据类型的更改，因此将花费比更改VARCHAR大小更长的时间，VARCHAR大小被认为是元数据更改。

下面发生的情况是，VARCHAR(8000)列中的数据将从行中移出，您的记录现在将有一个指向由VARCHAR(MAX)表示的数据的指针。

执行时间将取决于许多事情，如

• 硬件-存储性能
• 利用--在你的盒子上正在发生什么其他的过程。
• TempDB尺寸
• 您的300万张唱片中有多少在VARCHAR(8000)字段中有某些内容
• VARCHAR(8000)字段中实际有多少字节。如果您的大多数记录都有较大的字符串，那么这将需要很长时间。

就我个人而言，我会在测试环境中进行排练，并期望在工作时间之外的安静时间内进行演练。

Answer 3

如果表未被锁定，则需要键入它的时间。

如果你怀疑我的话，那是DDL，不是DML。