0

Suppose I have a table Emp with emp_id, emp_name. I insert 3 entries in the table - (1,A) (2,B) (3,C).

I do not create any primary key nor add any indexes.

Now, when I run a select * statement, will the data be displayed as (1,A),(2,B),(3,C) every time or is there a possibility for the output to be (2,B), (1,A), (3,C) or in other variations.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Hardik Patel
  • 17
  • 2
  • See: http://stackoverflow.com/q/3574284/330315 and http://stackoverflow.com/q/36077399/330315 –  Aug 29 '16 at 10:44
  • 2
    Actually, in this case - three rows, inserted in this order, no CI, no NCIs, no updates/deletes, no rebuilds etc - these rows will be returned in this order. But of course, any schema or data or engine change could break it, so an ORDER BY is required. BTW, I don't understand how come this question got upvoted, being answered so many time already?? – dean Aug 29 '16 at 11:03

2 Answers2

3

The data can be displayed in any order, by a key, index, or even randomly .

The only way to "force" and guarantee the data will be displayed in the same order every time, is to use the ORDER BY clause and force the optimizer to select the data in the order you specifically asked for.

sagi
  • 40,026
  • 6
  • 59
  • 84
0

You need to add "ORDER BY" clause to guarantee order. Otherwise it's up to db engine, configuration and/or schema.

Read the following stackoverflow post, to get a more detailed reason.

Community
  • 1
  • 1
Kaveh Hadjari
  • 217
  • 1
  • 10