为什么我不能重新排序我的 SQL Server 列? [英] Why can't I reorder my SQL Server columns?

问题描述

我有一张旧桌子，里面有 FK.我想添加一个新列.我想让这个新列成为我的主键.所以我想我可以将该列作为第一列插入，也可以将其插入到表格的末尾，然后重新排列我的列.

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.

但是 SQL Server Management Studio 不允许我这样做.我知道我不能这样做，而且列顺序在 SQL 中几乎完全无关.

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.

推荐答案

您可能对 这个问题.

至于为什么您不能对列重新排序(除了发布的 SSMS 特定答案)，我不确定您会找到规范的答案，除了 SQL 标准不包含这样做的编程语法 - 重新定义表的模式(包括其列的顺序)的唯一方法是删除表并重新创建它.因此，任何提供列重新排序"的管理应用程序很可能会在幕后执行此操作(例如，链接问题中指出的 SSMS 的设计视图).

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).

如果有明确的理由不提供列重新排序机制作为标准 DDL 的一部分，那么对 SQL 规范有更深入了解的人可能会反驳这一点，但我只能推测，因为无论如何，查询定义了其结果集中列的顺序，而 DBMS 根据各自的实现来控制物理存储，逻辑列顺序本质上是没有意义的，并且不保证这种机制.

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.

这篇关于为什么我不能重新排序我的 SQL Server 列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！