blocks|key|1211229|text|你不能。列顺序只是我们人类关心的一个“表面”的东西--对于SQL+Server来说，它几乎总是完全无关紧要的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1211230|当您更改列顺序时，SQL+Server+Management+Studio在后台执行的操作是使用新的CREATE+TABLE命令从头重新创建表，复制旧表中的数据，然后删除该表。|offset|length|style|CODE|1211231|没有用于定义列排序的SQL命令。|1211232|entityMap^0|0|1E|C|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@$D|N|E|O|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|K|$]]

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

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

There is no SQL command to define the column ordering.

blocks|key|1426005|text|你必须按照你想要的返回顺序来显式地列出字段，而不是使用*来表示“默认”顺序。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1426006|原始查询：|1426007|select+*+from+foobar|code-block|syntax|javascript|1426008|返回|1426009|foo+bar
---+---
++1+++2|1426010|现在写下|1426011|select+bar,+foo+from+foobar

bar+foo
---+---
++2+++1|1426012|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|V|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|W|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|P|5|F|7|Y|8|@]|9|@]|A|$G|H]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

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:

<pre><code>select * from foobar
</code></pre>

returns

<pre><code>foo bar
--- ---
 1 2
</code></pre>

now write

<pre><code>select bar, foo from foobar

bar foo
--- ---
 2 1
</code></pre>

blocks|key|1426170|text|当然，您可以更改sql语句中列的顺序。然而，如果你想抽象表的物理列顺序，你可以创建一个视图。i.e|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1426171|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
-++-++-|code-block|syntax|javascript|1426172|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>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
- - -
</code></pre>

blocks|key|1211323|text|您可以创建一个新表，复制所有数据，删除旧表，然后重命名新表以替换旧表。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1211324|您还可以向表中添加新列，逐列复制数据，删除旧列，然后重命名新列以匹配旧列。下面是一个简单的例子：http://sqlfiddle.com/#!3/67af4/1|offset|length|1211325|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|code-block|syntax|javascript|1211326|entityMap|0|LINK|mutability|MUTABLE|url|http://sqlfiddle.com/#!3/67af4/1^0|0|1C|W|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@$D|U|E|V|1|W]]|A|$]]|$1|F|3|G|5|H|7|X|8|@]|9|@]|A|$I|J]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

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:
<a href="http://sqlfiddle.com/#!3/67af4/1" rel="noreferrer">http://sqlfiddle.com/#!3/67af4/1</a>

<pre><code>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
</code></pre>

blocks|key|1211752|text|在SQLServer+Management+Studio中：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1211753|Tools+->+Options+->+Designers+->+Table+and+Database+Designers|offset|length|style|CODE|1211754|取消选择Prevent+saving+changes+that+require+table+re-creation。|1211755|现在，您可以对表进行重新排序。|1211756|entityMap^0|0|0|5|9|7|K|9|X|S|0|4|1H|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@$D|P|E|Q|F|G]|$D|R|E|S|F|G]|$D|T|E|U|F|G]|$D|V|E|W|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|X|8|@$D|Y|E|Z|F|G]]|9|@]|A|$]]|$1|J|3|K|5|6|7|10|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|M|$]]

In SQLServer Management Studio:

<code>Tools</code> -> <code>Options</code> -> <code>Designers</code> -> <code>Table and Database Designers</code>

Unselect <code>Prevent saving changes that require table re-creation</code>.

Now you can reorder the table.

blocks|key|1426377|text|如果您的表有足够的列，那么您可以尝试这样做。首先，创建一个具有首选列顺序的新表。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1426378|++++create+table+new+as+select+column1,column2,column3,....columnN+from+table_name;|code-block|syntax|javascript|1426379|现在使用drop命令删除该表|1426380|++++drop+table+table_name;|1426381|现在将新创建的表重命名为旧的表名。|1426382|++++rename+new+to+table_name;|1426383|现在选择表，您可以按照您喜欢的方式重新排列列。|1426384|++++select+*+from+table_name;|1426385|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|Y|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|10|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

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

<pre><code> create table new as select column1,column2,column3,....columnN from table_name;
</code></pre>

Now drop the table using drop command

<pre><code> drop table table_name;
</code></pre>

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

<pre><code> rename new to table_name;
</code></pre>

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

<pre><code> select * from table_name;
</code></pre>

blocks|key|1426553|text|Sql+server在内部生成脚本。它创建一个具有新更改的临时表，复制数据并删除当前表，然后重新创建表insert+from+temp+table。我在“生成变更脚本”选项ssms+2014中找到了它。像这样的脚本。来自Here:+How+to+change+column+order+in+a+table+using+sql+query|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1426554|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|code-block|syntax|javascript|1426555|entityMap|0|LINK|mutability|MUTABLE|url|http://www.abahan.com/Article/view-article.aspx?q=How%2520to%2520change%2520column%2520order%2520in%2520a%2520table%2520using%2520sql%2520query-385^0|32|1N|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

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 <a href="http://www.abahan.com/Article/view-article.aspx?q=How%20to%20change%20column%20order%20in%20a%20table%20using%20sql%20query-385" rel="nofollow">Here: How to change column order in a table using sql query</a>

<pre><code>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
</code></pre>

blocks|key|1426261|text|归根结底，您根本不能在MS+SQL中做到这一点。我最近使用一个读取查找表的存储过程创建了运行中(应用程序启动)的表。当我创建一个视图，将它们与我之前手动创建的另一个表(相同的模式，包含数据)组合在一起时，它失败了-只是因为我对视图使用了''Select+*+UNION+Select+*‘。同时，如果我只使用通过存储过程创建的那些，我就成功了。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1426262|总而言之:如果有任何依赖于列顺序的应用程序，它确实不是好的编程，并且肯定会在未来产生问题。列应该“感觉”在任何地方，并且可以用于任何数据处理(插入、更新、选择)。|1426263|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

At the end of the day, you simply cannot do this in MS SQL. I recently created tables on the go (application startup) using a stored Procedure that reads from a lookup table. When I created a view that combined these with another table I had manually created earlier one (same schema, with data), It failed - simply because I was using ''Select * UNION Select * ' for the view. At the same time, if I use only those created through the stored procedure, I am successful.

In conclusion: If there is any application which depends on the order of column it is really not good programming and will for sure create problems in the future. Columns should 'feel' free to be anywhere and be used for any data process (INSERT, UPDATE, SELECT).

blocks|key|1211716|text|如果要重新排序的列是最近创建的并且是空的，则可以按正确的顺序删除和重新添加这些列。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1211717|这发生在我身上，我手动扩展数据库以添加新功能，我遗漏了一列，当我添加它时，顺序是不正确的。|1211718|在这里找不到合适的解决方案后，我使用以下命令简单地更正了该表。|1211719|ALTER+TABLE++tablename++DROP+COLUMN++columnname;+
ALTER+TABLE++tablename++ADD+columnname+columntype;|code-block|syntax|javascript|1211720|注意:只有在要删除的列中没有数据时才执行此操作。|1211721|人们说过，列的顺序并不重要。我经常使用SQL+Server+Management+Studio的“生成脚本”来创建数据库架构的文本版本。为了有效地版本控制这些脚本(git)并比较它们(WinMerge)，来自兼容数据库的输出必须是相同的，并且突出显示的差异是真正的数据库差异。|1211722|列顺序确实很重要；但只对某些人，而不是对所有人！|1211723|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|U|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|V|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|W|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|Y|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

If the columns to be reordered have recently been created and are empty, then the columns can be deleted and re-added in the correct order.

This happened to me, extending a database manually to add new functionality, and I had missed a column out, and when I added it, the sequence was incorrect.

After finding no adequate solution here I simply corrected the table using the following kind of commands.

<pre><code>ALTER TABLE tablename DROP COLUMN columnname; 
ALTER TABLE tablename ADD columnname columntype;
</code></pre>

Note: only do this if you don't have data in the columns you are dropping.

People have said that column order does not matter. I regularly use SQL Server Management Studio "generate scripts" to create a text version of a database's schema. To effectively version control these scripts (git) and to compare them (WinMerge), it is imperative that the output from compatible databases is the same, and the differences highlighted are genuine database differences.

Column order does matter; but just to some people, not to everyone!

blocks|key|1211156|text|使用|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1211157|SELECT+*+FROM+TABLE1|code-block|syntax|javascript|1211158|它显示表的默认列顺序。|1211159|如果要更改列的顺序，请执行以下操作。|1211160|指定要相应显示的列名|1211161|SELECT+COLUMN1,+COLUMN5,+COLUMN4,+COLUMN3,+COULMN2+FROM+TABLE1|1211162|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

Use

<pre><code>SELECT * FROM TABLE1
</code></pre>

which displays the default column order of the table.

If you want to change the order of the columns.

Specify the column name to display correspondingly

<pre><code>SELECT COLUMN1, COLUMN5, COLUMN4, COLUMN3, COULMN2 FROM TABLE1
</code></pre>

blocks|key|1211396|text|您可以使用索引..索引后，if+select+*+from+XXXX结果应与索引一致，但仅限于结果集。表的非结构化|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1211397|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

you can use indexing.. After indexing, if select * from XXXX results should be as per the index, But only result set.. not structrue of Table

blocks|key|1211812|text|为了有一个特定的列顺序，你需要按你想要的顺序逐列选择。选择顺序指定列在输出中的排序方式。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1211813|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

In order to have a specific column order You need to select column by column in the order You wish.
Selection order dictates how columns will be ordered in output.

blocks|key|1211842|text|尝试执行以下命令：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1211843|alter+table+students+modify+age+int(5)+first;+|code-block|syntax|javascript|1211844|这会将age的位置更改为第一个位置。|1211845|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Try this command:

<pre><code>alter table students modify age int(5) first; 
</code></pre>

This will change the position of age to the first position.

blocks|key|1426809|text|我想您想要在特定位置添加一个新列。可以通过向右移动当前列来创建新列。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1426810|%2B---%2B---%2B---%2B
%7C+A+%7C+B+%7C+C+%7C
%2B---%2B---%2B---%2B|code-block|syntax|javascript|1426811|删除所有受影响的索引和外键引用。添加一个数据类型与上一列完全相同的新列，并将数据复制到该列。|1426812|%2B---%2B---%2B---%2B---%2B
%7C+A+%7C+B+%7C+C+%7C+C+%7C
%2B---%2B---%2B---%2B---%2B
++++++++++%7C___%5E|1426813|将第三列的Change+data+type转换为与上一列相同的类型，并将数据复制到该列。|offset|length|1426814|%2B---%2B---%2B---%2B---%2B
%7C+A+%7C+B+%7C+B+%7C+C+%7C+
%2B---%2B---%2B---%2B---%2B
++++++%7C___%5E|1426815|相应地重命名列，重新创建已删除的索引和外键引用。|1426816|%2B---%2B---%2B---%2B---%2B
%7C+A+%7C+D+%7C+B+%7C+C+%7C+
%2B---%2B---%2B---%2B---%2B|1426817|更改第二列的数据类型。|1426818|请记住，列顺序只是像marc_s+said一样的“装饰品”。|1426819|entityMap|0|LINK|mutability|MUTABLE|url|https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-conversion-database-engine?view=sql-server-ver15|1|https://stackoverflow.com/users/13302/marc-s|2|https://stackoverflow.com/a/1605167/85113^0|0|0|0|0|5|G|0|0|0|0|0|0|A|6|1|H|4|2|0^^$0|@$1|2|3|4|5|6|7|1A|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|1B|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|1C|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|1D|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|1E|8|@]|9|@$M|1F|N|1G|1|1H]]|A|$]]|$1|O|3|P|5|D|7|1I|8|@]|9|@]|A|$E|F]]|$1|Q|3|R|5|6|7|1J|8|@]|9|@]|A|$]]|$1|S|3|T|5|D|7|1K|8|@]|9|@]|A|$E|F]]|$1|U|3|V|5|6|7|1L|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|1M|8|@]|9|@$M|1N|N|1O|1|1P]|$M|1Q|N|1R|1|1S]]|A|$]]|$1|Y|3|-4|5|6|7|1T|8|@]|9|@]|A|$]]]|Z|$10|$5|11|12|13|A|$14|15]]|16|$5|11|12|13|A|$14|17]]|18|$5|11|12|13|A|$14|19]]]]

I suppose you want to add a new column in a specific position. You can create a new column by moving current columns to the right.

<pre><code>+---+---+---+
| A | B | C |
+---+---+---+
</code></pre>

Remove all affected indexes and foreign key references. Add a new column with the exact same data type like the last column and copy data there.

<pre><code>+---+---+---+---+
| A | B | C | C |
+---+---+---+---+
 |___^
</code></pre>

<a href="https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-conversion-database-engine?view=sql-server-ver15" rel="nofollow noreferrer">Change data type</a> of the third column to the same type like the previous column and copy data there. 

<pre><code>+---+---+---+---+
| A | B | B | C | 
+---+---+---+---+
 |___^
</code></pre>

Rename columns accordingly, recreate removed indexes and foreign key references.

<pre><code>+---+---+---+---+
| A | D | B | C | 
+---+---+---+---+
</code></pre>

Change data type of the second colum.

Keep in mind that the column order is just a "cosmetic" thing like <a href="https://stackoverflow.com/users/13302/marc-s">marc_s</a> <a href="https://stackoverflow.com/a/1605167/85113">said</a>.

blocks|key|1211432|text|您可以使用SQL查询来更改这一点。以下是用于更改列顺序的sql查询。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1211433|ALTER+TABLE+table+name+
CHANGE+COLUMN+`column1`+`column1`+INT(11)+NOT+NULL+COMMENT+''+AFTER+`column2`;|code-block|syntax|javascript|1211434|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can change this using SQL query. Here is sql query to change the sequence of column.

<pre><code>ALTER TABLE table name 
CHANGE COLUMN `column1` `column1` INT(11) NOT NULL COMMENT '' AFTER `column2`;
</code></pre>

blocks|key|1426456|text|alter+table+name+modify+columnname+(5)+first；将该列带到alter+table+name+modify+columnname+(5)+first+(Tablename)之后；|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1426457|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

alter table name modify columnname int(5) first; 
will bring the column to first 
alter table name modify columnname int(5) after (tablename);

blocks|key|1426840|text|这对我在Oracle+DB上很有效：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1426841|select+column1,+column2,+t.*+from+table+t|code-block|syntax|javascript|1426842|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This worked for me on Oracle DB:
<pre><code>select column1, column2, t.* from table t
</code></pre>

blocks|key|1211593|text|示例:在表态field_price之后改变field_priority的位置。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1211594|ALTER+TABLE+`status`+CHANGE+`priority`+`priority`+INT(11)+NULL+DEFAULT+NULL+AFTER+`price`;|code-block|syntax|javascript|1211595|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Example: Change position of field_priority after field_price in table status.

<pre><code>ALTER TABLE `status` CHANGE `priority` `priority` INT(11) NULL DEFAULT NULL AFTER `price`;
</code></pre>

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

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

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

SQL Server

数据库

如何在SQL Server2005中使用SQL query更改表中的列顺序？我想使用SQL查询重新排列表中的列顺序。

问如何使用sql server2005中的sql查询更改表中的列顺序？
EN

回答 18

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何使用sql server2005中的sql查询更改表中的列顺序？EN

回答 18

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何使用sql server2005中的sql查询更改表中的列顺序？
EN