How to change a column without dropping a table in SQL 2008

Question

Why does SQL 2008 all of a sudden want to drop my tables when I go to change the column type from say int to real? This never happened in SQL 2005 to my knowledge. Any insight would be helpful please.

Answer 1

I can't believe the top answer has been sitting here for so long - it is very dangerous advice!

There are few operations that you can do inplace without dropping your table:

• Expand a varchar column https://dba.stackexchange.com/questions/5211/changing-column-width
• Make a column nullable (but not vice-versa)
• Renaming columns using sp_rename

If you find yourself in the situation where altering a column is not possible without dropping the table, you can usually use a SELECT INTO query to project your data into a new table, then drop the old table (temporarily disabling constraints) and then renaming the projected table. You will need to take your database offline for maintenance in this case though.

Answer 2

Here is what I use:

-- Add new column ALTER TABLE MyTable ADD Description2 VARCHAR(MAX) GO  -- Copy data to new column (probably with modifications) Update MyTable SET Description2 = Description GO  -- Drop old column ALTER TABLE MyTable DROP COLUMN Description GO  -- Rename new column to the original column's name. sp_RENAME 'MyTable.Description2' , 'Description', 'COLUMN' GO 

1. Copy the data into a new column.
2. Drop the old column.
3. Rename the new column to the old column's name.