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

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.

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.

Answer 3

I have the same issue. Athough my account has sa rights if I try using another sa account it works. It seems that somehow my account does not have the ability to alter. still investigating, but it is a permission issue.

update:

I cannot expain it. but this is what I did. there two domain groups my account belonged to. One was a new AD domain group and the other was an NT legay domain group. Once I removed the legacy domain group I was able to alter the table successfully. Mind you both groups had "sa" priviliges.

The behavior was that the alter commands would result in success, but nothing changed on the table. Then when I manually tried to change the fields through the designer it complained that I was not allowed to make change if it required to drop and recreate the table. I found the setting in tools and I was able to turn that off. But this table is huge and not a good idea to do this. I woud advise others against it.

so it was a permission issue. I can't explain how, but I hope it helps someone else

Answer 4

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 5

Another way to this without totally dropping the table is

1. Take a backup of the column values.

2. Make the column nullable if it does not already allow nulls. Set the column values to be null by doing

   update tablename set columnname = null 
   

3. Delete the column
4. Insert a new column with the same name as the deleted column and the type which you want
5. Insert the saved data into this column