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 can I resolve the “Table 'dbo.Foo' already exists.” error when the table does not exist?
  • How to make a passthrough / passthru query editable?
  • Cannot connect to SQL Server Express 2014 from Visual Studio 2012
  • Calculating percentile rankings in MS SQL
  • How to save a PDF file in a SQL Server column using vb.net code
  • Select statement to return parent and infinite children
  • Thanks for the help.

  • How to extend the query to add 0 in the cell when no activity is performed
  • Custom sort in SQL Server
  • DECODE( ) function in SQL Server
  • Get consecutive numbers Range from SQL Server Table
  • Why can't I use an alias in a DELETE statement?
  • How Do I Deep Copy a Set of Data, and Change FK References to Point to All the Copies?
  • 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.