SQL Server - 任何添加列并使其成为表中第一列的方法？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26411111/
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
SQL Server -- Any way to add a column and make it first column in table?
提问by Joe Baker
I'm altering an existing table to add an Identity column. That I can do, no problem.
我正在更改现有表以添加标识列。我能做的，没问题。
But I'm wanting to be sure that people who look at it in the future will see that it has the identity column added, so I really want to make it column 1. I know this is totally inconsequential to the system's operation; it's strictly for human reading.
但是我想确保将来查看它的人会看到它添加了标识列，所以我真的想将它设为第 1 列。我知道这对系统的操作完全无关紧要；它严格用于人类阅读。
Does anyone know of a way to do this? I've looked at the TSQL syntax for Alter Table and for column_definition, and don't see anything; but I'm hoping someone knows of a way to make this happen.
有谁知道这样做的方法吗？我查看了 Alter Table 和 column_definition 的 TSQL 语法，但什么也没看到；但我希望有人知道实现这一目标的方法。
FWIW, this is a one-time operation (but on many servers, so it needs to be automated), so I'm not worried whether any "trick" might go away in the future -- as long as it works now. We're using recent versions of SQL Server Express.
FWIW，这是一次性操作（但在许多服务器上，因此需要自动化），所以我不担心将来是否有任何“技巧”会消失——只要它现在有效。我们使用的是最新版本的 SQL Server Express。
Thanks for any suggestions.
感谢您的任何建议。
采纳答案by geoandri
回答by timo.rieber
Solve this by following these steps:
请按照以下步骤解决此问题：
-- First, add identity column
alter table
mytable
add
id int identity(1, 1) not null
-- Second, create new table from existing one with correct column order
select
id,
col1,
col2
into
newtable
from
mytable
Now you've got newtablewith reordered columns. If you need to you can drop your mytableand rename newtableto mytable:
现在你已经有了newtable重新排序的列。如果需要，您可以删除mytable并重命名newtable为mytable：
drop table
mytable
exec sp_rename
'newtable', 'mytable'
回答by Shekhar Pankaj
It is not possible with ALTERstatement. If you wish to have the columns in a specific order, you will have to create a newtable, use INSERT INTO newtable (col-x,col-a,col-b)SELECT col-x,col-a,col-bFROM oldtable to transfer the data from the oldtable to the newtable, delete the oldtable and rename the newtable to the oldtable name.
ALTER声明是不可能的。如果您希望按特定顺序排列列，则必须创建一个新表，使用INSERT INTO newtable (col-x,col-a,col-b)SELECT col-x,col-a,col-bFROM oldtable 将数据从旧表传输到新表，删除旧表并将新表重命名为旧表名称。
This is not necessarily recommended because it does not matter which order the columns are in the database table. When you use a SELECTstatement, you can name the columns and have them returned to you in the order that you desire.
这不一定是推荐的，因为列在数据库表中的顺序无关紧要。当您使用SELECT语句时，您可以命名列并按照您希望的顺序将它们返回给您。
USING OBJECT EXPLORE
使用对象探索
Avoid this step.. because ssms tools gives you to do light Data administration, while going for changes with multiple column record ,you may end with loosing some data..etc..because how fast your processor is it will always hang for changing architecture..
避免这一步..因为 ssms 工具可以让您轻松进行数据管理，同时进行多列记录的更改，您可能会丢失一些数据..等等..因为您的处理器有多快它总是会因更改架构而挂起..
And once data lost..you will be no where to fetch them back...happened with me oncw..
一旦数据丢失..你将无处可取......发生在我身上..
