When increasing the size of VARCHAR column on a la

问题:

I'm using SQL Server 2008 and I need to make a VARCHAR field bigger, from (200 to 1200) on a table with about 500k rows. What I need to know is if there are any issues I have not considered.

I will be using this TSQL statement:

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

I've already tried it on a copy of the data and this statement had no ill effects that I could see.

So are there any possible problems from doing this that I may not have considered?

By the way, the column is not indexed.

回答1:

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

回答2:

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.

回答3:

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.

回答4:

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

回答5:

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);