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?

Rema

I respectfully disagree with the common opinion that order does not matter. Readability and organization always matter when programming. For example, I always like my PK's grouped together, not interspersed throughout a 50 column table. For another example, an obfuscator technically does not matter to a program's functionality, but it (purposefully) makes the code into an awful mess. IMO the desire to order your columns is a completely natural one. – Braxton

Answer 1

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)

Undesigning

Thanks! This should work for the moment but a little dangerous :) – Rema

Glad to hear it! By the way, SSMS will also move your data to the new table. I've never experienced data loss or had any other problems doing it this way. Though I always do a backup first of course! – Undesigning

This is a very dangerous advice! Never uncheck this field or you may accidentally lose all your data on structural changes. Forget the UI and use TSQL for altering your table as described in one of the other answers. – Veta

Answer 2

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

Use TSQL's ALTER TABLE instead:

ALTER TABLE
    ADD myCol int NOT NULL

Hebraism

@tvrsubs: Order does not matter. See this – Cirenaica

Thanks for the link. Yes, I am obsessive and you should probably see this answers.com/topic/cleanliness-is-next-to-godliness :) – Rema

Order does matter if you like being organized and if you leave a project for 2 years and come back to make changes isn't it nice to be able to see things in an organized fashion? – Carrizales

@ladieu: not really. There are situations when it is just not practical to drop and re-create a table, just so that a column is 'in the right place'. If that bothers you, write views on top of the tables. – Hebraism

Yes the order matters. Not for queries, but for maintenance; for making changes to the code. It doesn't matter that much, but it is not irrelevant. Grouping related columns where one started 4 years ago and another was added just the other day would be nice. After years of code changes many tables are a mess. Besides, it could be made into an SSMS feature; no need to physically reorder the records on the disk! Just store the display order somehow, is all we need... – Extant

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

Kilah

Answer 4

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.

Trella

Sorry where can I find the "Script" button? Is the same as "Script Table as" menu? – Rema

Answer 5

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

Zoellick

I should say, these make sense since doing this requires some additional overhead (of course they should probably check timestamps or something similar) – Rema

What make sense? - that one not neither autorefresh nor get actualized schema by forcing refresh manually the metadata without closing and reopening a window? What is the sense in it? It is not overhead, i.e. not delay, one cannot get the changed schema (metadata) graphical representation in SSMS at all in already opened (before change) window at all independently on delay – Bunyan

Answer 6

I wasen't allowed to add the column since the column didn't allow "NOT NULL" and therefore when I tried to create the column through the designer or by writing a script, I got the error, you've got.

Instead of "re-creating" the table I just added the column allowing NULL values, populated the column with NON-NULL values and then used the designer (or you can just use a script) to set the column to NOT NULL.

Hope that helps some of you at least.

Revive