How are records sorted without an order by clause?

Question

Post reply

How are records sorted without an order by clause?

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

I would say the DBA is being stupid about this. The query is obviously not returning the results in the correct order when the application requires it, so why wouldn't you use an ORDER BY?

The fact that a ORDER BY may require more resources is completely irrelevant if that is what the application requires. The server is there to serve the application.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

Hey guys... I put some bum dope out about the clustered index method and I've edited out the post I made about it. My fault, I didn't test well enough. :blush: Sorry for the confusion and thanks for the correction Michael, Peso, and Gail.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

Michael Valentine Jones (9/6/2008)

I have posted examples like this on SQLTeam several times that shows a select from a table with a clustered index that returns results not in cluster index order. If you want your query results in order, use an ORDER BY.

Heh... where there's a will, there's a way...

select Number from #t WHERE Number >= 0

... but I DO get your point. I've made a mistake and I've gone back and removed the bum-dope post I made. Thanks, Michael.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

By the way... just to explain my embarrassing statements... here's why I said them. All I did was change the table name from #t to t and name the index. All 3 methods produce the correct sorted output... still, now that I see what it does with a temp table, I won't trust it in a SELECT anymore....

drop table t



go

create table t (number int , CONSTRAINT PK_Number PRIMARY KEY CLUSTERED (NUMBER))

insert into t (number)

select

        number

from

        -- Number Table Function available here

        --http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685

        F_TABLE_NUMBER_RANGE(500,1000)

order by

        number

insert into t (number)

select

        number

from

        F_TABLE_NUMBER_RANGE(100,199)

order by

        number

insert into t (number)

select

        number

from

        F_TABLE_NUMBER_RANGE(1,50)

order by

        number

insert into t (number)

select

        number

from

        F_TABLE_NUMBER_RANGE(300,499)

order by

        number

select Number from t

select Number from t WITH(INDEX(PK_Number))

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

Yes, I saw the same thing when I changed my code to a permanent table. However, that is not really proof that there is no case with a permanent table where you can't get out of order results. How could you really test it anyway? Run all possible queries with all possible databases on all possible servers?

Since Microsoft does not make any promises about query order when you don't use an order by, I wouldn't depend on it, even it you could prove it. There is nothing preventing them from making a change in the next version or even service pack that changes how it works.

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 6

You could offer the dba a weeping tissue because he's the one messing up. They will have to modify their code.

As already stated : If you want order, you need to put an order by in your final select.

However, with sql2000 many "smarties" may suggest to

create a view select top 100 percent * from table order by the_wanted_order;

But they will have to adjust their code anyway if they want to upgrade to sql2005, surly for sql2008 !

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

Michael Valentine Jones (9/6/2008)

There is nothing preventing them from making a change in the next version or even service pack that changes how it works.

Heck... that true even about stuff they do have clearly documented. 😀

Anyway, I agree... the way it operates in TempDB is enough to cast suspicion on using it for "orderless" SELECT's.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions