ORDER BY in INSERT INTO, how is it done?

Question

ORDER BY in INSERT INTO, how is it done?

Viewing 7 posts - 16 through 21 (of 21 total)

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

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

Chirag, even if there is a clustered index on the table, it doen't mean that records are stored physically in same order.

I know Itzik Ben-Gan has demonstrated this lately.

The index is ordered, yes.

N 56°04'39.16"
E 12°55'05.25"

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 2

Peso (11/13/2008)
Chirag, even if there is a clustered index on the table, it doen't mean that records are stored physically in same order.
I know Itzik Ben-Gan has demonstrated this lately.
The index is ordered, yes.

So, if you build a clustered index, even if the physical order is not in the same order, if I just pull a SELECT with no order by and include the clustered index in the result set, will the default be to order by the clustered index?

Or does it still depend on query optimiser at the moment of execution?

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

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

jcrawf02 (11/13/2008)
Peso (11/13/2008)
Chirag, even if there is a clustered index on the table, it doen't mean that records are stored physically in same order.
I know Itzik Ben-Gan has demonstrated this lately.
The index is ordered, yes.
So, if you build a clustered index, even if the physical order is not in the same order, if I just pull a SELECT with no order by and include the clustered index in the result set, will the default be to order by the clustered index?
Or does it still depend on query optimiser at the moment of execution?

It's also a factor of how the data is being retrieved. In some ways it oculd be a simple matter of where the heads are when the request comes in, or whether any of this data is already in cache, etc....

----------------------------------------------------------------------------------
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?

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

jcrawf02 (11/13/2008)
So, if you build a clustered index, even if the physical order is not in the same order, if I just pull a SELECT with no order by and include the clustered index in the result set, will the default be to order by the clustered index?

Maybe. Depends on what index the optimiser decides to use and if there's any parallelism involved.

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

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 5

Thanks Gail/Matt - now that I've read this thread, gotta go fix something . . .

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

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

jcrawf02 (11/13/2008)
now that I've read this thread, gotta go fix something . . .

😀 :hehe:

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

Johan Bijnens SSC Guru Points: 135377 More actions · Answer 7

As always ... if you want order, you must use ORDER BY in the final select clause.

However, at insert time, an order by can have some influence, as stated in the other replies above.

Needs it to be said, when using BULK insert, you'd be better off having your data sorted according to the clustering indexes sequence ?

Even it it were to relief sqlservers burden to sort.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me