当您执行“SELECT *"时，SQL Server 如何确定列的顺序? [英] How does SQL Server determine the order of the columns when you do a `SELECT *`?

问题描述

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

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

我知道Order By"对于数据的排序至关重要，但我希望列名保持一致.

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

注意:我的代码不取决于返回列的实际顺序.我只想知道 SQL Server 如何决定对列名进行排序.

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.

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

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.

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

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

我的问题原来是其中一台计算机被配置为以sa"而不是预期用户身份登录.当我们打算查询视图时，查询直接命中表.感谢您帮助了解 sys.columns

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

推荐答案

它们在系统视图sys.columns中按照column_id的顺序排列.

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

您可以通过以下方式查看:

You can check it by:

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

编辑

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

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:

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

这篇关于当您执行“SELECT *"时，SQL Server 如何确定列的顺序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！