为什么我不能重新排序我的 SQL Server 列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4402312/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why can't I reorder my SQL Server columns?
提问by Sébastien Richer
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.
我有一张旧桌子，里面有 FK。我想添加一个新列。我想让这个新列成为我的主键。所以我想我可以将该列作为第一列插入，也可以将它插入到表格的末尾，然后重新排列我的列。
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.
但是 SQL Server Management Studio 不允许我这样做。我知道我不能这样做，而且列顺序在 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.
我只是想了解发生了什么。我也很难找到相关的文档。如果有人能指出我好的方向。
采纳答案by Dan J
You might be interested in the answers to this question.
您可能对这个问题的答案感兴趣。
As to whyyou 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).
至于为什么不能对列重新排序（除了发布的特定于 SSMS 的答案），我不确定您会找到规范的答案，除了 SQL 标准不包含这样做的编程语法 - 这是唯一的方法重新定义表的模式（包括其列的顺序）是删除表并重新创建它。因此，任何提供列“重新排序”的管理应用程序很可能会在后台执行此操作（例如，如链接问题中所指出的，SSMS 的设计视图）。
Someone with deeper knowledge of the SQL specification could contradict this if there isan 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.
有人用SQL规范的较深的造诣会反驳这一点，如果有是不提供重新排序列机制为标准DDL的一部分明确的原因，但我只能猜想，因为查询其结果集定义列的顺序呢和 DBMS 根据各自的实现控制物理存储，逻辑列顺序本质上是没有意义的，并且不保证这种机制。
回答by Jahan
Definitely you can. Uncheck this option in SQL Server Management Studio:
Tools > Options > Designers > Prevent saving changes that require table recreation.
绝对可以。在 SQL Server Management Studio 中取消选中此选项：
工具 > 选项 > 设计器 > 防止保存需要重新创建表的更改。
Please don't do this in production unless you know implications!
除非您知道其中的含义，否则请不要在生产中这样做！
回答by JNK
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
回答by Ryan
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
打开 SQL Management Studio 并使用菜单转到 Tools->Options
In the options menu on the left select Designers
在左侧的选项菜单中选择 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.
确认完成后，再次尝试重新排序您的列。
回答by OMG Ponies
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...
删除列基于列名，而不是顺序位置（第一/第二/第三/等列）。添加一列总是附加到列列表的末尾。这强化了列序列与 SQL 操作之外的数据完全无关。不知道你们怎么看的相似...
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.
我知道没有 SQL 数据库支持以特定顺序将列插入到现有表中。PHPMyAdmin 提供了功能，但在幕后，该功能正在创建一个具有所需列顺序的新表，将数据从旧表复制到新表，最后删除/删除旧表。
Column order provides no benefit to operations...
列顺序对操作没有任何好处......
回答by HLGEM
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.
除非表没有数据并且不在生产中，否则您不想这样做！原因是它必须将现有记录取出到临时表中，删除旧表，重命名临时表。这对生产中的现有表来说是一件非常糟糕的事情。无论如何在查询中依赖列顺序是愚蠢的，所以这是一个完全不必要的任务。这可能会导致巨大的痛苦，而实际上没有任何收益。即使找到了解决方法，也不要走这条路。
