在 SQL Server 2005 中的现有列之后添加新列的 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4732415/
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 Query to add a new column after an existing column in SQL Server 2005
提问by Aayushi
I need a SQL query which add a new column after an existing column, so the column will be added in a specific order.
我需要一个 SQL 查询，它在现有列之后添加一个新列，因此该列将按特定顺序添加。
Please suggest me if any ALTERquery which do that.
请建议我是否有任何ALTER查询。
回答by Brad Christie
Microsoft SQL (AFAIK) does not allow you to alter the table and add a column after a specific column. Your best bet is using Sql Server Management Studio, or play around with either dropping and re-adding the table, or creating a new table and moving the data over manually. neither are very graceful.
Microsoft SQL (AFAIK) 不允许您更改表并在特定列之后添加列。最好的办法是使用 Sql Server Management Studio，或者尝试删除和重新添加表，或者创建一个新表并手动移动数据。两者都不是很优雅。
MySQLdoes however:
然而，MySQL确实：
ALTER TABLE mytable
ADD COLUMN new_column <type>
AFTER existing_column
回答by gbn
ALTER won't do it because column order does not matterfor storage or querying
ALTER 不会这样做，因为列顺序对于存储或查询无关紧要
If SQL Server, you'd have to use the SSMS Table Designer to arrange your columns, which can then generate a script which drops and recreates the table
如果是 SQL Server，则必须使用 SSMS 表设计器来排列列，然后可以生成删除并重新创建表的脚本
Edit Jun 2013
2013 年 6 月编辑
Cross link to my answer here: Performance / Space implications when ordering SQL Server columns?
在此处交叉链接到我的答案：订购 SQL Server 列时的性能/空间影响？
回答by ilans
It's possible.
这是可能的。
First, just add each column the usual way (as the last column).
首先，只需以通常的方式添加每一列（作为最后一列）。
Secondly, in SQL Server Management StudioGet into Tools => Options.
其次，SQL Server Management Studio进入工具 => 选项。
Under 'Designers' Tab => 'Table and Database Designers' menu, uncheck the option 'Prevent saving changes that require table re-creation'.
在“设计器”选项卡 =>“表和数据库设计器”菜单下，取消选中“防止保存需要重新创建表的更改”选项。
Afterwards, right click on your table and choose 'Design'. In 'Design' mode just drag the columns to order them.
然后，右键单击您的表格并选择“设计”。在“设计”模式下，只需拖动列即可对其进行排序。
Don't forget to save.
不要忘记保存。
回答by ashish.chotalia
If you want to alter order for columns in Sql server, There is no direct way to do this in SQL Server currently.
如果要更改 Sql server 中列的顺序，目前在 SQL Server 中没有直接的方法可以做到这一点。
Have a look at http://blog.sqlauthority.com/2008/04/08/sql-server-change-order-of-column-in-database-tables/
看看http://blog.sqlauthority.com/2008/04/08/sql-server-change-order-of-column-in-database-tables/
You can change order while edit design for table.
您可以在编辑表格设计时更改顺序。
回答by wwmbes
First add the new column to the old table through SSMStudio. Go to the database >> table >> columns. Right click on columns and choose new column. Follow the wizard. Then create the new table with the columns ordered as desired as follows:
首先通过 SSMStudio 将新列添加到旧表中。转到数据库 >> 表 >> 列。右键单击列并选择新列。按照向导操作。然后创建新表，列按需要排序，如下所示：
select * into my_new_table from (
select old_col1, my_new_col, old_col2, old_col3
from my_old_table
) as A
;
Then rename the tables as desired through SSMStudio. Go to the database >> table >> choose rename.
然后通过 SSMStudio 根据需要重命名表。转到数据库>>表>>选择重命名。
回答by Jake Pogorelec
It is a bad idea to select * from anything, period. This is why SSMS adds every field name, even if there are hundreds, instead of select *. It is extremely inefficient regardless of how large the table is. If you don't know what the fields are, its still more efficient to pull them out of the INFORMATION_SCHEMA database than it is to select *.
从任何东西中选择 * 是一个坏主意，句号。这就是为什么 SSMS 添加每个字段名称，即使有数百个，而不是 select *。无论表有多大，它的效率都非常低。如果您不知道这些字段是什么，那么将它们从 INFORMATION_SCHEMA 数据库中提取出来比选择 * 更有效。
A better query would be:
更好的查询是：
SELECT
COLUMN_NAME,
Case
When DATA_TYPE In ('varchar', 'char', 'nchar', 'nvarchar', 'binary')
Then convert(varchar(MAX), CHARACTER_MAXIMUM_LENGTH)
When DATA_TYPE In ('numeric', 'int', 'smallint', 'bigint', 'tinyint')
Then convert(varchar(MAX), NUMERIC_PRECISION)
When DATA_TYPE = 'bit'
Then convert(varchar(MAX), 1)
When DATA_TYPE IN ('decimal', 'float')
Then convert(varchar(MAX), Concat(Concat(NUMERIC_PRECISION, ', '), NUMERIC_SCALE))
When DATA_TYPE IN ('date', 'datetime', 'smalldatetime', 'time', 'timestamp')
Then ''
End As DATALEN,
DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
Where
TABLE_NAME = ''
