When I Insert Rows into a SQL Server 2005 Table will the order be preserved?

When I select a series of rows from a sql server 2005 table using an “order by” clause and then insert them into a different (and empty) sql server 2005 table can I count on the rows staying in the same order.

The reason I am asking is that I want to manipulate the rows using ADO.net. I want the rows in a specific order to improve the speed of the calculations I do using ADO.net.

  • How do I enforce data integrity rules in my database?
  • SQL Server 2012 query is not executing on test server
  • Sql, do one expression if other expression is true
  • How to specify a port number in SQL Server connection string?
  • How to return zero using Count() with multiple tables
  • Use Ms SQL SERVER full text search to match words
  • Thanks for the help.

  • Can I use ADFS 2.0 to authenticate certain users against SQL Server?
  • find start and stop date for contiguous dates in multiple rows
  • TSQL pivot with multiple column headers
  • Using a .MDF SQL Server Database with ASP.NET Versus Using SQL Server
  • How to combine date with time, typed by hand in SQL Server?
  • sql join from microsoft access table to another table through odbc using pass through query
  • 3 Solutions collect form web for “When I Insert Rows into a SQL Server 2005 Table will the order be preserved?”

    Relational databases work on unordered sets. These sets do not have an order unless you explicitly apply an order to them. If you want the same order in the second table as when you selected from the first, then you need to apply the equivalent ORDER BY clause.

    While the order that you insert will be preserved, it can’t be depended on – a number of events in SQL Server can re-order a table, including adding a clustered index, or some other user doing a number of things (for example: insert into # select * where something, truncate table, insert into table select * from #, all without your knowledge).

    As John stated, the only way to ensure a specific order is to specify the ORDER BY clause on some identifier.

    Just be mindfull that Primary Keys default to being clustered indexes. This clustered index used as the physical order that the data is stored down on the disk drive.

    If you want to improve the speed and have complete control over the entire system (and understand all the repercussions of doing this change) then you can either look at indexing the order by clause or (in extreme cases only) apply a clusted index on those ordered fields.

    There are many websites that provide good insight into clustered indexes and what they actually mean underneath.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.