Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
share|improve this question
    
    
Similar question for Oracle: Does an IOT guarantee order in a select? –  ypercube Jan 12 '13 at 0:18
    
Similar question for MySQL: What is the default order of records for a SELECT statement in MySQL? –  ypercube Jan 12 '13 at 0:19
add comment

1 Answer 1

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!

share|improve this 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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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