Re: why the result of the query is in asending order

On Tue, 19 Apr 2005 01:43:04 -0700, bhavik wrote:

>Hi, Hugo
>
>thanks for ur kind reply
>what u suggest me, will not solve my problem
>
>i want to do exactly reverse of this
>
>I dont want my data(result of query)to be order by.
>
>i want to know why sql server returns me data in order by
>
>Pravin

Hi Pravin,

As I said, without ORDER BY clause SQL Server is free to return the data
in any order it wants. Changes in available memory and processors can
change the execution plan (and hence the order of rows returned). If an
other process is reading the same data, your process might "piggyback"
on that process to prevent a second read operation, resulting in yet
another ordering.

And even on a system with no other users and no fluctuations in
available resources, ordering of rows might be quite unpredictable, as
can easily be showed by running this code in Query Analyzer. Note the
deliberate error in Paul McCartney's name. If you correct it, you'll see
that this will result in yet another order of rows!

CREATE TABLE Beatles (id int PRIMARY KEY,
fname varchar(10) UNIQUE,
lname varchar(10) UNIQUE)
go
INSERT Beatles (id, fname, lname)
SELECT 1, 'John', 'Lennon'
UNION ALL
SELECT 2, 'Paul', 'Cartney' -- Should be 'McCartney'
UNION ALL
SELECT 3, 'George', 'Harrison'
UNION ALL
SELECT 4, 'Ringo', 'Starr'
go
SELECT * FROM Beatles
SELECT id, fname FROM Beatles
SELECT id, lname FROM Beatles
go
DROP TABLE Beatles
go

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
.