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

Question

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

If you try to use SQL Server Enterprise Manager, the created script effectively creates a new table with a new structure, inserting all the data from the existing table into it, discarding 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 the ALTER statement to resize.

Does ALTER the way data is stored in any way? Indices? Statistics? I want to avoid performance hits due to this modification due to the fact that the table can become large.

Answer 1

Just use ALTER TABLE. SSMS a little er er silly sometimes

You will need to drop and restore dependent restrictions (FK, unique, index, validation, etc.)

However, this is only a metadata change and will be very fast 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 Microsoft intended to make this kind of change.

And if you do not add additional parameters to your team, neither indexes nor statistics will suffer. The possibility of data loss is also not provided, because you just make the column larger. Everything should be fine.

Answer 3

Changes to the database structure should NEVER be made using SSMS in a radiation environment just for the reason that you raised it. It can ruin performance in a large table. ALTER table is the preferred method, it is faster and can be saved in the source control as a change for push to prod after testing.

Answer 4

Next up should be the best 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