Sort order in SQL 2005

On 27 Nov 2006 07:32:39 -0800, wrote:

>In a 2005 database table function that has SELECT TOP 100 Percent ....
>ORDER BY TextField
>This displays it in the primary key (numeric field) order and not the
>specified. Is there a setting change that is required to make the
>function return the order as specified?

The change is to add an ORDER BY to the SELECT that retrieves the rows
from the table function. The only way to ever be completely sure of
the order in which rows are returned has always been an explicit ORDER
BY.

Though SQL Server 2000 generally returned the rows in order when that
trick was used, it was never a documented behavior, just a trick. The
undocumented behavior changed with SQL Server 2005.

Roy Harvey
Beacon Falls, CT
 >> Stay informed about: Sort order in SQL 2005 

> In a 2005 database table function that has SELECT TOP 100 Percent ....
> ORDER BY TextField

Do you mean a VIEW?

> This displays it in the primary key (numeric field) order and not the
> specified.

Do you mean if you say SELECT * FROM VIEW and don't bother using an ORDER BY
statement?

> Is there a setting change that is required to make the
> function return the order as specified?

Yes, you say SELECT * FROM View ORDER BY <specified order>. Views and
tables are, by definition, unordered sets of rows, and the optimizer is free
to return the rows in any order it deems appropriate, unless you explicitly
tell it what order you want. You do that by appending an ORDER BY clause to
the query, not to the definition of the view.

Even in the kludge case where a view allows an order by (which it shouldn't,
IMHO) -- this determines which top n rows are returned, but not the order in
which those rows are returned. The problem is actually in the dependency
between TOP <-> ORDER BY and how the syntax works, which would make more
sense if the selection of the rows were tied more tightly with TOP. ORDER
BY should be completely independent of that construct (again IMHO) and
shouldn't serve two purposes.
 >> Stay informed about: Sort order in SQL 2005 

Looks and sounds to me like people can't read or listen.... So, did you ever find the solution to the vew not returning the sort order when you DO have an order by clause?

I found an article about an SP2 hotfix, of course I've already put on SP3, expecting the hotfix to be "in" SP3.. but no... back to the old NT4 scenario where you can't trust MS service packs again...
 >> Stay informed about: Sort order in SQL 2005