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

Question

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

User · Accepted Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql-server] When increasing the size of VARCHAR column on a large table could there be any problems?

Examples related to sql-server

Examples related to sql-server-2008

Examples related to varchar

Examples related to alter-column