CREATE OR ALTER FUNCTION dbo.rangeAB
(
  @Low  BIGINT, -- (start) Lowest  number in the set
  @High BIGINT, -- (stop)  Highest number in the set
  @Gap  BIGINT, -- (step)  Difference between each number in the set
  @Row1 BIT     -- Base: 0 or 1; should RN begin with 0 or 1?
)
/****************************************************************************************
[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). RangeAB is a pure, 100% set-based
 alternative to solving SQL problems using iterative methods such as loops, cursors and
 recursive CTEs. RangeAB is based on Itzik Ben-Gan's getnums function for producing a
 sequence of integers and uses logic from Jeff Moden's fnTally function which includes a
 parameter for determining if the "row-number" (RN) should begin with 0 or 1.
 
 I used the name "RangeAB" because it functions and performs almost identically to
 the Range function built into Python and Clojure. RANGE is a reserved SQL keyword so I 
 went with "RangeAB". Functions/Algorithms developed using rangeAB can be easilty ported
 over to Python, Clojure or any other programming language that leverages a lazy sequence.
 The two major differences between RangeAB and the Python/Clojure versions are:
   1. RangeAB is *Inclusive* where the other two are *Exclusive". range(0,3) in Python and
      Clojure return [0 1 2], core.rangeAB(0,3) returns [0 1 2 3].
   2. RangeAB has a fourth Parameter (@Row1) to determine if 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.rangeAB(@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

[Dependencies]:

[Developer Notes]:
 1.  core.rangeAB returns one billion rows in exactly 90 seconds on my laptop:
     4X 2.7GHz CPU's, 32 GB - multiple versions of SQL Server (2005-2019)       
 2.  The lowest and highest possible numbers returned are whatever is allowable by a 
     bigint. The function, however, returns no more than 531,441,000,000 rows (8100^3). 
 3.  @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.
 4.  @Gap must be greater than 0 or the function will not return any rows.
 5.  Keep in mind that when @Row1 is 0 then the highest RN value (ROWNUMBER) will be the 
     number of rows returned minus 1
 6.  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. 
 7.  Although @Gap is a bigint it must be a positive integer or the function will
     not return any rows.
 8.  The function will not return any rows when one of the following conditions are true:
       * any of the input parameters are NULL
       * @High is less than @Low 
       * @Gap is not greater than 0
     To force the function to return all NULLs instead of not returning anything you can
     add the following code to the end of the query:

       UNION ALL 
       SELECT NULL, NULL, NULL, NULL
       WHERE NOT (@High&@Low&@Gap&@Row1 IS NOT NULL AND @High >= @Low AND @Gap > 0)

     This code was excluded as it adds a ~5% performance penalty.
 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 core.rangeAB.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.rangeAB(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.rangeAB(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.rangeAB(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.rangeAB(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.rangeAB(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.rangeAB(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.rangeAB.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.rangeAB(-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.rangeAB(-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.rangeAB(-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.rangeAB(-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.rangeAB(-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.rangeAB(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.rangeAB(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.rangeAB(0,90,10,1) AS r;

-----------------------------------------------------------------------------------------
[Revision History]:
 Rev 00.0 - 20140518 - Initial Development - AJB
 Rev 01.0 - 20151029 - Added 65 rows. Now L1=465; 465^3=100.5M. Updated comments - AJB
 Rev 02.0 - 20180613 - Complete re-design including opposite number column (op)
 Rev 03.0 - 20180920 - Added additional CROSS JOIN to L2 for 530B rows max - AJB
 Rev 04.0 - 20200329 - 1. Removed startup predicate that dicatated that:
                          @High >= @Low AND @Gap > 0 AND @Row1 = @Row1.
                          That means that this must be handled outside the function 
                       2. Removed the ISNULL Logic from the TOP statement. This will make
                          errors possible if you provide bad parameters. 
                       3. Now using core.rowz() to build the rows - AJB
 Rev 05.0 - 20201001 - Redesigned leveraging fnTally by Jeff Moden
*****************************************************************************************/
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
  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.fnTally(@row1,(@high-@low)/@gap+@row1) AS f
CROSS APPLY (VALUES((@high-@low)/@gap+(2*@row1)-f.N))  AS o(OP);
GO

--==== EXAMPLE #1: N begins with 1, no gaps, 1-Base RN';
;-----------------------------------------------------------------------------------------
SELECT Params='1,4,1,1', r.RN, r.OP, N=r.N1 FROM dbo.rangeAB(1,4,1,1) AS r;
SELECT Params='1,5,1,1', r.RN, r.OP, N=r.N1 FROM dbo.rangeAB(1,5,1,1) AS r;

--==== EXAMPLE #2: N begins with 2, no gaps, 1-Base RN';
;-----------------------------------------------------------------------------------------
SELECT Params='2,5,1,1', r.RN, r.OP, N=r.N1 FROM dbo.rangeAB(2,5,1,1) AS r;
SELECT Params='2,6,1,1', r.RN, r.OP, N=r.N1 FROM dbo.rangeAB(2,6,1,1) AS r;

--==== EXAMPLE #3: Misc ranges of (0|1) to (3|4|5)0-Base, 0-Base RN, no gaps;
;-----------------------------------------------------------------------------------------
SELECT Params='0,3,1,0', r.RN, r.OP, N=r.N1 FROM dbo.rangeAB(0,3,1,0) AS r;
SELECT Params='0,4,1,0', r.RN, r.OP, N=r.N1 FROM dbo.rangeAB(0,4,1,0) AS r;
SELECT Params='1,4,1,0', r.RN, r.OP, N=r.N1 FROM dbo.rangeAB(1,4,1,0) AS r;
SELECT Params='1,5,1,0', r.RN, r.OP, N=r.N1 FROM dbo.rangeAB(1,5,1,0) AS r;

--==== EXAMPLE #4: 1-4 & 1-5, Distance From Median (DM);
;-----------------------------------------------------------------------------------------
SELECT Params='1,5,1,1', r.RN, N=r.N1, DM=r.DM FROM dbo.rangeAB(1,5,1,1) AS r;
SELECT Params='1,6,1,1', r.RN, N=r.N1, DM=r.DM FROM dbo.rangeAB(1,6,1,1) AS r;

--==== EXAMPLE #5: Add Gaps;
;-----------------------------------------------------------------------------------------
SELECT Params='1,11,2,1',  r.RN, N=r.N1, DM=r.DM FROM dbo.rangeAB(1,11,2,1) AS r;
SELECT Params='1,13,3,1', r.RN, N=r.N1, DM=r.DM  FROM dbo.rangeAB(1,13,3,1) AS r;

--==== EXAMPLE #6: Add Gaps & Finite Opposites of N (N_OP), note the ORDER BY, no sort;
;-----------------------------------------------------------------------------------------
DECLARE @gap BIGINT = 25

  --== Method 1: 1-Base, OP = r.OP*@gap-@gap
  SELECT
    Params = '0,100,25,1',
    RN     = r.RN,
    RN_OP  = r.OP,
    DM     = r.DM,
    N      = r.N1,
    N_OP   = n.OP
  FROM        dbo.rangeAB(0,100,@gap,1) AS r
  CROSS APPLY (VALUES(r.OP*@gap-@gap))  AS n(OP)
  ORDER BY    r.RN;

  --== Method 2: 0-Base, OP = r.OP*@gap
  SELECT
    Params = '0,100,25,0',
    RN     = r.RN,
    RN_OP  = r.OP,
    DM     = r.DM,
    N      = r.N1,
    N_OP   = n.OP
  FROM        dbo.rangeAB(0,100,@gap,0) AS r
  CROSS APPLY (VALUES(r.OP*@gap))       AS n(OP)
  ORDER BY    r.RN;

--==== EXAMPLE #7: RN,OP and DM together to measure distance from mid-week;
;-----------------------------------------------------------------------------------------
DECLARE @days BIGINT = 7;

  --==== Return All the days
  SELECT DayNumber       = r.RN,
         DaysRemaining   = r.OP,
         MidWeekDistance = ab.DM
  FROM        dbo.rangeAB(1,@days,1,1) AS r
  CROSS APPLY (VALUES(ABS(r.DM)))      AS ab(DM);
GO 

--==== EXAMPLE #8: Using DM to exclude "middle rows" based on distance from median(DM);
;-----------------------------------------------------------------------------------------
  DECLARE @days BIGINT = 7, @DM BIGINT = 2;
  
  --==== Exclude rows with a @DM >= MidWeekDistance <= @DM
  SELECT DayNumber       = r.RN,
         DaysRemaining   = r.OP,
         MidWeekDistance = ab.DM
  FROM        dbo.rangeAB(1,@days,1,1) AS r
  CROSS APPLY (VALUES(ABS(r.DM)))      AS ab(DM)
  WHERE       ab.DM >= @DM;

  --==== Only include rows with a MidWeekDistance <= @DM 
  SELECT DayNumber       = r.RN,
         DaysRemaining   = r.OP,
         MidWeekDistance = ab.DM
  FROM        dbo.rangeAB(1,@days,1,1) AS r
  CROSS APPLY (VALUES(ABS(r.DM)))      AS ab(DM)
  WHERE       ab.DM <= @DM; -- Excludes 

--==== EXAMPLE #9: Another DM example, return list of weeks with "Days Until Midterm"
;-----------------------------------------------------------------------------------------
DECLARE @weeks     BIGINT = 7,
        @startdate DATE   = GETDATE(),
		@incWkends BIT    = 1;

SELECT
  WeekNbr          = r.RN,                           -- Starts @ 1(1-7) (4th Param)
  WeekStart        = DATEADD(DAY,r.N1,@startdate),   -- Starts @ 0(0-42: @weeks*(7-1))
  WeekEnd          = DATEADD(DAY,r.N1+CHOOSE(@incWkends+1,4,6),@startdate), -- End Date
  DaysUntilMidterm = -r.DM
FROM   dbo.rangeAB(0,@weeks*(7-1),7,1) AS r;








DECLARE @String VARCHAR(8000) = 'ABC123XYZ'

SELECT
  TokenSize    = r.RN,
  TotalExtract = STRING_AGG(ng.Token,'') WITHIN GROUP (ORDER BY r.RN)
FROM        dbo.rangeAB(1,LEN(@String),1,1) AS r
CROSS APPLY samd.ngrams8k(@String,r.RN)     AS ng
GROUP BY    r.RN

SELECT
  TokenSize    = r.RN,
  TotalExtract = STRING_AGG(ng.Token,'') WITHIN GROUP (ORDER BY r.RN)
FROM        dbo.rangeAB2(1,LEN(@String),1,1) AS r
CROSS APPLY samd.ngrams8k(@String,r.RN)     AS ng
GROUP BY    r.RN
GO

--==== Distance From Median (No Sort)
-- "ORDER BY [DISTANCE FROM MEDIAN]"
DECLARE
  @string   VARCHAR(8000) = 'ABC123XYZ!',
  @workRank BIGINT = 2;

SELECT
  TokenSize    = r.RN,
  TotalExtract = STRING_AGG(ng.Token,''), 
  WorkRanking  = ABS(r.DM),
  CharCount    = LEN(STRING_AGG(ng.Token,'')),
  LogicalOps   = r.RN*r.OP
FROM        dbo.rangeAB(1,LEN(@String),1,1)  AS r
CROSS APPLY samd.ngrams8k(@String,r.RN)      AS ng
GROUP BY    r.RN, r.OP, r.DM
HAVING      ABS(r.DM) >= @workRank;

SELECT
  TokenSize    = r.RN,
  TotalExtract = STRING_AGG(ng.Token,''), 
  WorkRanking  = ABS(r.DM),
  CharCount    = LEN(STRING_AGG(ng.Token,'')),
  LogicalOps   = r.RN*r.OP
FROM        dbo.rangeAB2(1,LEN(@String),1,1) AS r
CROSS APPLY samd.ngrams8k(@String,r.RN)      AS ng
GROUP BY    r.RN, r.OP, r.DM
HAVING      ABS(r.DM) >= @workRank;
GO

--==== NO SORT ALTERNATIVE
DECLARE
  @string   VARCHAR(8000) = 'ABC123XYZ!',
  @workRank BIGINT = 2;

SELECT
  TokenSize   = r.RN,
  WorkRanking = ABS((r.RN-(LEN(@String)+1-r.RN))/2),
  LogicalOps  = (LEN(@String)+1-r.RN)*r.RN
FROM        dbo.rangeAB(1,LEN(@String),1,1) AS r
CROSS APPLY samd.ngrams8k(@String,r.RN)     AS ng
WHERE       ABS(r.DM) >= @workRank
GROUP BY    r.RN;

SELECT
  TokenSize   = r.RN,
  WorkRanking = ABS((r.RN-(LEN(@String)+1-r.RN))/2),
  LogicalOps  = (LEN(@String)+1-r.RN)*r.RN
FROM        dbo.rangeAB2(1,LEN(@String),1,1) AS r
CROSS APPLY samd.ngrams8k(@String,r.RN)     AS ng
WHERE       ABS(r.DM) >= @workRank
GROUP BY    r.RN;
GO