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?
Best Answer
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:
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.