How to arrange rows of a table in ascending order and to save the table simultaneously?

Question

I want to create a new table by arranging the rows in ascending order from the existing table. The code I am trying to use is:

SELECT * 
   INTO new_table
FROM existing_table
ORDER BY col1, col2

However new table does not show any ascending arrangement of rows. Can anybody tell me what is wrong with this code ?

Answer 1

Rows in a table are unordered, so it doesn't make sense to talk about rows being in order. And, the result set from a query is unordered, unless you use an order by clause.

That said, you can have the same effect of placing rows into a table in an ordered fashion. Here is a method.

select top 0 *
into new_table
from existing_table;

alter table new_table add new_table_id int identity(1, 1);

insert into new_table(<list of columns without new_table_id>)
    SELECT <list of columns without new_table_id>
    INTO new_table
    FROM existing_table
    ORDER BY col1, col2;

The id column is guaranteed to be in the correct order. In practice, it seems that the rows will be inserted in order. Strictly speaking, the insert order is not guaranteed although the values of the id are ordered correctly.

As mentioned in the comment, you can also do:

alter table new_table add new_table_id int identity(1, 1) not null primary key;

You can do this because the table is empty. As a primary key, the data should be inserted in order.

As a note, though. The query:

select *
from new_table;

does not guarantee the ordering of the results. It makes no difference what the insert order is into the table. You cannot depend on the results being in a particular order just because the rows were ordered that way. For instance, in a multi-threaded environment, the results will generally not be in order, either in theory or in practice.