快速將列 NVARCHAR(4000) 更改為 NVARCHAR(260)

我在處理帶有幾NVARCHAR(4000)列的表時遇到了非常大的記憶體授予的性能問題。問題是這些列永遠不會大於NVARCHAR(260).

使用

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

導致 SQL Server 重寫整個表（並在日誌空間中使用 2 倍的表大小），這是數十億行，只是為了改變任何內容，不是一種選擇。增加列寬沒有這個問題，但減少它。

我已經嘗試創建一個約束CHECK (DATALENGTH([col]) <= 520)，或者CHECK (LEN([col]) <= 260)SQL Server 仍然決定重寫整個表。

有沒有辦法將列數據類型更改為僅元數據操作？沒有重寫整個表的費用？我正在使用 SQL 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.

有沒有辦法將列數據類型更改為僅元數據操作？

我不這麼認為，這就是產品現在的工作方式。喬的回答中提出了一些非常好的解決方法來解決這個限制。

…導致 SQL Server 重寫整個表（並在日誌空間中使用 2 倍的表大小）

我將分別回應該聲明的兩個部分。

重寫表

正如我之前提到的，實際上沒有任何方法可以避免這種情況。這似乎是現實情況，即使從我們作為客戶的角度來看，這並不完全合理。

查看DBCC PAGE將列從 4000 更改為 260 之前和之後顯示所有數據都在數據頁上重複（我的測試表'A'在行中有 260 次）：

此時，頁面上有兩個完全相同的數據副本。“舊”列實質上被刪除（id從id=2更改為id=67108865），而“新”版本的列被更新為指向頁面上數據的新偏移量：

在日誌空間中使用 2 倍表大小

添加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 %）

如您所見，數據文件寫入量略有下降，而日誌文件寫入量則大幅下降。

我不知道有一種方法可以直接完成您在此處尋找的內容。請注意，此時查詢優化器還不夠聰明，無法將記憶體授予計算的約束因素考慮在內，因此無論如何約束都無濟於事。避免重寫表數據的一些方法：

1. 在使用它的所有程式碼中將該列轉換為 NVARCHAR(260)。查詢優化器將使用轉換的數據類型而不是原始數據類型來計算記憶體授予。
2. 重命名表並創建一個執行強制轉換的視圖。這完成了與選項 1 相同的事情，但可能會限制您需要更新的程式碼量。
3. 創建具有正確數據類型的非持久計算列，並讓所有查詢從該列而不是原始列中選擇。
4. 重命名現有列並使用原始名稱添加計算列。然後調整所有對原始列進行更新或插入的查詢，以改用新的列名。

引用自：https://dba.stackexchange.com/questions/248749