Is it bad to use ALTER TABLE to resize a varchar column to a larger size?

Question

I need a simple resize of a column from VARCHAR(36) to VARCHAR(40).

If you try to use SQL Server Enterprise Manager, the script it generates is effectively creating a new table with the new structure, inserting all of the data from the existing table into it, dropping the existing table, renaming the new table, and recreating any indexes.

If you read the documentation (and many online resources including SO), you can use an ALTER statement for the resize.

Does the ALTER affect the way the data is stored in any way? Indexes? Statistics? I want to avoid performance hits because of this modification due to the fact that the table can get large.

Answer 1

Just use ALTER TABLE. SSMS is a bit, er, stupid sometimes

You'll need to drop and recreate dependent constraints (FK, unique, index, check etc)

However, this is only a metadata change and will be very quick for any size table (unless you also change NOT NULL to NULL or varchar to nvarchar or such)

Answer 2

No, ALTER TABLE (http://msdn.microsoft.com/de-de/library/ms190273.aspx) is the way how Microsoft intended to do this kind of change.

And if you do not add extra options to your command, no indexes or statistics should get harmed. A possibility of data loss is also not given, because you are just making the column bigger. Everything should be fine.