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)
Improve this question
3

1 Answer 1

Reset to default
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!

Improve this answer
4
  • 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, 2013 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, 2013 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, 2013 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, 2017 at 22:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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