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.

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

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