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

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 r开发者_高级运维ead 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.

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)

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.

Changes to database structure should NEVER be made using SSMS on a porduction environment for just the reason you brought up. It can destroy performance in a large table. ALTER table is the prefered method, it is faster and it can be stored in source control as a change to push to prod after testing.

Following should be the better way to handle this

IF EXISTS (SELECT   1 
               FROM     INFORMATION_SCHEMA.COLUMNS
               WHERE    TABLE_NAME = '<tablename>' 
               AND      COLUMN_NAME = '<field>')
    BEGIN
        ALTER TABLE <tablename> ALTER COLUMN [<field>] varchar(xxxx) null
    END
ELSE