92

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.

Jon Schneider
  • 25,758
  • 23
  • 142
  • 170
Paul T Davies
  • 2,527
  • 2
  • 22
  • 39
  • 1
    @nonnb: that is an awful idea. http://stackoverflow.com/q/2091284/27535 – gbn Sep 22 '11 at 10:53
  • @gbn any thoughts on Justin's recent answer to that question? Seems to somewhat be at odds with yours. – AakashM Sep 22 '11 at 11:07
  • @AakashM: he's correct about storage but it is an overhead, not an optimization. Now read this http://stackoverflow.com/q/2009694/27535 – gbn Sep 22 '11 at 11:09
  • @gbn - good point, as is Martin Smith's observation on indexing. Withdrawn. – StuartLC Sep 22 '11 at 11:21
  • 2
    Turns out in the end there was one gotcha! The field was indexed, and when someone tried to enter an entry bigger than 900b it failed! Be warned. – Paul T Davies Sep 28 '11 at 08:36

5 Answers5

61

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

gbn
  • 422,506
  • 82
  • 585
  • 676
  • 1
    All went fine with this. No problems. – Paul T Davies Sep 23 '11 at 15:46
  • Do you know if the same rules apply when going from `varchar(200)` to `varchar(max)`? – CodeNaked Oct 22 '14 at 20:52
  • @CodeNaked: this is a lot trickier to answer. (max) is a LOB type which can be "in row" or outside the row. However, I'm inclined to say it should be the same because the data is already "in row" and no table rebuild would be needed – gbn Oct 23 '14 at 07:52
18

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.

jazzcat
  • 4,351
  • 5
  • 36
  • 37
  • Perhaps it's `VARCHAR(X)` where increasing X to less than or equal to 8000 is fast, whereas greater than 8000, or MAX, would be slow? – TTT Aug 26 '22 at 16:36
4

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.

Kprice84
  • 103
  • 1
  • 1
  • 7
  • I believe this may be true for small tables, but for large tables that are actively being queried this could block for a significant amount of time (as SQL server needs to see if it needs to truncate every row). – CodeNaked Oct 22 '14 at 20:54
0

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

psikorski
  • 1
  • 1
  • This appears to be a comment on [this answer](https://stackoverflow.com/a/41000121/4032703), not an attempt to answer the actual question. It actually has nothing to do with the question that the OP asked. The question was whether increasing the size of a column on a large table would cause problems, not whether or not to use varchar(max). – EJoshuaS - Stand with Ukraine Jan 04 '23 at 19:36
-3

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

Gourav Singla
  • 1,728
  • 1
  • 15
  • 22
  • 6
    That's because you're not using SQL Server per the question (but MySQL, probably). "ALTER TABLE ... MODIFY" is not valid T-SQL. – Jeroen Mostert Nov 27 '14 at 09:08