当您执行`SELECT *`时，SQL Server如何确定列的顺序？

How does SQL Server determine the order of the columns when you do a SELECT *?

当您执行SELECT *时，SQL Server如何确定列的顺序？

I know "Order By" is essential for ordering the data, but I expected the column names to be consistent.

我知道“Order By”对于排序数据至关重要，但我希望列名一致。

Note: My code is not dependent on the actual order the columns are returned. I just want to know how SQL Server decides to order the column names.

注意：我的代码不依赖于返回列的实际顺序。我只是想知道SQL Server如何决定对列名进行排序。

Of about 20 computers my team is using, one of them behaves differently. Any difference deserves to be investigated. The column name ordering appears to be the same for all computers when we open SQL Server Management Studio. When our application makes a query is when I see the difference.

在我的团队使用的大约20台计算机中，其中一台的行为不同。任何差异都值得研究。打开SQL Server Management Studio时，列名称排序对于所有计算机都是相同的。当我们的应用程序进行查询时，我看到了差异。

I am using SQL Server 2008, and SQL Server 2008 R2. My application uses C# System.Data.SqlClient to access the database.

我使用的是SQL Server 2008和SQL Server 2008 R2。我的应用程序使用C＃System.Data.SqlClient来访问数据库。

EDIT: My problem turned out to be that one of the computers was configured to log in as 'sa', instead of the intended user. The query was hitting the table directly when we intended it to hit a view. Thanks for the help learning about sys.columns

编辑：我的问题是，其中一台计算机配置为以“sa”身份登录，而不是目标用户。当我们打算查看视图时，查询直接命中了表。感谢有关sys.columns的帮助

2 个解决方案

#1

They are in the order of column_id from the system view sys.columns.

它们是系统视图sys.columns中column_id的顺序。

You can check it by:

你可以通过以下方式检查：

SELECT column_id, name
FROM sys.columns
WHERE object_id = Object_id('MyTableName')
ORDER BY column_id

EDIT

编辑

This is for Dems. You should test on a larger table, but it looks like it uses the order defined in the table, not the index:

这是给Dems的。您应该在更大的表上进行测试，但看起来它使用的是表中定义的顺序，而不是索引：

CREATE TABLE #T (cola int, colb int, colc int)

INSERT INTO #T
VALUES
(1,2,3),
(2,3,4),
(4,5,6)

SELECT * FROM #T

CREATE INDEX ix_test ON #T (colb, colc, cola)

SELECT * FROM #t
WHERE colb > 0

DROP TABLE #T

#2

They will be the same order in which they appear in Sql Server Management Studio; essentially, the order in which they were created.

它们将与它们在Sql Server Management Studio中出现的顺序相同;基本上，它们的创建顺序。

The "correct" solution for assuring a desired column order is to specify the fields explicitly in your SELECT statement. SELECT * makes no guarantees about the ordering of output columns.

确保所需列顺序的“正确”解决方案是在SELECT语句中明确指定字段。 SELECT *不保证输出列的排序。

#1