该表是关系模型中的一组集合。由于行之间没有明确的顺序，因此列之间也没有明确的顺序。但是您可以在投影期间通过指定所需的列而不是使用 * 重新排序它们。



但当然在您的情况下，RDBMS，SQL服务器仍在使用某些排序。您无法在特定位置添加字段，但可以更改字段顺序。让我引用这里的答案： http://www.sqlteam.com/forums/topic.asp？ TOPIC_ID = 58912 [ ^ ]

The table is a set of sets in relational model. As there is no explicit order between the rows, there is no explicit order between the columns either. But you can reorder them during projection by specifying the columns you need instead of using *.

But of course the RDBMS, SQL server in your case is still using some ordering. You can't really add the field at specific position, but you can change field order. Let me quote the answer from here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=58912[^]

Information_Schema.Columns表有一个ORDINAL_POSITION字段。是否可以通过ALTER TABLE简单地添加所需的列然后更改Information_Schema.Columns中表的ORDINAL_POSITION？



我没试过这个，但似乎以下方法可能有效。此示例假定有一个包含2列的表。基本上添加新列，将其分配到位置1，然后将旧列分配到位置2.



- 添加列

如果不是EXISTS（SELECT * FROM [information_schema]。[columns] WHERE table_name ='TargetTable'AND table_schema ='dbo'AND column_name ='TheNewColumn'）

BEGIN

ALTER TABLE [dbo]。[TargetTable]

ADD TheNewColumn int

END



- 移动新列的顺序位置到顶部。

UPDATE [information_schema]。[columns]

SET ORDINAL_POSITION = 1

WHERE table_name ='TargetTable'AND table_schema ='dbo'AND column_name ='TheNewColumn'



- 将旧列的序号位置移到底部。

UPDATE [information_schema ]。[columns]

SET ORDINAL_POSITION = 2

WHERE table_name ='TargetTable'AND table_schema ='dbo'AND column_name ='TheOldColumn'



一些SQL大师现在可能会尖叫，这会搞砸系统级别的东西。这就是我在这里发布的原因。

The Information_Schema.Columns table has an ORDINAL_POSITION field. Is it possible to simply add the desired columns via ALTER TABLE then change the ORDINAL_POSITION for the table in Information_Schema.Columns?

I haven't tried this, but it seems the following approach may work. This example assumes there is a table with 2 columns. Basically add the new column, assign it to position 1, then assign the old column to position 2.

-- Add the column
IF NOT EXISTS (SELECT * FROM [information_schema].[columns] WHERE table_name = 'TargetTable' AND table_schema = 'dbo' AND column_name = 'TheNewColumn')
BEGIN
ALTER TABLE [dbo].[TargetTable]
ADD TheNewColumn int
END

-- Move the new column's ordinal position to the top.
UPDATE [information_schema].[columns]
SET ORDINAL_POSITION = 1
WHERE table_name = 'TargetTable' AND table_schema = 'dbo' AND column_name = 'TheNewColumn'

-- Move the old column's ordinal position to the bottom.
UPDATE [information_schema].[columns]
SET ORDINAL_POSITION = 2
WHERE table_name = 'TargetTable' AND table_schema = 'dbo' AND column_name = 'TheOldColumn'

Some SQL guru is probably screaming right now that this would screw up something at a system level. That's why I'm posting it here.

Altrough MSDN [ ^ ]说，这不能从T-SQL完成，而是从SQL Management Studio完成。顺便说一下，那个选项没错？

Altrough MSDN[^] says, that this can't be done from T-SQL, but from SQL Management Studio. By the way, wha't wrong with that option?