order without ordering 
Author Message
 order without ordering

According to BOL:

An explicit ORDER BY clause for a SELECT statement is
required to ensure any useful ordering of data. In
addition, the exact results depend upon the collation
being used.


is that if a table was populated with and Order clause, it
wouldnt need to be ordered on the select. Basically, order
on the way in instead of on the way out. My point is what
BOL and everything else Ive ever read about this says,
that you must order clause it in the select. However, Im
having a hard time coming up with documentation backing up
my case. Does anyone know of articles or white papers on
this sort of thing?

Thanks in advance.
Chris

P.S. Im aware that tables arent ordered and that magnetic
tape files are.
Thanks.



Wed, 21 Sep 2005 05:57:02 GMT
 order without ordering

You need an ORDER BY if you want to make sure that the data returns in an ordered fashion, regardless of how the data is loaded into the table.  

--
Keith, SQL Server MVP

Quote:

> According to BOL:

> An explicit ORDER BY clause for a SELECT statement is
> required to ensure any useful ordering of data. In
> addition, the exact results depend upon the collation
> being used.


> is that if a table was populated with and Order clause, it
> wouldnt need to be ordered on the select. Basically, order
> on the way in instead of on the way out. My point is what
> BOL and everything else Ive ever read about this says,
> that you must order clause it in the select. However, Im
> having a hard time coming up with documentation backing up
> my case. Does anyone know of articles or white papers on
> this sort of thing?

> Thanks in advance.
> Chris

> P.S. Im aware that tables arent ordered and that magnetic
> tape files are.
> Thanks.



Wed, 21 Sep 2005 06:12:00 GMT
 order without ordering
CREATE TABLE junk (colx INTEGER PRIMARY KEY)

INSERT INTO junk VALUES (3)
INSERT INTO junk VALUES (2)
INSERT INTO junk VALUES (1)

SELECT * FROM junk

Are the values returned in the order they were inserted? ;-)

--
David Portas
------------
Please reply only to the newsgroup
--

Quote:
> According to BOL:

> An explicit ORDER BY clause for a SELECT statement is
> required to ensure any useful ordering of data. In
> addition, the exact results depend upon the collation
> being used.


> is that if a table was populated with and Order clause, it
> wouldnt need to be ordered on the select. Basically, order
> on the way in instead of on the way out. My point is what
> BOL and everything else Ive ever read about this says,
> that you must order clause it in the select. However, Im
> having a hard time coming up with documentation backing up
> my case. Does anyone know of articles or white papers on
> this sort of thing?

> Thanks in advance.
> Chris

> P.S. Im aware that tables arent ordered and that magnetic
> tape files are.
> Thanks.



Wed, 21 Sep 2005 06:29:32 GMT
 order without ordering
How data is actually stored on the physical data pages is not directly
related to how it is entered, and the order in which data is returned is not
dependent on the order in which it is stored. If you wanted data returned in
a particular order, you MUST use an ORDER BY.

I have written numerous articles for SQL Server Magazine on physical data
storage, plus my book "Inside SQL Server 2000" explains the algorithms and
mechanisms in great detail. Plus, the book gives  you the tools for
inspecting the physical data pages yourself, so you can see exactly how the
data ends up being stored on the pages, and you can compare it to the output
order when you don't use ORDER BY.

HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com


Quote:
> According to BOL:

> An explicit ORDER BY clause for a SELECT statement is
> required to ensure any useful ordering of data. In
> addition, the exact results depend upon the collation
> being used.


> is that if a table was populated with and Order clause, it
> wouldnt need to be ordered on the select. Basically, order
> on the way in instead of on the way out. My point is what
> BOL and everything else Ive ever read about this says,
> that you must order clause it in the select. However, Im
> having a hard time coming up with documentation backing up
> my case. Does anyone know of articles or white papers on
> this sort of thing?

> Thanks in advance.
> Chris

> P.S. Im aware that tables arent ordered and that magnetic
> tape files are.
> Thanks.



Wed, 21 Sep 2005 06:53:49 GMT
 order without ordering

I am on the side of needing an order by. However Im trying to find
documentation to back it up.

Any thoughts?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Wed, 21 Sep 2005 07:10:30 GMT
 order without ordering
Thats my point of the discussion Im having. What Im referring too though
is:

create table t1
(
c1 char(10)
)
insert into t1(c1)
values(select column from some other table
order by that column)

Sorry for not posting ddl before. I am on the side of needing an order
by. However Im trying to find documentation to back it up.

Thanks.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Wed, 21 Sep 2005 07:10:30 GMT
 order without ordering
CREATE TABLE junk (colx INTEGER PRIMARY KEY)
CREATE TABLE foo (colx INTEGER PRIMARY KEY)

INSERT INTO foo VALUES (1)
INSERT INTO foo VALUES (2)
INSERT INTO foo VALUES (3)

INSERT INTO junk
 SELECT * FROM foo
  ORDER BY colx DESC -- Don't do this!

SELECT * FROM junk

What more proof do you need?

Never use ORDER BY with INSERT... SELECT. It slows the query down and
achieves nothing.

--
David Portas
------------
Please reply only to the newsgroup
--

Quote:
> Thats my point of the discussion Im having. What Im referring too though
> is:

> create table t1
> (
> c1 char(10)
> )
> insert into t1(c1)
> values(select column from some other table
> order by that column)

> Sorry for not posting ddl before. I am on the side of needing an order
> by. However Im trying to find documentation to back it up.

> Thanks.

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!



Wed, 21 Sep 2005 07:25:17 GMT
 order without ordering
How about the 2nd sentence in the paragraph below the one you posted?

Here is the paragraph.

Quote:
>Expect different results as compared to earlier versions of SQL Server.
>Add an explicit ORDER BY clause to
>all SELECT statements needing to produce ordered rows.

Surely, you have already seen that paragraph.
I guess you need more ammunition to kill the misinformation.

If you use more data,
and run Keith's example SELECT query multiple overlapping times
on the Enterprise Edition of SQL Server,
you can demonstrated that the rows are not even necessarily
produced in the primary key order either!
(Recall, there is no ORDER BY)

Even if you have no indexing at all
and regardless of if you populate the table by
 - inserting the rows one row at a time, or
 - inserting all the rows at once (even if an ORDER BY is used)
a SELECT statment without an ORDER BY will not
produce the rows in any particular order long less
the order in which the rows where inserted.

Furthermore, if you visit the thread:

From: Delbert Glass
Subject: Does bookmark order imply clustered order?
Newsgroup: microsoft.public.sqlserver.programming
Date: 2002-07-15 20:08:52 PST

you will find that even when row requests are made in a particular order
that goes not guaruantee the rows will be produced in a particular order.

-----

If one includes an ORDER BY clause,
the planer will create a plan that honors that request.
Futhermore the planer is smart, it does not just throw in
a final sorting step just because an ORDER BY clause is present.
In other words, adding a ORDER BY clause to a query does not
necessarily make the query more expensive (as it would on dumb system).
However, leaving it out does cost one the risk (and that risk is very real)
of not producing order rows in whatever order one is unwisely expecting.

------

Say you give me some things, and then ask for them back.

I might put them in a queue,
and give them back to you in first in first out order.

I might put them in a stack,
and give them back to you in last in first out order.

I might put them in a min heap and
and give them back to you
- in min first order, or
- in depth first order, or
- in breath first order, or even
- in max first order.

I might put them in a max heap and
and give them back to you
- in min first order, or
- in depth first order, or
- in breath first order, or even
- in max first order.

I might put them in a binary (etc) tree.
I might put them in a balance tree.
I might put them in a red/black tree.
etc.

I might even give them back to you
in depth first, breath first,
odds then evens, evens then odds,
primes then non-primes etc,
or even a random order.

Ok, you get the idea.
Now look back and ponder, did I even retain information
about what order you gave the things to me?
In the case of the stack and queue, that information exists.
But for some of the others that information has been lost.
Even if the information exists, that doesn't mean it will be used.

That's the situation SQL server is in.
The information is lost.
Even it does happen to still exist (say in the log file),
the information is not used.

(Gee, you didn't even tell me to destroy any of the things, yet.)

Bye,
Delbert Glass


Quote:
> According to BOL:

> An explicit ORDER BY clause for a SELECT statement is
> required to ensure any useful ordering of data. In
> addition, the exact results depend upon the collation
> being used.


> is that if a table was populated with and Order clause, it
> wouldnt need to be ordered on the select. Basically, order
> on the way in instead of on the way out. My point is what
> BOL and everything else Ive ever read about this says,
> that you must order clause it in the select. However, Im
> having a hard time coming up with documentation backing up
> my case. Does anyone know of articles or white papers on
> this sort of thing?

> Thanks in advance.
> Chris

> P.S. Im aware that tables arent ordered and that magnetic
> tape files are.
> Thanks.



Wed, 21 Sep 2005 11:48:29 GMT
 order without ordering
By definition, a table is an unordered set of rows.

If the query is:
SELECT OrderNumber FROM Orders

and the RDBMS returns
OrderNumber
-----------
1
2
3

then that result is just as correct as

OrderNumber
-----------
3
1
2

This is because you are requesting a particular answer, not how this
answer is calculated.

SQL-Server takes the easy way. Without an ORDER BY clause, it will
simply output the rows at the end of the query plan, in the order it
happens to be in. This may be the result of a table scan, an index
traversal, a loop, merge or hash join, etc. These are all likely to
return the rows in a different order. SQL-Server is at liberty to use
whatever it deems necessary, just as long as the correct rows are
eventually returned.

Hope this helps,
Gert-Jan

Quote:

> According to BOL:

> An explicit ORDER BY clause for a SELECT statement is
> required to ensure any useful ordering of data. In
> addition, the exact results depend upon the collation
> being used.


> is that if a table was populated with and Order clause, it
> wouldnt need to be ordered on the select. Basically, order
> on the way in instead of on the way out. My point is what
> BOL and everything else Ive ever read about this says,
> that you must order clause it in the select. However, Im
> having a hard time coming up with documentation backing up
> my case. Does anyone know of articles or white papers on
> this sort of thing?

> Thanks in advance.
> Chris

> P.S. Im aware that tables arent ordered and that magnetic
> tape files are.
> Thanks.



Wed, 21 Sep 2005 18:53:28 GMT
 order without ordering
hi,


Quote:
> By definition, a table is an unordered set of rows.

is that true for a table with a clutered index too or just for  a heap??

Andrea Montanari

http://www.asql.biz/DbaMgr.shtm
DbaMgr2k ver 0.3.1  -  DbaMgr ver 0.49.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--------- remove DMO to reply



Wed, 21 Sep 2005 22:35:17 GMT
 order without ordering
Andrea,

I suspect you might be confusing the underpinnings of relational databases and the SQL language with a vendor specific application of the same.

The concept of relational databases was created by Dr. E. F. Codd, who was (and still is) a mathematician. He discussed relational databases in terms of sets. Sets are usually unordered. Meaning, for example, that mathematically the set {1, 2, 3} is identical to the set {3, 2, 1}. The sequence in which the elements of a set are listed is unimportant.

The language SQL, while not completely conforming to the full set of mathematics outlined by Dr. Codd, does follow the general underpinnings relatively closely. So it is correct to state that the language SQL considers a table to be an unordered set of rows.

Confusion comes in because it was always a part of both the theory that developers using relational databases should not have to be aware of the physical structure of the database. As such, the SQL language, as defined by the ANSI SQL standard, tries very diligently to avoid anything that deals with the physical structure of a database ... and indexes fall into that category. As a fairly easy example, there is no ANSI SQL compliant statement that ever references an index. In other words, the result set returned by an ANSI SQL statement is independent of whether indexes exist on the tables referenced. Indexes exist for performance reasons only. While indexes might (significantly) improve the response time of a query, it should be true that the result set returned by a query is not going to differ depending upon whether indexes exists or not.

In short, the SQL language has no concept of an index.

So, Gert-Jan is correct in his statement that a table is by definition an unordered set of rows. Vendor implementations of relational databases obviously are subject to physical realities, and thus a SQL Server table is logically (not necessarily physically) sequenced by a clustered index if it has one. But the SQL query that you write against the table ... when it conforms to the ANSI SQL standard ... will never explicitly reference a clustered, or any other type of, index.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

Quote:

> hi,


> > By definition, a table is an unordered set of rows.

> is that true for a table with a clutered index too or just for  a heap??

> Andrea Montanari

> http://www.asql.biz/DbaMgr.shtm
> DbaMgr2k ver 0.3.1  -  DbaMgr ver 0.49.1
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> --------- remove DMO to reply



Wed, 21 Sep 2005 23:25:43 GMT
 order without ordering
If you wanted data returned in

Quote:
> a particular order, you MUST use an ORDER BY.

However, if you specify "ORDER BY", SQL Server will not necessarily
perform a sort, correct? If there is clustered index on the table and
the index key is in your where clause, then the sorting operation can
be avoided.

- Jusung Yang



Thu, 22 Sep 2005 02:28:49 GMT
 order without ordering
Andrea,

  Indexes have nothing at all to do with the correct behavior of a query,
where "behavior" means the result returned and the changes that occur
to the state of the database.  The correct behavior of a query does not
change if an index is added or removed from a table.  If it did, we would
have a terrible language design, since the language statement would be
incomplete in decribing what should happen.  (This happens to some
extent already, as in SELECT * FROM T, which some people don't
recommend, for returning data, and due to some configuration parameters,
such as datetime settings, which already cause a number of problems and
confusion.)

In fact, the ANSI/ISO standard document for SQL-92, which Microsoft
SQL Server is based on, does not discuss indexes or other implementation
details at all.

select ....
< no order by clause>

is behaving correctly if it returns the correct rows.  Correct behavior
is no different if an index is added or removed.

One thing that confuses the issue is that there is more than one "correct
behavior" for a select query without an ordering clause.  In my opinion,
there would be less confusion if there were only one correct behavior
for a query.  But this would either mean a mandatory order by clause
or a default ordering.  In practice, this would be an obstacle to
programmers.

If you know Access, you know another SQL construct that has no
single correct behavior - the aggregate functions FIRST() and LAST().
They can be convenient, but can also surprise when their behavior
suddenly changes.

Steve Kass
Drew University

Quote:

>hi,


>>By definition, a table is an unordered set of rows.

>is that true for a table with a clutered index too or just for  a heap??

>Andrea Montanari

>http://www.asql.biz/DbaMgr.shtm
>DbaMgr2k ver 0.3.1  -  DbaMgr ver 0.49.1
>(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
>interface)
>--------- remove DMO to reply



Thu, 22 Sep 2005 02:49:49 GMT
 order without ordering
Yes, if there is a clustered index that already has the data in the
requested order, SQL Server will just follow the index pointers without
performing a sort.

The index key in the where clause doesn't matter, it's the index key
determining the sort order (in the ORDER BY clause) that is important.

--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com


Quote:
> If you wanted data returned in
> > a particular order, you MUST use an ORDER BY.

> However, if you specify "ORDER BY", SQL Server will not necessarily
> perform a sort, correct? If there is clustered index on the table and
> the index key is in your where clause, then the sorting operation can
> be avoided.

> - Jusung Yang



Thu, 22 Sep 2005 03:46:15 GMT
 order without ordering
[posted and mailed, please reply in news]

Quote:


> is that if a table was populated with and Order clause, it
> wouldnt need to be ordered on the select. Basically, order
> on the way in instead of on the way out. My point is what
> BOL and everything else Ive ever read about this says,
> that you must order clause it in the select. However, Im
> having a hard time coming up with documentation backing up
> my case. Does anyone know of articles or white papers on
> this sort of thing?

Rather than referring to documentation, show them right in the eye.
Run this script for them against an SQL Server that has more than
one processor:

   SELECT TOP 80000 Number = IDENTITY(int, 1, 1)
      INTO   Numbers
      FROM   pubs..authors t1, pubs..authors t2,
             pubs..authors t3, pubs..authors t4

   create table disorder (num int NOT NULL, guid uniqueidentifier NOT NULL)
   go
   INSERT disorder (num, guid)
      select Number, newid()
      from  Numbers
      order by  Number desc
   go
   select * from disorder

I don't know if you really need a double CPU to demonstrate this. When
I ran the script on my home machine, which is a single-CPU workstation,
I seemed to get the numbers in order. When I ran it against our development
server at the office, I got the rows in disorder, even if I added
OPTION (MAXDOP 1), which turns off parallelism.

--

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp



Thu, 22 Sep 2005 07:10:08 GMT
 
 [ 19 post ]  Go to page: [1] [2]

 Relevant Pages 

1. select all data in order without order by clause

2. Ordering select results without ORDER BY

3. data in sorted order in table without order by clause

4. Sort Order in Query without Order By

5. ORDER BY without SELECT clause (order by function)

6. Custom Rollup Formula / Pass Order / Solve Order

7. SELECT *, Is order of rows maintained if no sort order is specified

8. Converting a database from dictionary case insensitive sort order to binary sort order

9. Select order versus insert order

10. Multiple Tables DTS Order- How do I make them run in Specified order (not Alphbet)

11. Order By Question - Non alphabetical order

12. How to sort order in SELECT by ascending/descending order for MS SQL


 
Powered by phpBB® Forum Software