Is explicit ordering from subquery propagated upwards?

10 Aug 2006 8:36 AM

sugnaboris@gmail.com

I've tried this with an example - but of course an example isn't
definitive indication that the behaviour is required by standard
(SQL-92) or is guaranteed by implementation (SQL Server 2000).

Hope someone can help by pointing me in the right direction.

The problem is that I'd like a stored procedure to return an ordered
results set, but that I don't want it to return the column(s) on which
the ordering is based.

I tried the following, on Northwind:

-- example begin

SELECT
    a.CustomerID,
    a.ContactName
FROM
    (SELECT TOP 10
        CustomerID, ContactName, SUBSTRING(ContactName, 3, 1) AS
orderer
FROM Customers
ORDER BY orderer, ContactName) AS a

-- subquery on its own, for comparison

SELECT TOP 10 CustomerID, ContactName, SUBSTRING(ContactName, 3, 1) AS
orderer
FROM Customers
ORDER BY orderer, ContactName

-- example end

Where I've added an explicit ordering calculated column ("orderer") in
the subquery, then ordered by it and another column. (I used the
compound ordering just to impose an explicit ordering.)

When I run the top query, ordering is apparently preserved: but is this
guaranteed? If it is guaranteed, what guarantees it?

10 Aug 2006 8:43 AM

Chris Lim

sugnabo***@gmail.com wrote:

> Where I've added an explicit ordering calculated column ("orderer") in
> the subquery, then ordered by it and another column. (I used the
> compound ordering just to impose an explicit ordering.)
>
> When I run the top query, ordering is apparently preserved: but is this
> guaranteed? If it is guaranteed, what guarantees it?

I would not expect it to be ordered the same way every time, given the
rule that a table is a set of unordered rows (even though it may be
physically ordered based on the file system or an index in a particular
way). Maybe Kalen or Joe could confirm?

10 Aug 2006 8:47 AM

sugnaboris@gmail.com

Chris Lim wrote:

> sugnabo***@gmail.com wrote:
> > Where I've added an explicit ordering calculated column ("orderer") in
> > the subquery, then ordered by it and another column. (I used the
> > compound ordering just to impose an explicit ordering.)
> >
> > When I run the top query, ordering is apparently preserved: but is this
> > guaranteed? If it is guaranteed, what guarantees it?
>
> I would not expect it to be ordered the same way every time, given the
> rule that a table is a set of unordered rows (even though it may be
> physically ordered based on the file system or an index in a particular
> way). Maybe Kalen or Joe could confirm?

That was my concern: without any explicit ordering, the result set
would be unordered (since the underlying table is by definition
unordered). I just wanted to be sure that there is no propagation of
ordering ("once you've ordered the rows, they stay ordered").

10 Aug 2006 9:02 AM

Chris Lim

sugnabo***@gmail.com wrote:

> unordered). I just wanted to be sure that there is no propagation of
> ordering ("once you've ordered the rows, they stay ordered").

I would imagine the optimizer would treat the ordered subquery in the
same way as a table with a clustered index, or one that is physically
ordered in a certain way. That is, most of the time it will probably
retain the ordering, but you'd be a brave developer to rely on it!

Sorry I can't give you a definitive answer, but I'm sure someone else
will.

10 Aug 2006 8:52 AM

Uri Dimant

Hi
In order to return an ORDERED result, it means to be sure for 100 percent
you'll have to specify ORDER BY clause

Read these articles written by Itzik Ben-Gan
http://www.sqlmag.com/Departments/DepartmentID/1016/1016.html

<sugnabo***@gmail.com> wrote in message
Show quote

news:1155198989.594968.209610@q16g2000cwq.googlegroups.com...
> I've tried this with an example - but of course an example isn't
> definitive indication that the behaviour is required by standard
> (SQL-92) or is guaranteed by implementation (SQL Server 2000).
>
> Hope someone can help by pointing me in the right direction.
>
> The problem is that I'd like a stored procedure to return an ordered
> results set, but that I don't want it to return the column(s) on which
> the ordering is based.
>
> I tried the following, on Northwind:
>
> -- example begin
>
> SELECT
>    a.CustomerID,
>    a.ContactName
> FROM
>    (SELECT TOP 10
>        CustomerID, ContactName, SUBSTRING(ContactName, 3, 1) AS
> orderer
> FROM Customers
> ORDER BY orderer, ContactName) AS a
>
> -- subquery on its own, for comparison
>
> SELECT TOP 10 CustomerID, ContactName, SUBSTRING(ContactName, 3, 1) AS
> orderer
> FROM Customers
> ORDER BY orderer, ContactName
>
> -- example end
>
> Where I've added an explicit ordering calculated column ("orderer") in
> the subquery, then ordered by it and another column. (I used the
> compound ordering just to impose an explicit ordering.)
>
> When I run the top query, ordering is apparently preserved: but is this
> guaranteed? If it is guaranteed, what guarantees it?
>

10 Aug 2006 10:09 AM

Tibor Karaszi

No guarantees. Optimizer tries to execute the query as cheaply as possible. It will not "scramble"
toe order in the output just to make a point. But if it find a cheaper way to run the query meaning
the (logically) intermediate result will be re-ordered, it will.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

<sugnabo***@gmail.com> wrote in message

Show quote

news:1155198989.594968.209610@q16g2000cwq.googlegroups.com...
> I've tried this with an example - but of course an example isn't
> definitive indication that the behaviour is required by standard
> (SQL-92) or is guaranteed by implementation (SQL Server 2000).
>
> Hope someone can help by pointing me in the right direction.
>
> The problem is that I'd like a stored procedure to return an ordered
> results set, but that I don't want it to return the column(s) on which
> the ordering is based.
>
> I tried the following, on Northwind:
>
> -- example begin
>
> SELECT
>    a.CustomerID,
>    a.ContactName
> FROM
>    (SELECT TOP 10
>        CustomerID, ContactName, SUBSTRING(ContactName, 3, 1) AS
> orderer
> FROM Customers
> ORDER BY orderer, ContactName) AS a
>
> -- subquery on its own, for comparison
>
> SELECT TOP 10 CustomerID, ContactName, SUBSTRING(ContactName, 3, 1) AS
> orderer
> FROM Customers
> ORDER BY orderer, ContactName
>
> -- example end
>
> Where I've added an explicit ordering calculated column ("orderer") in
> the subquery, then ordered by it and another column. (I used the
> compound ordering just to impose an explicit ordering.)
>
> When I run the top query, ordering is apparently preserved: but is this
> guaranteed? If it is guaranteed, what guarantees it?
>