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

Question

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 1

Because that's how SQL Server Management Studio does it (sometimes)!

Use TSQL's ALTER TABLE instead:

ALTER TABLE
    ADD myCol int NOT NULL

Answer 2

When you edit a table definition in the designer, you are saying "here's what I want the table to look like, now work out what SQL statements to issue to make my wishes come true". This works fine for simple changes, but the software can't read your mind, and sometimes it will try to do things in a more complicated way for safety.

When this happens, I suggest that, instead of just clicking OK, click the "Script" button at the top of the dialog, and let it generate the SQL statements into a query window. You can then edit and simplify the generated code before executing it.

Answer 3

SQL Server (and any other RDBMS, really) doesn't have any notion of "column order" - e.g. if you move columns around, the only way to achieve that new table structure is be issuing a new CREATE TABLE statement. You cannot order your columns any other way - nor should you, really, since in the relational theory, the order of the columns in a tuple is irrelevant.

So the only thing SQL Server Management Studio can do (and has done all along) is:

• rename the old table
• create the new table in your new layout you wish to have
• copy the data over from the old table
• drop the old table

The only way to get around this is:

• not reordering any columns - only add new columns at the end of your table
• use ALTER TABLE SQL statements instead of the interactive table designer for your work

Answer 4

There are bugs in SSMS 2008 R2 (and older) that are useful to know:

• when the table data is changed, ерушк rendering in SSMS is autorefreshed by SSMS in its already opened tabs (windows) - one should press Ctrl+R to refresh. The options to force refreshing do not appear in SSMS GUI - through buttons, menus or context-sensitive options (on right-clicking)
• when a (table or database) schema is modified, like adding/deleting/removing a column in a table, SSMS does not reflect these changes in already opened tabs(windows) even through Ctrl+R, one should close and reopen tabs(windows)

I reported it few years ago through Microsoft Connect feedback, but bugs were closed due to it is "by design"

Update:
This is strange and irritating to see in desktop product developed during 2 decades, while this (autorefreshing) is being done by most webapplications in any browser

Answer 5

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)