When increasing the size of VARCHAR column on a large table could there be any problems?

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.

Solution 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

Solution 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.

Solution 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.