2

Today after one of the drives on our sql server ran out of space. The drive contained eventracker logs, so I didn't think it was a big deal, but then I noticed when I insert a record into a database, it inserts it above previous records instead of appending it.

When I do select * the records that were inserted show up first. Is there something I can do to fix this?

section_d   section
-------------------
15          SDSD
16          SDSD2
17          SDSD2
18          adf

(4 row(s) affected)

insert into test2 values ('inserted row')

section_d   section
-----------------------
19          inserted row
15          SDSD
16          SDSD2
17          SDSD2
18          adf

(5 row(s) affected)
marc_s
  • 8,613
  • 6
  • 43
  • 51
Max Marchevsky
  • 21
  • 1
  • Similar questions for SQL-Server: [Why is SSMS inserting new rows at the top of a table not the bottom?](http://dba.stackexchange.com/questions/5774/why-is-ssms-inserting-new-rows-at-the-top-of-a-table-not-the-bottom/5775#5775) and [Storage order vs Result order](http://dba.stackexchange.com/questions/19203/storage-order-vs-result-order/19222#19222) – ypercubeᵀᴹ Jan 12 '13 at 00:17
  • Similar question for Oracle: [Does an IOT guarantee order in a select?](http://dba.stackexchange.com/questions/14570/does-an-iot-guarantee-order-in-a-select/14571#14571) – ypercubeᵀᴹ Jan 12 '13 at 00:18
  • Similar question for MySQL: [What is the default order of records for a SELECT statement in MySQL?](http://dba.stackexchange.com/questions/6051/what-is-the-default-order-of-records-for-a-select-statement-in-mysql/6053#6053) – ypercubeᵀᴹ Jan 12 '13 at 00:19

1 Answers1

8

Databases do not return rows in a given order unless you supply an ORDER BY clause in your query, thus making the INSERT "order" meaningless. The order of a SELECT * FROM MYTABLE; query is undefined.

Apologies for the simple answer!

Philᵀᴹ
  • 30,570
  • 9
  • 75
  • 105
  • I know, but before when i do an insert it would append the record, and doing a straight select * would return recently inserted records last, now they are first, so I am wondering if something else is wrong. – Max Marchevsky Jan 12 '13 at 00:12
  • 2
    Nothing is wrong and nothing can be fixed. Add an explicit `ORDER BY something` in your queries, if you want the results ordered. – ypercubeᵀᴹ Jan 12 '13 at 00:13
  • @MaxMarchevsky If you are worried about the database, you should check out DBCC CHECKDB http://msdn.microsoft.com/en-us/library/ms176064.aspx but the data order is not required to be anything unless you specify an ORDER BY - it's entirely possible the order is different because of something you did for space, but it may not be related at all. – Cade Roux Jan 12 '13 at 00:40
  • This is normal behaviour. SQL Server will insert the rows in the data space whenever it first finds empty space to insert them. This can be easily tested by inserting rows, deleting and few, and inserting new rows. The new rows will take the space of the deleted rows. SQL is based on set mathematics. What is important is the reult set, not the order. Unless you specify an ORDER BY claure, the order is irrelevant and not guaranteed to come in any fashion. – Steve Oakes Feb 02 '17 at 22:07