Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

ORDER BY = Bubble Sort ? Quick Sort ? Insertion Sort ? Expand / Collapse
Author
Message
Posted Friday, March 8, 2013 2:54 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:03 AM
Points: 40,385, Visits: 36,829
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 2008, MVP
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

Post #1428467
Posted Friday, March 8, 2013 4:09 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 10:41 PM
Points: 2,031, Visits: 2,532
Why does it matter which one SQL uses?


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



karthik
Post #1428502
Posted Friday, March 8, 2013 4:10 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 10:41 PM
Points: 2,031, Visits: 2,532
is this a secret maintened by microsoft ?

karthik
Post #1428503
Posted Friday, March 8, 2013 4:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:31 PM
Points: 14,000, Visits: 28,381
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1428506
Posted Friday, March 8, 2013 4:22 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 3, 2014 8:05 AM
Points: 1,191, Visits: 9,892
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.
Post #1428508
Posted Friday, March 8, 2013 4:48 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:03 AM
Points: 40,385, Visits: 36,829
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 2008, MVP
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

Post #1428519
Posted Friday, March 8, 2013 5:13 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 4:25 AM
Points: 918, Visits: 2,507
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
Post #1428529
Posted Friday, March 8, 2013 8:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:39 PM
Points: 7,152, Visits: 15,634
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?
Post #1428602
Posted Monday, March 11, 2013 4:26 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 7:18 AM
Points: 220, Visits: 887
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.
Post #1429143
Posted Monday, March 11, 2013 5:05 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 3:45 AM
Points: 2,873, Visits: 5,188
Curiosity is a great thing!
Without it, elephants wouldn't have trunks...

What does the Crocodile have for dinner?...




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

How to post your question to get the best and quick help
Post #1429162
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse