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.