How sort works on generic column, what is behind the scene

• mario17

  SSCertifiable

  Points: 6079

  More actions

  May 19, 2014 at 9:52 am

  #291490

  Hi,

  I have an old code which do sort on very generic column like below, so I think it's not deterministic and should be replaced, but what is actual order after I SORT it on Rating, does it internally sorted on column 1,2,4, or there is some other logic, this is temp table from interim result from sp, no any idx exist,

  I tried to catch what is the actual order but looks like it's no any logic, it driven by some internals things.

  Thanks

  mario

  ;WITH t AS (

  ;WITH t AS (

  Select '11180' CustID, 'Alpha' Name ,'HARRIS@mail.com' Email, '55038-7316' zip , 'RateAAA'Rating union

  Select '34921' CustID, 'Bravo' Name ,'Jefferson@mail.com' Email, '32789-4824' zip , 'RateAAA'Rating union

  Select '92160' CustID, 'Charlie' Name ,'MARION@mail.com' Email, '94566-3567' zip , 'RateAAA'Rating union

  Select '95569' CustID, 'Delta' Name ,'BOULDER@mail.com' Email, '72205-3903' zip , 'RateAAA'Rating union

  Select '105380' CustID, 'Echo' Name ,'MONTGOMERY@mail.com' Email, '92118-3130' zip , 'RateAAA'Rating union

  Select '112878' CustID, 'foxtrcot' Name ,'SPARTANBURG@mail.com' Email, '72223-5132' zip , 'RateAAA'Rating union

  Select '120422' CustID, 'golf' Name ,'DAVIDSON@mail.com' Email, '07040-2133' zip , 'RateAAA'Rating union

  Select '77777' CustID, 'whiskey' Name ,'WASHINGTON111@mail.com' Email, '39110-30' zip , 'RateAAA'Rating UNION

  Select '77777' CustID, 'mike' Name ,'WASHINGTON444@mail.com' Email, '39110-20' zip , 'RateAAA'Rating UNION

  Select '77777' CustID, 'Alpha777' Name ,'WASHINGTON333@mail.com' Email, '39110-10' zip , 'RateAAA'Rating union

  Select '160815' CustID, 'india' Name ,'ORANGE@mail.com' Email, '79556-3412' zip , 'RateAAA'Rating union

  Select '161282' CustID, 'juliett' Name ,'Xray@mail.com' Email, '77777' zip , 'RateB'Rating )

  SELECT * FROM t ORDER BY Rating

• Koen Verbeeck

  SSC Guru

  Points: 259115

  More actions

  May 19, 2014 at 10:05 am

  #1714703

  The final sort order has something to do with all the UNIONs. Since UNION filters out duplicates, merge joins are used in the execution plan.

  This has some impact on the final output.

  If you use UNION ALL, there are no merge joins and the sort order is according to the order when they are defined in the CTE.

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP

• mario17

  SSCertifiable

  Points: 6079

  More actions

  May 19, 2014 at 10:12 am

  #1714708

  Thanks much Koen,

  So looks like it not deterministic, I think our biz should take care of it.

  M

• Koen Verbeeck

  SSC Guru

  Points: 259115

  More actions

  May 19, 2014 at 10:17 am

  #1714710

  mario17 (5/19/2014)

  ---

  Thanks much Koen,

  So looks like it not deterministic, I think our biz should take care of it.

  M

  The only thing you can rely on is that the output will be sorted on Rating.

  Anything else is dependent on physical implementation and can be considered as coincidence.

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP