ORDER BY in a Sql Server 2008 view

Question

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.)

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

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.

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.

Solution 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.

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

Solution 2

SQL Server 2005 ignores TOP 100 PERCENT by design.

Try TOP 2000000000 instead.

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

Found some...

Kimberly L. Tripp

"TOP 100 Percent ORDER BY Considered Harmful"

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

Solution 3

Just use :

"Top (99) Percent "

or

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

Solution 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.

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.

Solution 5

Microsoft has fixed this. You have patch your sql server

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

View more solutions