当增加大型表上 VARCHAR 列的大小时，会出现什么问题吗？

我正在使用 SQLServer2008，我需要在一个大约有500k 行的表上放大一个 VARCHAR 字段，从(200到1200)。我需要知道的是，是否有我没有考虑过的问题。

我将使用这个 TSQL 语句:

ALTER TABLE MyTable
ALTER COLUMN [MyColumn] VARCHAR(1200)

我已经在数据的一个副本上尝试过了，这个声明没有我看到的不良影响。

那么，这样做有没有什么我可能没有考虑到的问题呢？

顺便说一下，该列没有被索引。

117988 次浏览
小开
最佳答案

This is a metadata change only: it is quick.

An observation: specify NULL or NOT NULL explicitly to avoid "accidents" if one of the SET ANSI_xx settings are different eg run in osql not SSMS for some reason

小开

Changing to Varchar(1200) from Varchar(200) should cause you no issue as it is only a metadata change and as SQL server 2008 truncates excesive blank spaces you should see no performance differences either so in short there should be no issues with making the change.

小开

In my case alter column was not working so one can use 'Modify' command, like:

alter table [table_name] MODIFY column [column_name] varchar(1200);

小开

Just wanted to add my 2 cents, since I googled this question b/c I found myself in a similar situation...

BE AWARE that while changing from varchar(xxx) to varchar(yyy) is a meta-data change indeed, but changing to varchar(max) is not. Because varchar(max) values (aka BLOB values - image/text etc) are stored differently on the disk, not within a table row, but "out of row". So the server will go nuts on a big table and become unresponsive for minutes (hours).

--no downtime
ALTER TABLE MyTable ALTER COLUMN [MyColumn] VARCHAR(1200)


--huge downtime
ALTER TABLE MyTable ALTER COLUMN [MyColumn] VARCHAR(max)

PS. same applies to nvarchar or course.

小开

Another reason why you should avoid converting the column to varchar(max) is because you cannot create an index on a varchar(max) column.


提交答案