InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 07:57:35 GMT20http://www.sqlservercentral.com/Forums/Topic1427851-392-1.aspxI thought Fried Rice :-D & Egg Omlet :-PWed, 13 Mar 2013 01:07:28 GMTkarthik Mhttp://www.sqlservercentral.com/Forums/Topic1427851-392-1.aspx[quote][b]Lynn Pettis (3/11/2013)[/b][hr][quote][b]Eugene Elutin (3/11/2013)[/b][hr]Curiosity is a great thing!Without it, elephants wouldn't have trunks...What does the Crocodile have for dinner?...:hehe:[/quote]Whatever it wants...[/quote]It prefers to start with curios, little elephants, actually... (as per Rudyard Kipling):hehe:Mon, 11 Mar 2013 06:12:59 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1427851-392-1.aspx[quote][b]Eugene Elutin (3/11/2013)[/b][hr]Curiosity is a great thing!Without it, elephants wouldn't have trunks...What does the Crocodile have for dinner?...:hehe:[/quote]Whatever it wants...Mon, 11 Mar 2013 06:04:09 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1427851-392-1.aspxCuriosity is a great thing!Without it, elephants wouldn't have trunks...What does the Crocodile have for dinner?...:hehe:Mon, 11 Mar 2013 05:05:00 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1427851-392-1.aspx[quote][b]Matt Miller (#4) (3/8/2013)[/b][hr]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".[/quote]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.Mon, 11 Mar 2013 04:26:41 GMTcrmitchellhttp://www.sqlservercentral.com/Forums/Topic1427851-392-1.aspxBesides - 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".Fri, 08 Mar 2013 08:23:12 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic1427851-392-1.aspxI 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 etcFri, 08 Mar 2013 05:13:07 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1427851-392-1.aspx[quote][b]karthik M (3/8/2013)[/b][hr][quote]Why does it matter which one SQL uses? [/quote]I am asking "Out of Curiosity" to know the algorithm used by the engine.[/quote]So download the public symbols, attach a debugger to SQL Server and walk through what runs when you order a query.Fri, 08 Mar 2013 04:48:57 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1427851-392-1.aspx[quote][b]karthik M (3/8/2013)[/b][hr]is this a secret maintened by microsoft ? :-)[/quote]In the sense that it's proprietary information that they've chosen not to share and SQL Server is a closed source product, yes.Fri, 08 Mar 2013 04:22:05 GMTHowardWhttp://www.sqlservercentral.com/Forums/Topic1427851-392-1.aspx[quote][b]karthik M (3/8/2013)[/b][hr]is this a secret maintened by microsoft ? :-)[/quote]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.Fri, 08 Mar 2013 04:20:59 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1427851-392-1.aspxis this a secret maintened by microsoft ? :-)Fri, 08 Mar 2013 04:10:46 GMTkarthik Mhttp://www.sqlservercentral.com/Forums/Topic1427851-392-1.aspx[quote]Why does it matter which one SQL uses? [/quote]I am asking "Out of Curiosity" to know the algorithm used by the engine. Fri, 08 Mar 2013 04:09:06 GMTkarthik Mhttp://www.sqlservercentral.com/Forums/Topic1427851-392-1.aspx[quote][b]karthik M (3/8/2013)[/b][hr]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?[/quote]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?Fri, 08 Mar 2013 02:54:47 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1427851-392-1.aspxThe optimiser is the wrong term for this, it's not the query optimiser that performs sorts. The Query Engine obviously uses algorithms to perform sorts. Those algorithms are proprietary and undocumented, but you'd imagine them to be based on some of the better performing sorting mechanisms above, tweaked and changed for SQL's internal storage characteristics, index usage, parallelism, statistics, etc.Fri, 08 Mar 2013 02:42:19 GMTHowardWhttp://www.sqlservercentral.com/Forums/Topic1427851-392-1.aspxwhereever 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?Fri, 08 Mar 2013 01:57:40 GMTkarthik Mhttp://www.sqlservercentral.com/Forums/Topic1427851-392-1.aspx[quote][b]Grant Fritchey (3/7/2013)[/b][hr][quote][b]GilaMonster (3/7/2013)[/b][hr]Unless the data's small enough it can manage entirely within the memory grant. Not much chance of that except with trivial data.[/quote]Not arguing, just clarifying, won't it allocate some space on tempdb anyway?[/quote]Sorts I don't think so, not unless it actually spills. Until it spills, it's within the query execution's memory memory space.Thu, 07 Mar 2013 09:45:14 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1427851-392-1.aspx[quote][b]GilaMonster (3/7/2013)[/b][hr]Unless the data's small enough it can manage entirely within the memory grant. Not much chance of that except with trivial data.[/quote]Not arguing, just clarifying, won't it allocate some space on tempdb anyway?Thu, 07 Mar 2013 09:34:35 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1427851-392-1.aspx[quote][b]Grant Fritchey (3/7/2013)[/b][hr]The query engine will do what it does. That's in the internals. No way to know without a debugger or insider knowledge. It will use tempdb though.[/quote]Unless the data's small enough it can manage entirely within the memory grant. Not much chance of that except with trivial data.Thu, 07 Mar 2013 09:27:35 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1427851-392-1.aspxThe query engine will do what it does. That's in the internals. No way to know without a debugger or insider knowledge. It will use tempdb though.Thu, 07 Mar 2013 05:55:43 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1427851-392-1.aspx[quote][b]karthik M (3/7/2013)[/b][hr]I have a table with 5 rows.100523253445323No index has been created.If I use ORDER BY , which alogirthm is used to do this sort operation?523100232534453[/quote]Again, why? It does it and it does it quickly.Thu, 07 Mar 2013 05:54:14 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1427851-392-1.aspxI have a table with 5 rows.100523253445323No index has been created.If I use ORDER BY , which alogirthm is used to do this sort operation?523100232534453Thu, 07 Mar 2013 05:41:47 GMTkarthik Mhttp://www.sqlservercentral.com/Forums/Topic1427851-392-1.aspxWell the optimiser won't use any algorithm. The optimiser optimises queries, it doesn't run them.Why does it matter?Thu, 07 Mar 2013 02:40:07 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1427851-392-1.aspxHi All,When We sort the data by using ORDER BY , which sorting algorithm method will be used by SQL optimizer?1) Bubble Sort2) Quick Sort3) Merge Sort4) Heap Sort5) Insertion SortInputs are welcome!Thu, 07 Mar 2013 02:37:17 GMTkarthik M