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.

Whenever I manually insert a row into a table in SQL Server Management Studio 2008 (the database is SQL Server 2005) my new row appears at the TOP of the list rather than the bottom. I'm using identity columns and this results in things like

id  row
42 first row
1 second row
2 third row

When rows are fetched and not explicitly ordered. This results in a different appearance when the rows are fetched for the web app and changes what a TOP 1 query returns.

I know I can order by them, but why is this happening? Most of my data is inserted through a web application, all inserts from this application result in a First In First Out ordering, e.g. latest insert is at the bottom, so the ids are all in a row. Is there some setting in the server or Management Studio that causes this improper ordering?

share|improve this question
    
As I have seen sometimes, it depends on the PK's ordering how the rows are displayed, if you select a single table. If you make some joins, it can change depending on the other tables PK's, FK's and indexes... –  Guillermo Gutiérrez Jul 18 '13 at 22:00
add comment

3 Answers

up vote 11 down vote accepted

In the SQL world, order is not an inherent property of a set of data. Thus, you get no guarantees from your RDBMS that your data will come back in a certain order -- or even in a consistent order -- unless you query your data with an ORDER BY clause.

From Craig Freedman:

Combining TOP with ORDER BY adds determinism to the set of rows returned. Without the ORDER BY, the set of rows returned depends on the query plan and may even vary from execution to execution.

Always use ORDER BY if you expect a well-defined and consistent order in your result set. Never rely on how your database may store the rows on disk (e.g. via a clustered index) to guarantee a certain ordering of data in your queries.

share|improve this answer
add comment

It's because the table is a heap table (most likely) and is not indexed. Make the ID column a PRIMARY KEY as well as IDENTITY. SQL Server physically stores the data based on the indexes - for example, if id were a clustered index (such as a primary key) the data would be physically stored in the order of the ids and would be returned that way in a query even without an ORDER BY clause. Otherwise, the ordering of the rows is not important to the database at all. Consequently, having an application depend on the order of the rows in the database (as well as depending on columns being in a certain order) is not a good practice. The application needs to work with whatever keys there are in the database to identify rows.

share|improve this answer
    
Good to know. I knew this was a hint to change the app to use order by (it's an inherited app with a great deal of bad practice) but I was wondering exactly why it happened. –  Ben Brocka Sep 14 '11 at 19:49
3  
Change the structure of the table just so that SELECT * without ORDER BY might come back in the "right" order (but still not be guaranteed)? –  Aaron Bertrand Sep 14 '11 at 20:22
    
On a simple SELECT from the table with a clustered index would come back in the order of the clustered index. Wasn't as clear as I should have been that it was an illustration of when data is ordered by a particular column physically, but it's definitely not a given that in every query it would be returned correctly. Mea culpa. –  Wil Sep 14 '11 at 20:27
3  
It doesn't really matter what the clustered index is. SQL Server may still return the order based on some other index based on a wide variety of factors. Creating a primary key on some column DOES NOT guarantee that selects with no order by will suddenly come back ordered by that column always. Might you observe that most of the time? Sure. But that is not the same as a guarantee. I've never seen a polar bear on my street but there is no polar bear forcefield that prevents it from happening. –  Aaron Bertrand Sep 14 '11 at 20:41
2  
Anyway my point was that adding an ORDER BY clause is a much better guarantee (never mind much less disruptive) than making schema changes to the table and hoping that the ordering will be as you expect, forever. –  Aaron Bertrand Sep 14 '11 at 20:42
add comment

Just to augment the other answers: a table is, by definition, an unordered set of rows. If you don't specify an ORDER BY clause, SQL Server is free to return the rows in whatever order it deems most efficient. This will often just happen to coincide with the order of insert, since most tables have a clustered index on identity, datetime or other monotonically increasing columns, but you should treat it exactly as that: a coincidence. It can change with new data, a statistics update, a trace flag, changes to maxdop, query hints, changes to the join or where clauses in the query, changes to the optimizer due to a service pack/cumulative update/hotfix/upgrade, moving the database to a different server, etc. etc.

In other words, and I know you already know the answer, but it can't be stated enough:

If you want to rely on the order of a query, ALWAYS add ORDER BY.

share|improve this answer
    
I wasn't planning on depending on the order, but visually it is a bit annoying that my last inserted items are suddenly at the top. –  Ben Brocka Sep 14 '11 at 20:18
    
I suppose this is because you're using Open Table. In Management Studio 2008 this command has been diverged into "Edit Top n Rows" and "Select Top N rows" - when you choose the latter, you are free to edit the resulting query, e.g. remove the top and add an order by. –  Aaron Bertrand Sep 14 '11 at 20:21
    
It's Management studio 2008, the server is 2005, should have been clearer. I didn't know there was an "open table" command, I've always used the select statments –  Ben Brocka Sep 14 '11 at 20:36
3  
Ok, well point still stands, I understand it's annoying that the data comes back in an order you don't expect, but I hope it's clear now why SQL Server doesn't really care what order you expect unless you tell it that you care by adding an order by clause. :-) –  Aaron Bertrand Sep 14 '11 at 20:37
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.