I would want to leave a sorted table, ie when I did a query select * from NewTable I obtained the sorted table.
I've tried, but not sort the table how I specify
select column1,column2,column3,column4
into NewTable
from Table1,Table2
order by column1,column2
You only get result sets in a particular order when you use order by. Tables represent unordered sets, so they have no order except when being output as result sets.
However, you can use a trick in SQL Server to make that order by fast. The trick is to using the order by in insert and have an identity primary key. Then ordering by the primary key should be very efficient. You could do this as:
create table NewTable (
NewTableId int identity(1, 1) not null primary key,
column1 . . .
. . .
);
insert into NewTable(column1, column2, column3, column4)
select column1, column2, column3, column4
from Table1 cross joinTable2
order by column1, column2;
Now when you select from the table doing:
select column1, column2, column3, column4
from NewTable
order by id;
You are ordering by the primary key and no real sort is being done.
The clustered index of a table decides how the data is ordered, this example will demonstrate it:
CREATE TABLE test (id int, value varchar)
INSERT INTO test VALUES(1, 'z')
INSERT INTO test VALUES(2, 'y')
INSERT INTO test VALUES(3, 'x')
SELECT * FROM test
CREATE CLUSTERED INDEX IX_test ON test (value ASC)
SELECT * FROM test
This is the result:
id value
----------- -----
1 z
2 y
3 x
id value
----------- -----
3 x
2 y
1 z
After creating the index, the result is reversed, since the index is sorting the value-column ascending.
However please note, as others have mentioned, that the only 100% guaranteed way to get a correctly ordered result is to use an ORDER BY clause.
JOIN or other set operation. There is no guaranteed order without an ORDER BY.
The answer "The clustered index of a table decides how the data is ordered..." is incorrect. Without an ORDER BY the result set is returned in random order.
It depends on numerous facts. But one of the obvious ones is the index being used:
Here's a simple example to show that the previous statement is wrong:
create table #t (id int identity (1,1) primary key clustered, col1 int)
INSERT INTO #t (col1)
values
(5),
(4),
(3),
(2),
(1),
(0)
SELECT col1 FROM #t
CREATE INDEX IX_t
ON #t (col1);
SELECT col1 FROM #t
Even if the clustered index is present, with a covering index in a different sort order data will be returned more likely in the order of the index being used instead of the clustered index. But if there are some pages already in memory and other ones need to get loaded from disc, the result set might look different again.
To summarize: Without ORDER BY the sort order cannot be guaranteed.
The ORDER of your result set is dictated by the execution plan, if the query uses an index AND there is no ORDER BY then the sort will be a result of that index. There is no guarantee on the order unless you issue an ORDER BY
