0

I created a table with two columns(name varchar, dob datetime). I have inserted a row

insert into table values ( 'jimmy', '19970819 05:00:00');

When I print the table jimmy is in the first row. Now, I updated my table 

alter table test1 add d date;

and inserted a row

insert into table values ( 'john', '19970819 05:00:00', '19950819');

Now, when I print my table, jimmy is now the second row.

After this I have added some other rows and all these rows are inserted into the table between john and jimmy serially. jimmy is in the last row now.

My question is on what basis SQL inserts rows into a table? Is there any order for insertion?

Dale K
  • 25,246
  • 15
  • 42
  • 71
anonymous_coder
  • 119
  • 1
  • 9
  • 5
    The ordering of rows in SQL query results **is always undefined** unless you specify an `ORDER BY` clause. If you want rows returned in the order they're inserted then use an `IDENTITY` column and `ORDER BY` it. You should also add a clustered primary-key value to your table (often the `IDENTITY` column will be the primary key because it's guaranteed to be unique). – Dai Mar 24 '20 at 00:34
  • @Dai now, i have tried on another table and tried the same steps as mentioned in the question above, but now results i am getting are the same order as the order i inserted into the table. – anonymous_coder Mar 24 '20 at 00:45
  • There are multiple answers in that linked question, what specific steps did you follow? And what order of results do you want? What trouble are you having exactly? – Dai Mar 24 '20 at 00:54
  • @Dai i am talking about the question i posted here. repeated the steps i mentioned in the question i posted (not the link). – anonymous_coder Mar 24 '20 at 01:02
  • 3
    Your question "On what basis SQL inserts rows into a table" is answered in the duplicate. However if your real question is something like "How can I order my rows by the insertion date" then the answer is add a column `InsertionDate` and default it to `current_timestamp` and then order by this column. Given its not clear what you are asking you may well want to ask a new, clear, question. – Dale K Mar 24 '20 at 01:02

0 Answers0