Question about ORDER BY

Question

Post reply

Question about ORDER BY

SqlDeveloper2246

SSC Eights!

Points: 886
More actions
May 11, 2008 at 5:40 am

#190198

When we use INSERT SELECT statement, why can't we order the rows before inserting them in the specified table?
what's the logic from that?
Regards

Viewing 7 posts - 1 through 6 (of 6 total)

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

viji-741955 Old Hand Points: 351 More actions · Answer 1

yes. thats true. coz thats not necessary actually.

after insertion in the table you can order the rows using ORDER BY,but not during insertion.

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 2

Since a table doesn't preserve the "insertion order" there is no reason to control the order in which rows are inserted. Better to let the optimizer do the best job it can by inserting the rows in whatever order it finds to be efficient.

Jeff Moden SSC Guru Points: 1003921 More actions · Answer 3

Actually, "It Depends". You can trick it... see the following...

http://www.sqlservercentral.com/articles/Advanced+Querying/61716/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

FerasGer83 (5/11/2008)
When we use INSERT SELECT statement, why can't we order the rows before inserting them in the specified table?
what's the logic from that?
Regards

If the table you are inserting into has an identity column, then the ORDER BY clause guarantees you that the rows will be assigned identity in the order dictated by the ORDER BY. However, without that identity column ALSO being your clustered index, you can't guarantee the physical order.

In either case - you can't control the physical processing order of the row insertions either.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

ricardo valdes-309372 Valued Member Points: 63 More actions · Answer 5

Depends on the state on the table being inserted into.

If it is a new table, temporary or truncated, the inserted records will be in order.

If the table has already been populated, and especially if CRUD has already been applied to it, the results of the insertions are generally unpredictable.

Try it:

--- create temporary work tables

select cast(0 as integer) as seqno, cast('' as varchar(11)) as "xvalue" into #tmpxxx where 0=1

select cast(0 as integer) as seqno, cast('' as varchar(11)) as "xvalue" into #tmpzzz where 0=1

-- insert test data in semi-random order

insert into #tmpxxx values (71,'700700020-7')

insert into #tmpxxx values (31,'300300060-3')

insert into #tmpxxx values (21,'200200070-2')

insert into #tmpxxx values (61,'600600030-6')

insert into #tmpxxx values (41,'400400050-4')

insert into #tmpxxx values (11,'100100080-1')

insert into #tmpxxx values (51,'500500040-5')

insert into #tmpxxx values (81,'800800010-8')

-- show test data

select * from #tmpxxx

-- insert into work table in sorted order

insert into #tmpzzz select * from #tmpxxx order by seqno

-- show inserted records as is

select * from #tmpzzz

-- do some CRUD

delete from #tmpxxx

delete from #tmpzzz where seqno in (11,31,51,61,71)

-- show remaining records

select * from #tmpzzz

-- insert new test data, in order

insert into #tmpxxx values (12,'100100080-1')

insert into #tmpxxx values (22,'200200070-2')

insert into #tmpxxx values (32,'300300060-3')

insert into #tmpxxx values (42,'400400050-4')

insert into #tmpxxx values (52,'500500040-5')

insert into #tmpxxx values (62,'600600030-6')

insert into #tmpxxx values (72,'700700020-7')

insert into #tmpxxx values (82,'800800010-8')

-- show test data

select * from #tmpxxx

-- raw insert

--note that the records are inserted (in order!)

--into the "empty slots" left from the delete

insert into #tmpzzz select * from #tmpxxx order by seqno

-- show it

select * from #tmpzzz

-- wrap up

drop table #tmpxxx

drop table #tmpzzz

Steve Jones - SSC Editor SSC Guru Points: 734639 More actions · Answer 6

In a new or old table, the rows are not in order. There is no guarantee without an ORDER BY clause. I could alter the clustered index, not the data or insertion order, and the rows would not be in order.