Default Sort column....

My query looks like this......
SELECT COL1,COL2,COL3,COL4 FROM TABL ORDER BY COL2 ASC
How the rest of the data will be displayed? How exactly SQL Server
determines the sorting order columns for rest of the columns?
I am using SQL2K.
Thanks,
Smith
The results will be ordered by COL2 only. If the values are not unique, the
ordering of the remaining data is undefined. You need to specify additional
columns in your ORDER BY clause if you want other data returned in a
particular sequence.
Hope this helps.
Dan Guzman
SQL Server MVP
"Smith John" <JohnSmith56@.hotmail.com> wrote in message
news:OIPh$Mb2EHA.1452@.TK2MSFTNGP11.phx.gbl...
> My query looks like this......
> SELECT COL1,COL2,COL3,COL4 FROM TABL ORDER BY COL2 ASC
> How the rest of the data will be displayed? How exactly SQL Server
> determines the sorting order columns for rest of the columns?
> I am using SQL2K.
> Thanks,
> Smith
>
|||Dan,
Thanks for the reply.
1. Is it documented anywhere that remaining sort order is undefined?
2. Is it consistent while displaying the data when the order is undefined?
Thanks,
Smith
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OMaxZUb2EHA.4028@.TK2MSFTNGP15.phx.gbl...
> The results will be ordered by COL2 only. If the values are not unique,
the
> ordering of the remaining data is undefined. You need to specify
additional
> columns in your ORDER BY clause if you want other data returned in a
> particular sequence.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Smith John" <JohnSmith56@.hotmail.com> wrote in message
> news:OIPh$Mb2EHA.1452@.TK2MSFTNGP11.phx.gbl...
>
|||> 1. Is it documented anywhere that remaining sort order is undefined?
Not as far as I know. As a rule, undefined behavior is seldom documented.
It is risky to rely on undefined/undocumented behavior because this can
change without notice between versions or service packs and break your code.

> 2. Is it consistent while displaying the data when the order is undefined?
No. Once your ordering criteria is satisfied, the order of the remaining
data depends on the details of the query plan. This may vary depending on
the indexes used, number of processors, concurrent scans, etc. SQL Server
certainly doesn't add the unnecessary overhead of sequencing data without an
explicit ORDER BY unless it is needed for internal query optimization
Hope this helps.
Dan Guzman
SQL Server MVP
"Smith John" <JohnSmith56@.hotmail.com> wrote in message
news:Om%23p8Xb2EHA.2016@.TK2MSFTNGP15.phx.gbl...
> Dan,
> Thanks for the reply.
> 1. Is it documented anywhere that remaining sort order is undefined?
> 2. Is it consistent while displaying the data when the order is undefined?
> Thanks,
> Smith
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OMaxZUb2EHA.4028@.TK2MSFTNGP15.phx.gbl...
> the
> additional
>
|||"Smith John" <JohnSmith56@.hotmail.com> wrote in message
news:Om%23p8Xb2EHA.2016@.TK2MSFTNGP15.phx.gbl...
> Dan,
> Thanks for the reply.
> 1. Is it documented anywhere that remaining sort order is undefined?
Yes. In theory: Sets have no order
In SQL 2000 selects return no specific order unless you use an Order BY.
Part of this is because it may be faster for the DB to return it in an order
different from what you want. (for example SOMETIMES you can expect it to
return in the order of the clustered index, but that's simply because it can
read it off the disk faster that way.)

> 2. Is it consistent while displaying the data when the order is undefined?
No. Or rather, it's not guaranteed to be. Now, in my experience, usually
it is, but don't count on it.

> Thanks,
> Smith
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OMaxZUb2EHA.4028@.TK2MSFTNGP15.phx.gbl...
> the
> additional
>
|||With an SQL query, you specify the resultset. It is up to the RDBMS to
generate the correct result, and it can obtain this result any which way
it likes, just as long as the result is correct.
Everything you don't specify (such as the ordering of two rows with the
same value in column COL2) is by definition unspecified. This is a
property of SQL.
What SQL-Server will do is create a query plan that ensures the ordering
on COL2. The order for duplicates COL2-rows will just be in whatever
order the rows happen to be at that point. Since (in most cases) there
are many ways to achieve the same result, you cannot rely on any
particular order that you did not specify.
Gert-Jan
Smith John wrote:[vbcol=seagreen]
> Dan,
> Thanks for the reply.
> 1. Is it documented anywhere that remaining sort order is undefined?
> 2. Is it consistent while displaying the data when the order is undefined?
> Thanks,
> Smith
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OMaxZUb2EHA.4028@.TK2MSFTNGP15.phx.gbl...
> the
> additional