INSERT INTO SELECT WITH ORDER BY

Question

Post reply

INSERT INTO SELECT WITH ORDER BY

nguyennd

SSCommitted

Points: 1881
More actions
June 21, 2010 at 8:50 pm

#224116

I have a question 🙁
IF i insert into a table with identity column. EX
DECLARE @AAAATABLE TABLE(
K INT IDENTITY(1,1)
,V1 VARCHAR(10)
,V2 VARCHAR(10)
)
DECLARE @DATATABLE TABLE(
V1 VARCHAR(10)
,V2 VARCHAR(10)
)
INSERT INTO @DATATABLE(V1,V2)
SELECT 1 AS V1, 2 AS V2
UNION ALL SELECT 2 AS V1, 1 AS V2
UNION ALL SELECT 3 AS V1, 2 AS V2
UNION ALL SELECT 2 AS V1, 3 AS V2
UNION ALL SELECT 4 AS V1, 6 AS V2
UNION ALL SELECT 1 AS V1, 1 AS V2
--INSERT INTO SQL
INSERT INTO @AAAATABLE
SELECT V1,V2 FROM @DATATABLE ORDER BY V1,V2
--TEST SQL
SELECT * FROM @AAAATABLE ORDER BY K
When,How,Why result of TEST SQL is not ORDER with V1,V2?
http://unitech.vn

Viewing 15 posts - 1 through 15 (of 39 total)

You must be logged in to reply to this topic. Login to reply

SSIS developer SSC Journeyman Points: 95 More actions · Answer 1

Hi,

I might be wrong, but the result of the TEST sql is ordered by V1,V2.

According to your query, the result is ordered by the values in column V1 first and then by the values in column V2.

I hope I have interpreted your question properly and this reply answers your query.:-)

nguyennd SSCommitted Points: 1881 More actions · Answer 2

Yes, TEST SQL always order by V1 then V2, but what exception make it don't sort?

http://unitech.vn

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 3

Your result is ordered by k. It really doesn't matter what in order you load the table, you order it on the select out, or use a clustered index.

Converting oxygen into carbon dioxide, since 1955.

nguyennd SSCommitted Points: 1881 More actions · Answer 4

😀 My Flow IS:

- Truncate table @AAAATABLE

- INSERT INTO @AAAATABLE from @DATATABLE with ORDER BY V1,V2

- SELECT * FROM @AAAATABLE ORDER BY K

I want a result with ordered by V1,V2 but i don't want ORDER BY V1,V2 besauce @AAAATABLE hasn't an index with (V1,V2). What is my wrong?

http://unitech.vn

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Steve Cullen (6/21/2010)
It really doesn't matter what in order you load the table, you order it on the select out, or use a clustered index.

There's no OR here. If you want to retrieve the data in a particular order, there must be an order by on that select. The presence of a clustered index does not guarantee the order of data returned, without an ORDER BY, SQL is free to order the data in any way it likes. Yes, a simple query will often (usually) return data in the order of the index used to retrieve the data, but that's not a guarantee that it always will.

I want a result with ordered by V1,V2 but i don't want ORDER BY V1,V2 besauce @AAAATABLE hasn't an index with (V1,V2). What is my wrong?

If you want the result ordered by V1, V2, the select must have an ORDER BY V1, V2 on it. Presence or absence of an index is irrelevant, you want the data ordered that way, specify the order by.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

shield_21 Ten Centuries Points: 1033 More actions · Answer 6

Hi there,

The result is also sorted by V1 and V2 in ascending order since that is the order when you inserted those records into @AAAATABLE.. Running your query produces this result:

K V1 V2

--------------

111

212

321

423

532

646

which is already your expected result, is this right? 😛

SELECT * FROM @AAAATABLE ORDER BY K

OR

SELECT * FROM @AAAATABLE ORDER BY V1, V2

will just produce the same result..

Regards,

shield_21

nguyennd SSCommitted Points: 1881 More actions · Answer 7

GilaMonster (6/21/2010)
Steve Cullen (6/21/2010)
It really doesn't matter what in order you load the table, you order it on the select out, or use a clustered index.
There's no OR here. If you want to retrieve the data in a particular order, there must be an order by on that select. The presence of a clustered index does not guarantee the order of data returned, without an ORDER BY, SQL is free to order the data in any way it likes. Yes, a simple query will often (usually) return data in the order of the index used to retrieve the data, but that's not a guarantee that it always will.
I want a result with ordered by V1,V2 but i don't want ORDER BY V1,V2 besauce @AAAATABLE hasn't an index with (V1,V2). What is my wrong?
If you want the result ordered by V1, V2, the select must have an ORDER BY V1, V2 on it. Presence or absence of an index is irrelevant, you want the data ordered that way, specify the order by.

But in this case, my flow use insert into select with order by (K is IDENTITY). How many percent the result cannot order V1,V2?

http://unitech.vn

Dave Ballantyne SSC-Dedicated Points: 33566 More actions · Answer 8

nguyennd (6/21/2010)
Yes, TEST SQL always order by V1 then V2, but what exception make it don't sort?

Are you saying you have an example where it doesn't sort correctly ?

Any multiple digit number will cause 'incorrect' ordering here as you have used a varchar in the temp tables.

So, 10 will sort before 2.

Clear Sky SQL
My Blog[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

nguyennd (6/22/2010)
But in this case, my flow use insert into select with order by (K is IDENTITY). How many percent the result cannot order V1,V2?

The order inserted is irrelevant. If you want a guaranteed order, use ORDER BY. Then the order will always be by V1, V2

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

shield_21 (6/22/2010)
The result is also sorted by V1 and V2 in ascending order since that is the order when you inserted those records into @AAAATABLE..

Order that rows were inserted does not guarantee order that rows will be retrieved. If results are needed sorted in a particular order, ORDER BY must be specified.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

nguyennd SSCommitted Points: 1881 More actions · Answer 11

Híc, i found this solution in http://support.microsoft.com/kb/273586

It say:

The following INSERT INTO SELECT FROM query with an ORDER BY clause will guarantee that column ID in NewTable is in the same order as Col1.

INSERT INTO NewTable (Col1, Col2) SELECT Col1, Col2 FROM OldTable ORDER BY Col1

ID (identity) Col1 Col2

------------- ------ ------

1 1 S

2 2 z

3 7 G

4 11 F

5 17 I

Note ID identity column is generated to have same order as of Col1. However, INSERT INTO doesn’t guarantee the physical order of either ID or Col1 in NewTable. To retrieve the data in desired order, an ORDER BY clause is required as shown by following SELECT statement:

SELECT * from NewTable ORDER BY ID

http://unitech.vn

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 12

DECLARE @AAAATABLE TABLE(

K INT IDENTITY(1,1)

,V1 VARCHAR(10)

,V2 VARCHAR(10)

)

DECLARE @DATATABLE TABLE(

V1 VARCHAR(10)

,V2 VARCHAR(10)

)

INSERT INTO @DATATABLE(V1,V2)

SELECT 1 AS V1, 2 AS V2

UNION ALL SELECT 2 AS V1, 1 AS V2

UNION ALL SELECT 3 AS V1, 2 AS V2

UNION ALL SELECT 2 AS V1, 3 AS V2

UNION ALL SELECT 4 AS V1, 6 AS V2

UNION ALL SELECT 1 AS V1, 1 AS V2

select * from @DATATABLE

--INSERT INTO SQL

INSERT INTO @AAAATABLE

SELECT V1,V2 FROM @DATATABLE --ORDER BY V1,V2

--TEST SQL

SELECT * FROM @AAAATABLE ORDER BY K

Malleswarareddy
I.T.Analyst
MCITP(70-451)

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 13

GilaMonster (6/21/2010)
...
There's no OR here. If you want to retrieve the data in a particular order, there must be an order by on that select. The presence of a clustered index does not guarantee the order of data returned, without an ORDER BY, SQL is free to order the data in any way it likes. Yes, a simple query will often (usually) return data in the order of the index used to retrieve the data, but that's not a guarantee that it always will....

The "quirky update" method is based on the assumption/fact that the simpe query is always retrieves or operates with data in the order of the clustered index by default.

I tend to agree with you about "but that's not a guarantee that it always will", however when fronted with Jeff Moden challenge to provide the example when it doesn't, it will be hard to do so.

It looks like the simple queries do always retrieve the data in order of the table's clustered index, even so in SQL specification it is not guaranteed.

Would be cool to know exact algorithm used by MS now. May be it was possible long ago, but no one bothered to change the documentation regarding the above. Do we have here any developer invloved in the development of SQL Server itself, who could put some light on this?

It's looks very logical that cheapest way to retrieve the data from storage is in the order of how the data is physically stored.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Dave Ballantyne SSC-Dedicated Points: 33566 More actions · Answer 14

The "quirky update" method is based on the assumption/fact that the simpe query is always retrieves or operates with data in the order of the clustered index by default.
I tend to agree with you about "but that's not a guarantee that it always will", however when fronted with Jeff Moden challenge to provide the example when it doesn't, it will be hard to do so.
It looks like the simple queries do always retrieve the data in order of the table's clustered index, even so in SQL specification it is not guaranteed.
Would be cool to know exact algorithm used by MS now. May be it was possible long ago, but no one bothered to change the documentation regarding the above. Do we have here any developer invloved in the development of SQL Server itself, who could put some light on this?
It's looks very logical that cheapest way to retrieve the data from storage is in the order of how the data is physically stored.

IMO , to rely upon the data being returned in any particular order without specifying "order by" is stupidity. You dont know what will happen in the future ( or if some else will change the clustering key )

The quirky update works NOW and does not break NOW. Thats all that can be guaranteed.

Data is not PHYSICALLY stored in the clustered key order , but logically.

If it were physical , at every page split all the following pages would have to be shuffled forward.

So if it were to be returned in LOGICAL order , as that would be the fastest reading from page 0 through to page N , the result set would be 'Out of order'

Clear Sky SQL
My Blog[/url]