如何在 sql server 2005 中使用 sql 查询更改表中的列顺序？

How to change column order in a table using sql query in sql server 2005?

提问by Himadri

How to change column order in a table using SQL query in SQL Server 2005?

如何在 SQL Server 2005 中使用 SQL 查询更改表中的列顺序？

I want to rearrange column order in a table using SQL query.

我想使用 SQL 查询重新排列表中的列顺序。

回答by marc_s

You cannot. The column order is just a "cosmetic" thing we humans care about - to SQL Server, it's almost always absolutely irrelevant.

你不能。列顺序只是我们人类关心的“装饰性”事情——对于 SQL Server，它几乎总是完全无关紧要的。

What SQL Server Management Studio does in the background when you change column order there is recreating the table from scratch with a new CREATE TABLEcommand, copying over the data from the old table, and then dropping it.

当您更改列顺序时，SQL Server Management Studio 在后台执行的操作是使用新CREATE TABLE命令从头开始重新创建表，复制旧表中的数据，然后删除它。

There is no SQL command to define the column ordering.

没有定义列排序的 SQL 命令。

回答by lexu

You have to explicitly list the fields in the order you want them to be returned instead of using * for the 'default' order.

您必须按照希望返回的顺序明确列出字段，而不是使用 * 作为“默认”顺序。

original query:

原始查询：

select * from foobar

returns

回报

foo bar
--- ---
  1   2

now write

现在写

select bar, foo from foobar

bar foo
--- ---
  2   1

回答by robotik

according to http://msdn.microsoft.com/en-us/library/aa337556.aspx

根据http://msdn.microsoft.com/en-us/library/aa337556.aspx

This task is not supported using Transact-SQL statements.

使用 Transact-SQL 语句不支持此任务。

Well, it can be done, using create/ copy / drop/ rename, as answered by komma8.komma1

好吧，可以使用create/copy drop// rename来完成，正如komma8.komma1所回答的

Or you can use SQL Server Management Studio

或者您可以使用 SQL Server Management Studio

1. In Object Explorer, right-click the table with columns you want to reorder and click Design(Modify in ver. 2005 SP1 or earlier)
2. Select the box to the left of the column name that you want to reorder. (You can select multiple columns by holding the [shift] or the [ctrl] keys on your keyboard.)
3. Drag the column(s) to another location within the table.

1. 在对象资源管理器中，右键单击包含要重新排序的列的表，然后单击设计（在 2005 SP1 或更早版本中修改）
2. 选择要重新排序的列名称左侧的框。（您可以通过按住键盘上的 [shift] 或 [ctrl] 键来选择多列。）
3. 将列拖到表中的另一个位置。

Then click save. This method actually drops and recreates the table, so some errors might occur.

然后点击保存。此方法实际上是删除并重新创建表，因此可能会出现一些错误。

If Change Trackingoption is enabled for the database and the table, you shouldn't use this method.

如果为数据库和表启用了更改跟踪选项，则不应使用此方法。

If it is disabled, the Prevent saving changes that require the table re-creationoption should be cleared in Tools menu > Options > Designers, otherwise "Saving changes is not permitted"error will occur.

如果禁用，则应在工具菜单>选项>设计器中清除阻止保存需要重新创建表的更改选项，否则将出现“不允许保存更改”错误。

• Disabling the Prevent saving changes that require the table re-creationoption is strongly advised against by Microsoft, as it leads to the existing change tracking information being deleted when the table is re-created, so you should never disable this option if Change Tracking is enabled!

• Microsoft 强烈建议不要禁用阻止保存需要重新创建表的更改选项，因为这会导致在重新创建表时删除现有的更改跟踪信息，因此如果更改跟踪是启用！

Problems may also arise during primary and foreign key creation.

在主键和外键创建过程中也可能出现问题。

If any of the above errors occurs, saving fails which leaves you with the original column order.

如果发生上述任何错误，保存将失败，从而保留原始列顺序。

回答by Thorsten

This is similar to the question on ordering the records in the result of a query .. and typically no one likes the formally correct answer ;-)

这类似于在查询结果中对记录进行排序的问题..通常没有人喜欢正式的正确答案;-)

So here it goes:

所以这里是这样的：

• as per SQL standard, the columns in a table are not "ordered"
• as a result, a select *does not force the columns to be returned in a particular order
• typically, each RDBMS has a kind of "default" order (usually the order that the columns were added to the table, either in the create table' or in thealter table add ` statements
• therefore, if you rely on the order of columns (because you are using the results of a query to poulate some other datastructure from the position of the columns), explicitly list the columns in the order you want them.

• 根据 SQL 标准，表中的列不是“有序的”
• 因此， aselect *不会强制以特定顺序返回列
• 通常，每个 RDBMS 都有一种“默认”顺序（通常是将列添加到表中的顺序，或者在create table' or in thealter table add ` statements
• 因此，如果您依赖列的顺序（因为您正在使用查询结果从列的位置填充其他数据结构），请按照您希望的顺序明确列出列。

回答by mevdiven

You can of course change the order of the columns in a sql statement. However if you want to abstract tables' physical column order, you can create a view. i.e

您当然可以更改 sql 语句中列的顺序。但是，如果您想抽象表的物理列顺序，则可以创建一个视图。IE

CREATE TABLE myTable(
    a int NULL,
    b varchar(50) NULL,
    c datetime NULL
);


CREATE VIEW vw_myTable
AS
SELECT c, a, b
  FROM myTable;

select * from myTable;
a  b  c
-  -  -

select * from vw_myTable
c  a  b
-  -  -

回答by Tim Connolly

In SQLServer Management Studio:

在 SQLServer 管理工作室中：

Tools -> Options -> Designers -> Table and Database Designers

工具 -> 选项 -> 设计器 -> 表和数据库设计器

• Unselect 'Prevent saving changes that require table re-creation'.

• 取消选择“防止保存需要重新创建表的更改”。

Then:

然后：

• right click the table you want to re-order the columns for.
• click 'Design'.
• Drag the columns to the order you want.
• finally, click save.

• 右键单击要为其重新排序列的表。
• 点击“设计”。
• 将列拖到所需的顺序。
• 最后，点击保存。

SQLServer Management studio will drop the table and recreate it using the data.

SQLServer Management studio 将删除该表并使用数据重新创建它。

回答by komma8.komma1

You can do it by creating a new table, copy all the data over, drop the old table, then renaming the new one to replace the old one.

您可以通过创建一个新表，复制所有数据，删除旧表，然后重命名新表以替换旧表来实现。

You could also add new columns to the table, copy the column by column data over, drop the old columns, then rename new columns to match the old ones. A simple example below: http://sqlfiddle.com/#!3/67af4/1

您还可以向表中添加新列，逐列复制数据，删除旧列，然后重命名新列以匹配旧列。下面是一个简单的例子：http: //sqlfiddle.com/#!3/67af4/1

CREATE TABLE TestTable (
    Column1 INT,
    Column2 VARCHAR(255)
);
GO

insert into TestTable values(1, 'Test1');
insert into TestTable values(2, 'Test2');
GO

select * from TestTable;
GO

ALTER TABLE TestTable ADD Column2_NEW VARCHAR(255);
ALTER TABLE TestTable ADD Column1_NEW INT;
GO

update TestTable 
set Column1_NEW = Column1, 
    Column2_NEW = Column2;
GO

ALTER TABLE TestTable DROP COLUMN Column1;
ALTER TABLE TestTable DROP COLUMN Column2;
GO

sp_rename 'TestTable.Column1_NEW', 'Column1', 'COLUMN';
GO
sp_rename 'TestTable.Column2_NEW', 'Column2', 'COLUMN';
GO

select * from TestTable;
GO

回答by vikas Chaturvedi

In SQLServer Management Studio:

在 SQLServer 管理工作室中：

Tools-> Options-> Designers-> Table and Database Designers

Tools-> Options-> Designers->Table and Database Designers

Unselect Prevent saving changes that require table re-creation.

取消选择Prevent saving changes that require table re-creation。

Now you can reorder the table.

现在您可以对表重新排序。

回答by Subhankar

Sql server internally build the script. It create a temporary table with new changes and copy the data and drop current table then recreate the table insert from temp table. I find it from "Generate Change script" option ssms 2014. Script like this. From Here: How to change column order in a table using sql query

Sql server 内部构建脚本。它创建一个具有新更改的临时表并复制数据并删除当前表，然后从临时表重新创建表插入。我从“生成更改脚本”选项 ssms 2014 中找到它。像这样的脚本。从这里：如何使用 sql 查询更改表中的列顺序

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_emps
    (
    id int NULL,
    ename varchar(20) NULL
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_emps SET (LOCK_ESCALATION = TABLE)
GO
IF EXISTS(SELECT * FROM dbo.emps)
     EXEC('INSERT INTO dbo.Tmp_emps (id, ename)
        SELECT id, ename FROM dbo.emps WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.emps
GO
EXECUTE sp_rename N'dbo.Tmp_emps', N'emps', 'OBJECT' 
GO
COMMIT

回答by Hemanth Nukala

If your table has enough columns then you can try this. First create a new table with preferred order of columns.

如果你的表有足够多的列，那么你可以试试这个。首先创建一个具有首选列顺序的新表。

    create table new as select column1,column2,column3,....columnN from table_name;

Now drop the table using drop command

现在使用 drop 命令删除表

    drop table table_name;

now rename the newly created table to your old table name.

现在将新创建的表重命名为您的旧表名。

    rename new to table_name;

now select the table, you have your columns rearranged as you preferred before.

现在选择表格，您可以根据以前的喜好重新排列列。

    select * from table_name;