快速更改列NVARCHAR(4000)到NVARCHAR(260)

Question

非常大的内存授权使用几个NVARCHAR(4000)列处理这个表时，我遇到了性能问题。问题是这些列从来没有比NVARCHAR(260)大。

使用

ALTER TABLE [table] ALTER COLUMN [col] NVARCHAR(260) NULL

SQL Server中的结果是重写整个表(并在日志空间中使用2x表大小)，这是数十亿行的结果，但不需要更改任何内容。增加列宽没有这个问题，但是减少了。

我尝试过创建约束CHECK (DATALENGTH([col]) <= 520)或CHECK (LEN([col]) <= 260)，Server仍然决定重写整个表。

是否有任何方法将列数据类型更改为元数据操作？不用再重写整张表格了？我使用的是Server 2017 (14.0.2027.2和14.0.3192.2)。

下面是一个用于复制的示例DDL表：

CREATE TABLE [table](
    id INT IDENTITY(1,1) NOT NULL,
    [col] NVARCHAR(4000) NULL,
    CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED (id ASC)
);

然后运行ALTER。

Answer 1

我不知道有什么办法能直接完成你在这里想要的。请注意，查询优化器此时还不够聪明，无法考虑用于内存授权计算的约束，因此该约束无论如何也不会有帮助。避免重写表数据的几种方法：

1. 在使用该列的所有代码中将其转换为NVARCHAR(260)。查询优化器将使用抛出的数据类型而不是原始数据类型计算内存授权。
2. 重命名表并创建一个视图来代替此转换。这可以完成与选项1相同的任务，但可能会限制您需要更新的代码数量。
3. 创建一个具有正确数据类型的非持久化计算列，并从该列中选择所有查询，而不是原始查询。
4. 重命名现有列并添加具有原始名称的计算列。然后调整所有对原始列进行更新或插入的查询，以使用新的列名。

Answer 2

是否有任何方法将列数据类型更改为元数据操作？

我不这么认为，现在产品就是这样运作的。对于乔的回答中提出的这一限制，有一些非常好的解决办法。

Server中的...results重写整个表(并在日志空间中使用2x表大小)

我将对声明的两个部分分别作出回应。

重写表

正如我前面提到的，没有任何办法可以避免这种情况。这似乎是现实的情况，即使它不完全有意义，从我们的角度作为客户。

在将列从4000更改为260之前和之后查看DBCC PAGE显示，所有数据都在数据页上重复(我的测试表在行中有'A' 260次)：

此时，页面上有两个完全相同数据的副本。“旧”列基本上被删除( id从id=2更改为id=67108865)，该列的“新”版本被更新为指向页面上数据的新偏移量：

在日志空间

中使用2x表大小

将WITH (ONLINE = ON)添加到ALTER语句的末尾将日志记录活动减少大约一半，因此这是您可以进行的一项改进，以减少对磁盘/磁盘空间的写入量。

我使用这个测试工具来尝试它：

USE [master];
GO
DROP DATABASE IF EXISTS [248749];
GO
CREATE DATABASE [248749] 
ON PRIMARY 
(
    NAME = N'248749', 
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\248749.mdf', 
    SIZE = 2048000KB, 
    FILEGROWTH = 65536KB
)
LOG ON 
(
    NAME = N'248749_log', 
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\248749_log.ldf', 
    SIZE = 2048000KB, 
    FILEGROWTH = 65536KB
);
GO
USE [248749];
GO

CREATE TABLE dbo.[table]
(
    id int IDENTITY(1,1) NOT NULL,
    [col] nvarchar (4000) NULL,

    CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED (id ASC)
);

INSERT INTO dbo.[table]
SELECT TOP (1000000)
    REPLICATE(N'A', 260)
FROM master.dbo.spt_values v1
    CROSS JOIN master.dbo.spt_values v2
    CROSS JOIN master.dbo.spt_values v3;
GO

在运行sys.dm_io_virtual_file_stats(DB_ID(N'248749'), DEFAULT)语句之前和之后，我检查了ALTER，这里有不同之处：

默认(脱机) ALTER

• 写入数据文件/写入字节: 34,809 / 2,193,801,216
• 写入日志文件/写入字节: 40,953 / 1,484,910,080

联机ALTER

• 写入数据文件/写入字节: 36,874 / 1,693,745,152 (下降22.8%)
• 日志文件写入/写入字节: 24,680 / 866,166,272 (下降41%)

如您所见，数据文件写入量略有下降，日志文件写入量大幅下降。

Answer 3

我也经历过很多次类似的情况。

步骤：

添加一个所需宽度的新值

使用游标，每次提交几千次迭代(可能是一万次或两万次)，将数据从旧列复制到新列。

掉落旧柱

将新列重命名为旧列的名称

塔达！