SQL Server - Simple Select Statement, Hot topic

Asked By huhoi on 03-Apr-07 07:09 PM
Ok this might or might not be a hot topic among different levels of
sql users.

If I have a select statement simple or complicated, but let's stick to
simple just to start off:

Select * from Users
Select * from Cars where Make = "Ferrari"

no "sort by" attribute is added to the queries.

something like that as an example.

Will they yield the exact same result each time, in term of the order
row by row returned?  My friend says yes, but I was told that they do
not yield the same results.  Maybe it does if the table does not
change, but how about if 1 or more rows are added or removed from this
table?  Will the results be the same row by row and the new rows are
just added on to the end?  What is the sort order done internally to
bring this result back?

GilbertoZampatt replied on 04-Apr-07 03:40 AM
The answer you expect depends on the design of the tables: If the table is a
heap (without clustered index) and no non-clustered indexes are present,
there's no way to be sure that the result will be always in the same order,
because of the way SQL gets the result: it should be a table scan, and this
means that SQL gets the pages belonging to the table.
Again, if the table is a heap but there's is some non clustered index and
the optimizer will use it, each time the same non clustered index wil be used
the order of the result set will be the same.
Finally, if the table have a clustered index, the result set will be always
in the same order (USING the QUERIES YOU SHOW AS SAMPLE). The clustered index
is a B-Tree structure in which the leaf level IS the data page, and
determines the phisical order of the rows.
Hugo Kornelis replied on 05-Apr-07 05:24 PM
Hi Gilberto,

Not even that is guaranteed. If the engine is halfway through a scan of
the table for another query, the engine will "piggyback" on that scan
for this query, then restart from the beginning up to where the
piggybacking started.

And in the next service pack, another optimization technique might be
added to the optimizer, and you'll get yet another order.

Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Mike C# replied on 05-Apr-07 10:32 PM
That's not guaranteed.  Parallelism, for instance, can cause your results to
be returned in helter-skelter order.  The only method guaranteed to return
your results in the same order *every single time* is to use the ORDER BY
clause.  That's what it's there for, if the order of the results is
important to you.