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.
Thanks for the help.
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.