SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ORDER BY = Bubble Sort ? Quick Sort ? Insertion Sort ?


ORDER BY = Bubble Sort ? Quick Sort ? Insertion Sort ?

Author
Message
GilaMonster
GilaMonster
SSC Guru
SSC Guru (368K reputation)SSC Guru (368K reputation)SSC Guru (368K reputation)SSC Guru (368K reputation)SSC Guru (368K reputation)SSC Guru (368K reputation)SSC Guru (368K reputation)SSC Guru (368K reputation)

Group: General Forum Members
Points: 368948 Visits: 46945
karthik M (3/8/2013)
whereever the optimizer sort the data, it sould use some algorothim to do this operation? Right?

My question is simple, is there any alogrithm used for ORDER BY clause or not by the optimizer?


As I said earlier, the optimiser won't use any algorithm. The optimiser optimises queries, it doesn't run them.

Sure there will be algorithms used for order by (and for every single other operation given the definition of 'algorithm'). Why does it matter which one SQL uses?

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


karthik M
karthik M
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10691 Visits: 2588
Why does it matter which one SQL uses?


I am asking "Out of Curiosity" to know the algorithm used by the engine.

karthik
karthik M
karthik M
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10691 Visits: 2588
is this a secret maintened by microsoft ? :-)

karthik
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)

Group: General Forum Members
Points: 145309 Visits: 33199
karthik M (3/8/2013)
is this a secret maintened by microsoft ? :-)


Short answer, yes.

Longer answer, Microsoft publishes some of the internals because it helps people understand how or why things are occurring within the system. Also, some people figure out some of the internals through trial & error, investigation, or even putting a debugger on the system. But all the internals are not published. There are lots and lots of proprietary information that Microsoft has inside the code that they just don't feel the need to share. The exact sorting process used by the query engine (not the optimizer) is not something I've seen documentation on, so I suspect it's protected.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
HowardW
HowardW
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10763 Visits: 9893
karthik M (3/8/2013)
is this a secret maintened by microsoft ? :-)


In the sense that it's proprietary information that they've chosen not to share and SQL Server is a closed source product, yes.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (368K reputation)SSC Guru (368K reputation)SSC Guru (368K reputation)SSC Guru (368K reputation)SSC Guru (368K reputation)SSC Guru (368K reputation)SSC Guru (368K reputation)SSC Guru (368K reputation)

Group: General Forum Members
Points: 368948 Visits: 46945
karthik M (3/8/2013)
Why does it matter which one SQL uses?


I am asking "Out of Curiosity" to know the algorithm used by the engine.


So download the public symbols, attach a debugger to SQL Server and walk through what runs when you order a query.

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


Jason-299789
Jason-299789
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8229 Visits: 3232
I would suspect that there's some sort of internal algorithm used to select the best sort algorithm for a given data set, based on the data type, data volumes etc

_________________________________________________________________________
SSC Guide to Posting and Best Practices
Matt Miller (4)
Matt Miller (4)
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46598 Visits: 19255
Besides - all of those sorts methods you mentioned presume a flat list structure. The internals for how tables are structured are anything but flat.

In short - I suspect the actual answer is "none of the above".

----------------------------------------------------------------------------------
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?
crmitchell
crmitchell
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1716 Visits: 1860
Matt Miller (#4) (3/8/2013)
Besides - all of those sorts methods you mentioned presume a flat list structure. The internals for how tables are structured are anything but flat.

In short - I suspect the actual answer is "none of the above".


I'd tend to agree with Matt on this - my suspicion is that it will use a heavily optimised hash table in most circumstances but the only people that can give you a definitive answer would be Microsoft.
You could try asking on MSDN.
Eugene Elutin
Eugene Elutin
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20812 Visits: 5478
Curiosity is a great thing!
Without it, elephants wouldn't have trunks...

What does the Crocodile have for dinner?...

Hehe

_____________________________________________
"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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search