SQL Select Top 100 % forgets Order By Clause

Introduction

Sometimes, a developer can't trust his eyes, when things changes without warning. They just react in another way and no one knows why.

What are we talking about?

Using an Order By in a Select Query (in a view, inline function or something similiar) is a beginner level phrase. And it's nothing that challenges ... But in some cases, SQL Server just ignores the Order By Clause. Let's look at a common sql statement:

Select * from Customer Order By CompanyName, City

<p>Now, this works normal. In some cases we add a <code>Top 100%

Select Top 100% from Customer Order By CompanyName, City

<p>If we run this more or less same query, we see, that the rows now come unsorted... </p>

Solution

After a few hours of research, we found in the SQL Server Books Online the following chapter:

When ORDER BY is used in the definition of a view, inline function, derived table, or subquery, the clause is used only to determine the rows returned by the TOP clause. The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself.

But for any reason, it does work with numeric Top

Select Top 10 from Customer Order By CompanyName, City

This works ... so our solution... we take the highest possible value for the Top Clause (what means a max(int) = 2147483647 and it works...

Select Top 2147483647 from Customer Order By CompanyName, City

will give us what we expect. Now you just have to change all your Top 100% and you are on the safe side for next SQL Server Releases from 2005 upwards.