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

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.

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.

In SQL Server 2008, go to Tools >> Options. In the little window, click "Designer". Uncheck "Prevent saving changes that require ..."

=====

Edited on Sept 4th, 2015.

I have added this answer here a long, long time ago describing the way I would solve the situation described on the question above. Since then, users on the threads below have exposed several concerns on doing things the way I recommended at the time. Basically, the solution I described could be problematic on some scenarios. I then suggest you to keep on reading to check other users' comments and pick the best solution for you.