16

I have an old table with FKs in it. I want to add a new column. I'll want to make this new column my primary key. So I thought I could either insert that column as the first one or insert it at the end of my table and re-order my columns afterwards.

But SQL Server Management Studio did not allow me to do that. I understand that I cannot do that, and that column order is almost completely irrelevant in SQL.

What I want to know, is how come... I mean, I can drop a column... isn't that the same thing as adding a new one?

I'm just trying to understand what's going on. I've had a hard time finding documentation for that also. If anyone can point me in the good direction.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sébastien Richer
  • 1,642
  • 2
  • 17
  • 38
  • 1
    Can you tell us what version of sql server you're using and what the exact error message you get is? I'm guessing it may be a setting that won't allow you to make changes that require a table to be recreated. – Ryan Dec 09 '10 at 19:41
  • Sorry for the delay, Christmas work party delayed my work hehe. I'm using MSSQL Server 2008 R2 (management studio). The error message is : "Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that requires the table to be re-created." – Sébastien Richer Dec 13 '10 at 18:44
  • possible duplicate of [Can I logically reorder columns in a table?](http://stackoverflow.com/questions/1610/can-i-logically-reorder-columns-in-a-table) – bummi May 15 '14 at 10:47
  • This is very close to being a duplicate, but isn't actually a duplicate as the referenced question doesn't ask or get an answer as to the why. – Tim Abell Aug 06 '15 at 18:06

6 Answers6

26

Definitely you can. Uncheck this option in SQL Server Management Studio:
Tools > Options > Designers > Prevent saving changes that require table re-creation.

Please don't do this in production unless you know the implications!

Options dialog

Pang
  • 9,564
  • 146
  • 81
  • 122
Jahan Zinedine
  • 14,616
  • 5
  • 46
  • 70
  • 7
    Hopefully its needless to say but ill say it anyway. This can kill tables. If you go anywhere near production databases I'd recommend turning that flag back on as soon as you've made your change! – Robb Dec 09 '10 at 20:05
  • I totally agree, specially for large tables it could be really harmful. – Jahan Zinedine Dec 10 '10 at 06:10
  • This has worked for my problem. I was looking for this option in the table's properties. My current table has 66 rows and 1 FK to a language table. I'll try it out see if it breaks my stuff. Thanks!! – Sébastien Richer Dec 13 '10 at 18:48
7

Reordering columns is basically the same as remaking the table, which is the workaround if changes that require recreation are disabled:

SELECT (fields in DESIRED order)
INTO MyNewTable
FROM OldTable
JNK
  • 63,321
  • 15
  • 122
  • 138
6

You might be interested in the answers to this question.

As to why you can't reorder columns (aside from the SSMS-specific answers posted), I'm not sure you'll find a canonical answer, other than the SQL standard contains no programmatic syntax for doing so - the only way to redefine a table's schema (including the order of its columns) is to drop the table and recreate it. So any management application that provides column "reordering" will most likely be doing that behind the scenes (e.g. SSMS's Design View, as pointed out in the linked question).

Someone with deeper knowledge of the SQL specification could contradict this if there is an explicit reason for not providing a column-reordering mechanism as part of the standard DDL, but I can only conjecture that, since queries define the order of columns in their resultsets anyway, and DBMSes control physical storage according to their individual implementation, that logical column order is essentially meaningless and doesn't warrant such a mechanism.

Community
  • 1
  • 1
Dan J
  • 16,319
  • 7
  • 50
  • 82
  • 4
    The fact that column order provides no value in the context of any SQL statement is canonical to me -- you select whatever columns you like, in whatever order you desire. – OMG Ponies Dec 09 '10 at 19:50
  • Agreed. I guess I was trying to qualify my statement by pointing out that there may or may not be a more-explicit reason. But I agree that I don't think there needs to be one. – Dan J Dec 09 '10 at 19:58
  • I think this is the closedt to an answer I got. Thanks a lot! I think it makes sense that if there is no logical need for this, SQL creators would not include functionnality that leads to an almost useless feature. – Sébastien Richer Dec 13 '10 at 19:02
  • Often, I will `Select top 10 *` from a table to get the gist of the data. The ordering/grouping of columns can help a ton in aiding in the understanding of the tables. I often find that tables that have been added to numerous times over the years to be confusing to new colleagues vs tables with logically ordered columns. – Copy and Paste Feb 14 '22 at 17:30
1

If my suspicion is correct you have a setting turned on that you need to turn off to allow changes that require a table to be re-created to be saved.

Open SQL Management Studio and use the menu to go to Tools->Options

In the options menu on the left select Designers

In the Table Options pane on the right ensure that the check box next to the setting "Prevent saving changes that require table re-creation" is unchecked.

After you confirm that is done try reordering your columns again.

Ryan
  • 6,756
  • 13
  • 49
  • 68
1

What I want to know, is how come... I mean, I can drop a column... isn't that the same thing as adding a new one?

Dropping a column is based on column name, not ordinal position (first/second/third/etc column). Adding a column is always appended to the end of the list of columns. This reinforces that the sequence of columns is completely irrelevant to data besides SQL operations. I don't know how you see either as being similar...

No SQL databases I'm aware of support inserting columns in a particular order into an existing table. PHPMyAdmin provided functionality, but under the covers the function is creating a new table with the desired column order, copying the data from the old to new table, and finally deleting/dropping the old table.

Column order provides no benefit to operations...

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • 7
    It *does* provide a benefit to slightly-OCD developers, however... ;) – Dan J Dec 09 '10 at 19:59
  • @djacobson: I had to support DBAs with such OCD requirements -- waste of time, money & resources. Statistical fact that the more you change, the more that can/will go wrong. – OMG Ponies Dec 09 '10 at 20:02
  • Woof. That's unfortunate. Was your solution to write a script generator that would spit out a new `CREATE TABLE` statement for the given table with the columns in the desired order, along with the associated drop-table and copy-data statements, and execute that on the database? :) – Dan J Dec 09 '10 at 20:06
  • @djacobson: Worse - we were required to write & test scripts, and provide instructions... only to find that some DBAs would write their own entirely. We had to be 100% on the instructions, in the event that something failed blame could be properly attributed. – OMG Ponies Dec 09 '10 at 20:15
  • 1
    Well I see drop the same as add in that in order to drop a column, other columns will "move" in their order. And adding a column not at the end, would also "move" the other columns in their order. I agree that this is not beneficial to anything else than readibility of said tables in a GUI. But deep in there in the sql engine, what's stopping us from doing this... – Sébastien Richer Dec 13 '10 at 18:57
  • `Column order provides no benefit to operations`. So organization/logical groupings provide no value? I simply disagree.... Have a new employee `Select top 10 *` from a table with 100 attributes randomly arranged vs one logically organized. You will see very rapidly the benefit. – Copy and Paste Feb 14 '22 at 17:36
0

You do NOT want to do that unless the table has no data and is NOT on production! The reason is that it has to take the existing records out to a temp table, delete the old table, rename the temp table. This is a very bad thing to do to an existing table in production. It is stupid to rely on column order in queries anyway, so this is a completely unnecessary task. This is something that can cause big pain for literally no gain. Do not go down this road even if you find a workaround.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
  • Hi HLGEM, please read my post carefully, I do not really want to do this :) What I really want is to know why someone cannot do this. But thanks for the warnings though hehe :) – Sébastien Richer Dec 13 '10 at 18:58
  • You can do it only by creating a table and moving the data to it. That's why it is a poor idea and why it is not allowed in general becaue it can create tremendous problems with the existing database access if you do it. – HLGEM Dec 13 '10 at 19:33