0

would you say it is safe to assume that output returned by 

   select * from table1;

will be ordered in the same way as the output of:

select * from table1 where table1.a<0;

where a is some random attribute of table1?

thanks!

Janna Sherazi
  • 167
  • 1
  • 1
  • 15
  • I don't see why not –  Jun 17 '16 at 20:34
  • 2
    It may depend on your DBMS software (SQL Server, MySQL, etc) - but generally no order is guaranteed. – Nick Jun 17 '16 at 20:34
  • 2
    No order is guaranteed unless it's explicitly specified, but it definitely depends on the DBMS – shamsup Jun 17 '16 at 20:37
  • This post might be helpful http://stackoverflow.com/questions/26236352/default-row-order-in-select-query-sql-server-2008-vs-sql-2012 –  Jun 17 '16 at 20:38
  • 2
    The general consensus is to never rely on the inherit ordering of your DB's storage. It will only end in tears. Use an `ORDER BY` clause in your SQL if it's important. – JNevill Jun 17 '16 at 20:39
  • 2
    Do you mean order of the rows or order of the columns – paparazzo Jun 17 '16 at 20:40

2 Answers2

1

If you mean columns then I have never seen it different from the order when edit table in SSMS but I am not sure if that is a hard rule.

There is no inherent order to rows. In absence of a sort the exact same query is not guaranteed to return rows in the same order each time.

this will be the same 

select * from table1 
order by table1.PK;

select * from table1 where table1.a<0
order by table1.PK;

even a table with a clustered PK is not guaranteed to return rows in that order without an order by clause

This has to be a dup. I will look for one and delete.

paparazzo
  • 44,497
  • 23
  • 105
  • 176
0

The order of the column is defined in database schema so you should consider the returned sequence of column in select * alway the same (for changing the column sequence there are specific command that alter this value in schema, is a variant of alter table)

the order of the row no .. if you want to be sure of a specific order by result for the selected rows you must explicitally set using a order by clause 

 select * from your_table
 order by your_column
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107