-- THIS IS THE OFFICIAL GUY!
IF OBJECT_ID('dbo.rangeAB2','IF') IS NOT NULL DROP FUNCTION dbo.rangeAB2;
GO
CREATE FUNCTION dbo.rangeAB2
(
  @low  BIGINT,
  @high BIGINT,
  @gap  BIGINT,
  @row1 BIGINT
)
/****************************************************************************************
[Purpose]:
 Creates a lazy, in-memory, forward-ordered sequence of up to 1,073,741,824 integers
 beginning with @Low and ending with @High (inclusive). dbo.rangeAB2 is a pure, 100% set-
 based alternative to solving SQL problems using iterative methods such as loops, cursors 
 and recursive CTEs. rangeAB2 is based on RangeAB, which leverages Itzik Ben-Gan's CTE-
 style tally table and logic from Jeff Moden's fnTally function which includes a parameter
 for determining if the "row-number" (RN) should begin with 0 or 1.
 
[Author]:
 Alan Burstein

[Compatibility]: 
 SQL Server 2008+

[Syntax]:
 SELECT r.RN, r.OP, r.N1, r.N2
 FROM   core.rangeAB2(@Low,@High,@Gap,@Row1) AS r;

[Parameters]:
 @Low  = BIGINT; represents the lowest  value for N1.
 @High = BIGINT; represents the highest value for N1.
 @Gap  = BIGINT; represents how much N1 and N2 will increase each row. @Gap is also the 
                 difference between N1 and N2.
 @Row1 = BIT;    represents the base (first) value of RN. When @Row1 = 0, RN begins with 0,
                 when @row = 1 then RN begins with 1.
 
[Returns]:
 Inline Table Valued Function returns:
 RN = BIGINT; a row number that works just like T-SQL ROW_NUMBER() except that it can 
      start at 0 or 1 which is dictated by @Row1. If you need the numbers: 
      (0 or 1) through @High, then use RN as your "N" value, ((@Row1=0 for 0, @Row1=1),
      otherwise use N1.
 OP = BIGINT; returns the "finite opposite" of RN. When RN begins with 0 the first number 
      in the set will be 0 for RN, the last number in will be 0 for OP. When returning the
      numbers 1 to 10, 1 to 10 is retrurned in ascending order for RN and in descending 
      order for OP.
      Given the Numbers 1 to 3, 3 is the opposite of 1, 2 the opposite of 2, and 1 is the
      opposite of 3. Given the numbers -1 to 2, the opposite of -1 is 2, the opposite of 0
      is 1, and the opposite of 1 is 0.
      The best practie is to only use OP when @Gap > 1; use core.O instead. Doing so will
      improve performance by 1-2% (not huge but every little bit counts)      
 N1 = BIGINT; This is the "N" in your tally table/numbers function. this is your *Lazy* 
      sequence of numbers starting at @Low and incrimenting by @Gap until the next number
      in the sequence is greater than @High.
 N2 = BIGINT; a lazy sequence of numbers starting @Low+@Gap and incrimenting by @Gap. N2
      will always be greater than N1 by @Gap. N2 can also be thought of as:
      LEAD(N1,1,N1+@Gap) OVER (ORDER BY RN)
 DM = BIGINT Distance From the median rownumber

[Developer Notes]:
 1.  rangeAB2 leverages a one million row tally table; if you might need more than one-
     million numbers (or numeric values)
 2.  @Gap does not affect RN, RN will begin at @Row1 and increase by 1 until the last row
     unless its used in a subquery where a filter is applied to RN.
 3.  @Gap must be greater than 0 or the function will not return any rows.
 4.  Keep in mind that when @Row1 is 0 then the highest RN value (ROWNUMBER) will be the 
     number of rows returned minus 1
 5.  If you only need is a sequential set beginning at 0 or 1 then, for best performance
     use the RN column. Use N1 and/or N2 when you need to begin your sequence at any 
     number other than 0 or 1 or if you need a gap between your sequence of numbers. 
 6.  Although @Gap is a bigint it must be a positive integer or the function will
     not return any rows.
 9.  There is no performance penalty for sorting by RN ASC; there is a large performance 
     penalty, however for sorting in descending order. If you need a descending sort the
     use OP in place of RN then sort by rn ASC. 
 10. When setting the @Row1 to 0 and sorting by RN you will see that the 0 is added via
     MERGE JOIN concatination. Under the hood the function is essentially concatinating
     but, because it's using a MERGE JOIN operator instead of concatination the cost 
     estimations are needlessly high. You can circumvent this problem by changing:
     ORDER BY rangeAB2.RN to: ORDER BY ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

[Examples]:
*** The Examples below are the best practices for using this function ***

--===== 1. Using RN (rownumber)
 -- (1.1) The best way to get the numbers 1,2,3...@High (e.g. 1 to 5):
 SELECT r.RN
 FROM   core.rangeAB2(1,5,1,1) AS r;

 -- (1.2) The best way to get the numbers 0,1,2...@High (e.g. 0 to 5):
 SELECT r.RN
 FROM   core.rangeAB2(0,5,1,0) AS r;

--===== 2. Using OP for descending sorts without a performance penalty
 -- (2.1) Best Practice for getting the numbers 5,4,3,2,1 (5 to 1):
 SELECT   r.OP
 FROM     core.rangeAB2(1,5,1,1) AS r 
 ORDER BY R.RN;

 -- (2.2) Best Practice for getting the numbers 5,4,3,2,1,0 (5 to 0):
 SELECT   r.OP 
 FROM     core.rangeAB2(0,5,1,0) AS r
 ORDER BY r.RN ASC;

 -- (2.3) (ADVANCED) - Ex 2.2. (above) but with better query plan estimations (compare both)
 SELECT   r.OP 
 FROM     core.rangeAB2(0,5,1,0) AS r
 ORDER BY ROW_NUMBER() OVER (ORDER BY (SELECT NULL));
 -- This will leverage concatination operator instead of a merge join union;
 -- This will not improve performance but the exection plan will include better estimations
;
 -- (2.4) (ADVANCED) The BEST way (leveraging core.O)
 SELECT      o.OP
 FROM        core.rangeAB2(0,5,1,0) AS r
 CROSS APPLY core.O(0,5,r.RN)      AS o
 ORDER BY    ROW_NUMBER() OVER (ORDER BY (SELECT NULL));
 -- Note that core.rangeAB2.Op is best when there are gaps (@Gap > 1)

--===== 3. Using N1
 -- (3.1) To begin with numbers other than 0 or 1 use N1 (e.g. -3 to 3):
 SELECT r.N1
 FROM   core.rangeAB2(-3,3,1,1) AS r;

 -- (3.2) ROW_NUMBER() is built in. If you want a ROW_NUMBER() include RN:
 SELECT r.RN, r.N1
 FROM   core.rangeAB2(-3,3,1,1) AS r;

 -- (3.3) If you wanted a ROW_NUMBER() that started at 0 you would do this:
 SELECT r.RN, r.N1
 FROM   core.rangeAB2(-3,3,1,0) AS r;

 -- (3.4) Ex 3.3. Guaranteed ORDER BY without a sort in the execution plan
 SELECT   r.RN, r.N1
 FROM     core.rangeAB2(-3,3,1,0) AS r
 ORDER BY r.RN;

 -- (3.5) Ex 3.4. But with better cost estimations (similar to ex 2.4)
 SELECT   r.RN, r.N1
 FROM     core.rangeAB2(-3,3,1,0) AS r
 ORDER BY    ROW_NUMBER() OVER (ORDER BY (SELECT NULL));

--===== 4. Using N2 and @Gap
 -- (4.1) To get 0,10,20,30...100, set @Low to 0, @High to 100 and @Gap to 10:
 SELECT r.N1
 FROM   core.rangeAB2(0,100,10,1) AS r;

 -- (4.2) Adding N2
   -- Note that N2=N1+@Gap; this allows you to create a sequence of ranges.
   -- For example, to get (0,10),(10,20),(20,30).... (90,100):
 SELECT r.N1, r.N2
 FROM  core.rangeAB2(0,90,10,1) AS r;

 -- (4.3) Remember that a rownumber is included and it can begin at 0 or 1:
 SELECT r.RN, r.N1, r.N2
 FROM   core.rangeAB2(0,90,10,1) AS r;

-----------------------------------------------------------------------------------------
[Revision History]:
 Rev 05.0 - 20201206 - Created - AJB
*****************************************************************************************/
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
  RN = 0,
  OP =  (@High-@Low)/@Gap,
  DM = -(@High-@Low)/2*@Gap,
  N1 = @Low,
  N2 = @Gap+@Low
WHERE @Row1 = 0
UNION ALL
SELECT TOP((@high-@low)/@gap+@row1)
  RN = f.N,
  OP = o.OP,
  DM = (f.N-o.OP)/2*@gap,
  N1 = (f.N-@row1)*@gap+@low,
  N2 = (f.N-(@row1-1))*@gap+@low
FROM     dbo.tally AS f
CROSS APPLY (VALUES((@high-@low)/@gap+(2*@row1)-f.N))  AS o(OP)
ORDER BY RN;
GO