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.
This question is related to
sql-server
sql-server-2008
varchar
alter-column
Another reason why you should avoid converting the column to varchar(max) is because you cannot create an index on a varchar(max) column.
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.
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.
Source: Stackoverflow.com