Sql Server 2008视图中的ORDER BY

we have a view in our database which has an ORDER BY in it. Now, I realize views generally don't order, because different people may use it for different things, and want it differently ordered. This view however is used for a VERY SPECIFIC use-case which demands a certain order. (It is team standings for a soccer league.)

我们的数据库中有一个ORDER BY的视图。现在，我意识到观点通常不会排序，因为不同的人可能会将它用于不同的事物，并希望它有所不同。然而，该视图用于需要特定订单的非常特定的用例。（这是足球联赛的球队排名。）

The database is Sql Server 2008 Express, v.10.0.1763.0 on a Windows Server 2003 R2 box.

该数据库是Windows Server 2003 R2上的Sql Server 2008 Express，v.10.0.1763.0。

The view is defined as such:

视图定义如下：

CREATE VIEW season.CurrentStandingsOrdered
AS
    SELECT TOP 100 PERCENT *, season.GetRanking(TEAMID) RANKING   
    FROM season.CurrentStandings 
    ORDER BY 
        GENDER, TEAMYEAR, CODE, POINTS DESC, 
        FORFEITS, GOALS_AGAINST, GOALS_FOR DESC, 
        DIFFERENTIAL, RANKING

It returns:

它返回：

GENDER, TEAMYEAR, CODE, TEAMID, CLUB, NAME,  
WINS, LOSSES, TIES, GOALS_FOR, GOALS_AGAINST,  
DIFFERENTIAL, POINTS, FORFEITS, RANKING

Now, when I run a SELECT against the view, it orders the results by GENDER, TEAMYEAR, CODE, TEAMID. Notice that it is ordering by TEAMID instead of POINTS as the order by clause specifies.

现在，当我对视图运行SELECT时，它按GENDER，TEAMYEAR，CODE，TEAMID命令结果。请注意，它是由TEAMID而不是POINTS排序，因为order by子句指定。

However, if I copy the SQL statement and run it exactly as is in a new query window, it orders correctly as specified by the ORDER BY clause.

但是，如果我复制SQL语句并完全按照新查询窗口中的方式运行它，它将按照ORDER BY子句的指定正确排序。

7 个解决方案

#1

The order of rows returned by a view with an ORDER BY clause is never guaranteed. If you need a specific row order, you must specify where you select from the view.

从不保证具有ORDER BY子句的视图返回的行的顺序。如果需要特定的行顺序，则必须指定从视图中选择的位置。

See this the note at the top of this Book On-Line entry.

请参阅本书在线条目顶部的注释。

#2

SQL Server 2005 ignores TOP 100 PERCENT by design.

SQL Server 2005在设计上忽略了TOP 100 PERCENT。

Try TOP 2000000000 instead.

请尝试使用TOP 2000000000。

Now, I'll try and find a reference... I was at a seminar presented by Itzak Ben-Gan who mentioned it

现在，我会尝试找一个参考......我参加了由Itzak Ben-Gan提出的研讨会

Found some...

发现一些......

Kimberly L. Tripp

"TOP 100 Percent ORDER BY Considered Harmful"

“前100％的秩序被认为是有害的”

In this particular case, the optimizer recognizes that TOP 100 PERCENT qualifies all rows and does not need to be computed at all.

在这种特殊情况下，优化器识别出TOP 100 PERCENT限定所有行，并且根本不需要计算。

#3

Just use :

只需使用：

"Top (99) Percent "

“最高（99）百分比”

要么

"Top (a number 1000s times more than your data rows like 24682468123)" it works! just try it.

“Top（比你的数据行多了1000多倍，比如24682468123）”它有效！去尝试一下。

#4

In SQL server 2008, ORDER BY is ignored in views that use TOP 100 PERCENT. In prior versions of SQL server, ORDER BY was only allowed if TOP 100 PERCENT was used, but a perfect order was never guaranteed. However, many assumed a perfect order was guaranteed. I infer that Microsoft does not want to mislead programmers and DBAs into believing there is a guaranteed order using this technique.

在SQL Server 2008中，在使用TOP 100 PERCENT的视图中忽略ORDER BY。在SQL Server的早期版本中，只有在使用TOP 100 PERCENT时才允许ORDER BY，但从未保证完美的订单。然而，许多人认为完美的订单是有保证的。我推断微软不想误导程序员和DBA相信使用这种技术有保证的订单。

An excellent comparative demonstration of this inaccuracy, can be found here...

这里可以找到这种不准确性的优秀对比示例......

http://blog.sqlauthority.com/2009/11/24/sql-server-interesting-observation-top-100-percent-and-order-by

Oops, I just noticed that this was already answered. But checking out the comparative demonstration is worth a look anyway.

哎呀，我刚才注意到这已经回答了。但无论如何，查看比较示范值得一看。

#5

Microsoft has fixed this. You have patch your sql server

微软解决了这个问题。你有补丁你的SQL服务器

http://support.microsoft.com/kb/926292

#6

run a profiler trace on your database and see the query that's actually being run when you query your view.

在数据库上运行探查器跟踪，并在查询视图时查看实际运行的查询。

You also might want to consider using a stored procedure to return the data from your view, ordered correctly for your specific use case.

您还可以考虑使用存储过程从视图中返回数据，并针对您的特定用例正确排序。

#7

I found an alternative solution.

我找到了另一种解决方案。

My initial plan was to create a 'sort_order' column that would prevent users from having to perform a complex sort.

我最初的计划是创建一个“sort_order”列，以防止用户执行复杂的排序。

I used a windowed function ROW_NUMBER. In the ORDER BY clause, I specified the default sort order that I needed (just as it would have been in the ORDER BY of a SELECT statement).

我使用了一个窗口函数ROW_NUMBER。在ORDER BY子句中，我指定了我需要的默认排序顺序（就像它在SELECT语句的ORDER BY中一样）。

I get several positive outcomes:

我得到了几个积极的结果：

By default, the data is getting returned in the default sort order I originally intended (this is probably due to the windowed function having to sort the data prior to assigning the sort_order value)

默认情况下，数据以我最初预期的默认排序顺序返回（这可能是由于窗口函数必须在分配sort_order值之前对数据进行排序）
Other users can sort the data in alternative ways if they choose to

其他用户可以选择以其他方式对数据进行排序
The sort_order column is there for a very specific sort need, making it easier for users to sort the data should whatever tool they use rearranges the rowset.

sort_order列用于非常特定的排序需求，使用户可以更轻松地对数据进行排序，无论他们使用何种工具重新排列行集。

Note: In my specific application, users are accessing the view via Excel 2010, and by default the data is presented to the user as I had hoped without further sorting needed.

注意：在我的特定应用程序中，用户通过Excel 2010访问视图，默认情况下，数据按照我的希望呈现给用户，无需进一步排序。

Hope this helps those with a similar problem.

希望这可以帮助那些有类似问题的人。

Cheers, Ryan

干杯，瑞恩

#1