Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Display of a result set without order by

Author  Topic 

venkath
Posting Yak Master

202 Posts

Posted - 2008-04-08 : 09:53:07
Hi all

If we don't specify any Order by clause, what is the default order of displaying the data .

Will SQL Server show the data in a consistent order?

Thanks.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-08 : 09:54:46
It will show in order only if you have clustered index.
Depends on the query.
Go to Top of Page

venkath
Posting Yak Master

202 Posts

Posted - 2008-04-08 : 11:21:36
I am querying a view which was created using 3 tables. All the 3 tables have clustered indexes on them.

But each time i query the view with out order by clause, the data being displayed is inconstent.

is it the default behaviour of SQL Server?

Thanks
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-08 : 12:44:24
Without an order by clause it's up to the server how it returns data. This will depend on how the query gets executed and can vary from one execution to the next.
Doesn't matter whether there are clustered indexes or not.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-04-08 : 12:54:49
venkath -- just specify what order you want, and you'll get it. If you don't specify it, you don't get it. It's that simple, and order by clauses are not very hard to write.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

venkath
Posting Yak Master

202 Posts

Posted - 2008-04-08 : 12:57:36
I can use order by cluase, but i wanted to know on what basis sql server returns data with out an order by clause.

Thanks you all for your response.

Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-08 : 13:04:42
It just depends on the optimiser and probably the order in which parallel threads complete. Nothing you can predict.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
   

- Advertisement -