SQL Server Management Studio - Adding/Moving Columns require drop and re-create?

sql-server sql-server-2005 sql-server-2008 sql-server-express
tvr picture tvr · Oct 9, 2010 · Viewed 38.2k times · Source

Why do I get message that the table needs to dropped and re-created when I add/move columns? I believe this happens after adding foreign key constraints.

What can I do to add new columns without dropping table?

Answer

John Rose picture John Rose · Oct 11, 2010

"Prevent saving changes that require table re-creation"

If you're more interested in simply getting SSMS to stop nagging, you can uncheck the "Prevent saving changes that require table re-creation" setting in Options->Designers->Table And Database Designers. The table(s) will still be dropped and re-created, but at least SSMS won't pester you quite as much about it.

(This assumes you're working in an dev/test environment or in a production environment where a brief lapse in the existence of the table won't screw anything up)

Related questions

How to avoid the "divide by zero" error in SQL?

I have this error message: Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered. What is the best way to write SQL code so that I will never see this error message again? I could do either of the following: …

Read More
SQL Server query to find all permissions/access for all users in a database

I would like to write a query on a sql 2008 that will report all the users that have access to a specific database, or objects within the database such as tables, views, and stored procedures, either directly or due to …

Read More
sql query to return differences between two tables

I am trying to compare two tables, SQL Server, to verify some data. I want to return all the rows from both tables where data is either in one or the other. In essence, I want to show all the …

Read More