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)
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!

  • 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 0: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 0:13
  • @MaxMarchevsky If you are worried about the database, you should check out DBCC CHECKDB 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 0: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 2 '17 at 22:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.