MS SQL Server - Sort order is good in design mode, but bad in open query

Author

Sort order is good in design mode, but bad in open query

Chad Kuester

2006-01-02, 3:23 am

I've got a query problem that looks like it might be a 2005 bug (it worked in
2000).

I'm using SQL Server Management Studio Express (I've tried the non-express
version too).

When I right-click design the query, then click Execute, it sorts correctly.
When I close the query, right-click "Open View" it's sorts wrong!

No matter how I specify the sort modifiers, the Open View will only sort
based on the order of primary key. It ignores all sort fields - even if I
specify a sort on every column.

As soon as I add a custom expression, the sort order goes whacky. My query
consists of 2 output columns. One is the CategoryName (which is sorted ASC)
and the CategoryBalance (which is unsorted). Here's the query:

SELECT C_CategoryName, C_OpeningBalance + COALESCE
((SELECT SUM(F_AccountAmount)
AS Expr1
FROM dbo.FundTransactions
WHERE (F_CategoryRec =
dbo.Categories.CategoryRec)), 0) AS Balance, CategoryRec
FROM dbo.Categories
ORDER BY C_CategoryName

The 2nd column is a sum, then COALESCE basically converts nulls into 0's,
then the opening balance is added to the sum.

I noticed the sort problem after upgrading from MSDE 2000 to SQL 2005 Express.

Why not try adding the two columns and doing an INNER JOIN instead? Tried
that too. Here was another query that has the same problem.

SELECT dbo.Categories.C_CategoryName
dbo.Categories.C_OpeningBalance + COALESCE
(SUM(dbo.FundTransactions.F_CategoryAmount), 0) AS Balance
FROM dbo.FundTransactions INNER JOIN
dbo.Categories ON dbo.FundTransactions.F_CategoryRec =
dbo.Categories.CategoryRec
GROUP BY dbo.Categories.C_CategoryName, dbo.Categories.C_OpeningBalance
ORDER BY dbo.Categories.C_CategoryName

The worst of this problem is that it reveals itself while in the Management
Studio. At first I thought this was an Access form sort messing me up, but
then I noticed that if I stay within the Management Studio and do a
design/execute it sorts correctly. If I close the query and do an "Open
View", it sorts wrong.

As soon as I delete the column with the '+' equation in it, it starts to
sort correctly everywhere. Anyone else having problems with SQL 2005 and
sorting with custom expressions added to the output?

Management Studio Express 9.00.1399.00
SQL Server Express 2005 9.00.1399.06

Razvan Socol

2006-01-02, 3:23 am

Hello, Chad

It's not a bug, it's a legitimate optimization performed by SQL Server
2005 (but not by SQL Server 2000). By definition, views (as well as
tables) represent unordered sets of rows. The only way an ORDER BY
clause could be specified in a view (and also in a subquery), is to use
a TOP clause, for example "TOP 100 PERCENT".

Consider the following view (in AdventureWorks):

CREATE VIEW OldestEmployees
SELECT TOP 10 *
FROM HumanResources.Employee
ORDER BY BirthDate

GO

SELECT * FROM OldestEmployees

It's the same thing as:

SELECT * FROM (
SELECT TOP 10 *
FROM HumanResources.Employee
ORDER BY BirthDate
) OldestEmployees

In this case, we are saying that we want the oldest 10 employees. The
selection of which employees are displayed is based on "ORDER BY
BirthDate", but we did not specify that the rows need to be returned in
any particular order. Incidentally, they are returned in the same
order, but if we wanted to be sure that they are returned in order, we
need to use "ORDER BY" in the outermost SELECT query:

SELECT * FROM OldestEmployees ORDER BY BirthDate

which is equivalent to:

SELECT * FROM (
SELECT TOP 10 *
FROM HumanResources.Employee
ORDER BY BirthDate
) OldestEmployees
ORDER BY BirthDate

If we use "TOP 100 PERCENT" instead of "TOP 10" in the view (and we
don't specify the ORDER BY clause in the outermost SELECT query), we
say that we need all the rows, so SQL Server 2005 realizes that the
ordering would be useless (since we want all the rows, anyway, and we
did not specify an ordering of rows in the outermost SELECT query), so
it ignores the ORDER BY clause in the view. SQL Server 2000 did not
make this optimization, and returned the rows in the order specified in
the view (but this was not a guaranteed behaviour, it just "happen" to
be that way).

Of course, I think you realize that using a "SELECT * FROM SomeTable"
does not guarantee any order for the returned rows (it may happen to be
in the order of the clustered index of the table, which is usually the
primary key, but this is just incidentally). If we want a particular
order we must always specify the ORDER BY clause in the (outermost)
SELECT query.

Regarding SQL Server Management Studio, the "Execute" button in Query
Designer executes the definition of the view, for example:

SELECT TOP 100 PERCENT *
FROM HumanResources.Employee
ORDER BY BirthDate

The "Open View" command executes a query like this:

SELECT * FROM OldestEmployees

You can see that the query executed by the "Execute" button has an
ORDER BY clause in the outermost SELECT, therefore an order is
specified and must be respected. The query executed by the "Open View"
command has no ORDER BY clause (in the outermost SELECT), so the order
of the returned rows is not specified.

Razvan

Sponsored Links