sql-server - 为什么将列从 nvarchar 更改为 varchar 会导致 mdf 文件大幅增长?
问题描述
我有一个包含大约 1500 万行的表,我将一列从 nvarchar(100) 更改为 varchar(100)。此更改仍在进行中(更改已运行大约一个小时),到目前为止 mdf 文件已增长 50GB(并且仍在增长)。
这是出乎意料的,因为从nvarchar
到的更改varchar
理论上应该会导致mdf
(nvarchar
需要大约两倍的空间varchar
) 中使用的空间减少。
知道为什么mdf
文件在此更改期间增长如此之多吗?
环境:SQL Server 2014
编辑:原因不是由于自动增长,因为自动增长仅设置为 256MB。
解决方案
当您在 SSMS 表设计器中进行更改时,SSMS 会使用您的架构更改创建一个新表。然后它将原始表中的数据复制到新表中并删除原始表。最后,它将新表重命名为原始名称。假设此交换所需的空间超过 .mdf 文件中的可用空间量,新表说明了 .mdf 文件的增长。
如果您在服务器实例上启动跟踪,然后执行更新架构的步骤,您可以看到表设计器在后台执行的所有语句。
SSMS 更改列数据类型的过程如下所示:
CREATE TABLE dbo.some_table (Col1 NVARCHAR(100)); --the original table
--SSMS does this in the background
CREATE TABLE dbo.some_table_1 (Col1 VARCHAR(100));
ALTER TABLE dbo.som_table_1 SET (LOCK_ESCALATION = TABLE);
IF EXISTS(SELECT * FROM dbo.some_table_1) EXEC('INSERT INTO dbo.som_table_1 (Col1) SELECT CONVERT(VARCHAR(100), Col1) FROM dbo.some_table');
DROP TABLE dbo.some_table;
EXECUTE sp_rename N'dbo.some_table_1', N'some_table', 'OBJECT';
您可以通过使用自己的 DDL 而不是表设计器来缓解这种增长。这是一个权衡 - 易于自动化与花费一段时间并增加文件。
ALTER TABLE dbo.some_table ADD Col2 VARCHAR(100);
UPDATE dbo.some_table SET Col2 = CONVERT(VARCHAR(100), Col1);
ALTER TABLE dbo.some_table DROP COLUMN Col1;
EXEC sp_rename 'dbo.some_table.Col2', 'Col1', 'COLUMN';
由于重复的列数据,您仍然会获得一点数据增长,但它只是单个列而不是整个表。根据表的不同,它可以产生巨大的影响。