SQL Server Join In Order

Question

I have 2 string in input for example '1,5,6' and '2,89,9' with same number of element (3 or plus). Those 2 string i want made a "ordinate join" as

1   2
5   89
6   9

i have think to assign a rownumber and made a join between 2 result set as

SELECT a.item, b.item  FROM 
  (
  SELECT  
  ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS rownumber,
  *  FROM dbo.Split('1,5,6',',')
  ) AS a
  INNER JOIN   
  (
  SELECT  
  ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS rownumber,
  *  FROM dbo.Split('2,89,9',',')
 ) AS b ON a.rownumber = b.rownumber 

is that a best practice ever?

Answer 1

When dbo.Split() returns the data-set, nothing you do can assign the row_number you want (based on their order in the string) with absolute certainty. SQL never guarantees an ordering without an ORDER BY that actually relates to the data.

With you trick of using (SELECT 0) to order by you may often get the right values. Probably very often. But this is never guaranteed. Once in a while you will get the wrong order.

Your best option is to recode dbo.Split() to assign a row_number as the string is parsed. Only then can you know with 100% certainty that the row_number really does correspond to the item's position in the list.

Then you join them as you suggest, and get the results you want.

Other than that, the idea does seem fine to me. Though you may wish to consider a FULL OUTER JOIN if one list can be longer than the other.