Using RangeAB and RangeAB2

Introduction

This page is for reference only. These “range” functions are similar to the range in Python or Clojure but with a 4th parameter to set a zero-or-one base for the purposes of leveraging the virtual index. will be used often so I created this page for reference purposes. Check back often as this page will be updated frequently.

dbo.RangeAB sits on top of Jeff Moden’s dbo.fnTally. I use fnTally often and created dbo.RangeAB for a specific set of problems but found it helpful for enforcing some tally table best practices I’ve learned over the years.
dbo.RangeAB2 is the exact same function as dbo.RangeAB except that it uses a correctly indexed 1,000,000 row tally table. Both functions usually perform the same except that fnTally doesn’t require I/O. There will be times, on the other hand, where one is profoundly more efficient than the other. To find out if when one is better than the other you only need to add a “2” at the end of all references to dbo.RangeAB in your code.

Tally Table Setup

This is how I setup dbo.Tally on my system, it’s the DDL for the execution plans below. 1 Million rows will do for the examples below and generally enough for my needs but there are cases where I have gone higher to support dbo.rangeAB for some projects. 

--==== Create the table
IF OBJECT_ID('dbo.tally') IS NOT NULL DROP TABLE dbo.tally;
CREATE TABLE dbo.tally (N INT NOT NULL);

--==== Add Primary Key (I do it here so that I can name it)
ALTER TABLE dbo.tally 
  ADD CONSTRAINT pk_cl_tally PRIMARY KEY CLUSTERED(N) 
    WITH FILLFACTOR=100;

--==== Add a Unique Index (the optimizer will pick this one)
ALTER TABLE dbo.tally 
  ADD CONSTRAINT uq_tally UNIQUE NONCLUSTERED(N);

--==== Populate with at least 1 Million Rows
INSERT dbo.tally(N) SELECT t.N FROM dbo.fnTally(1,1000000) AS t;

The Functions

Below is the function logic for each. Note that BIGINT is used for implicit conversion related purposes. The complete function is attached for download, complete with comments, below each query.

dbo.RangeAB

DECLARE
  @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?

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);
dbo.rangeABDownload

dbo.RangeAB2

DECLARE
  @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?

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;
dbo.rangeAB2Download

Examples

EXAMPLE #1: Start N with 1, 1-base RN, no gaps

This is a basic example of how to count from 1 to 4, or 1 to 5.

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;
Results
Params   RN   OP   N
-------- ---- ---- ----
1,4,1,1  1    4    1
1,4,1,1  2    3    2
1,4,1,1  3    2    3
1,4,1,1  4    1    4

Params   RN   OP   N
-------- ---- ---- ----
1,5,1,1  1    5    1
1,5,1,1  2    4    2
1,5,1,1  3    3    3
1,5,1,1  4    2    4
1,5,1,1  5    1    5

EXAMPLE #2: Start N with 2, 1-base RN, no gaps

These queries will return the number 2-5 and 3-5 for N. The row number (RN) begins with 1.

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;
Results
Params  RN   OP   N
------- ---- ---- ----
2,5,1,1 1    4    2
2,5,1,1 2    3    3
2,5,1,1 3    2    4
2,5,1,1 4    1    5


Params  RN   OP   N
------- ---- ---- ----
2,6,1,1 1    5    2
2,6,1,1 2    4    3
2,6,1,1 3    3    4
2,6,1,1 4    2    5
2,6,1,1 5    1    6

EXAMPLE #3: Misc. range of numbers, 0-Base RN, no gaps

For N we want the numbers 0-3. 0-4, 1-4 and 1-5. RN will begin with 0. 

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;
Results
Params   RN    OP   N
-------- ----- ---- ---
0,3,1,0  0     3    0
0,3,1,0  1     2    1
0,3,1,0  2     1    2
0,3,1,0  3     0    3

Params   RN    OP   N
-------- ----- ---- ---
0,4,1,0  0     4    0
0,4,1,0  1     3    1
0,4,1,0  2     2    2
0,4,1,0  3     1    3
0,4,1,0  4     0    4

Params   RN    OP   N
-------- ----- ---- ---
1,4,1,0  0     3    1
1,4,1,0  1     2    2
1,4,1,0  2     1    3
1,4,1,0  3     0    4

Params   RN    OP   N
-------- ----- ---- ---
1,5,1,0  0     4    1
1,5,1,0  1     3    2
1,5,1,0  2     2    3
1,5,1,0  3     1    4
1,5,1,0  4     0    5

EXAMPLE #4: 1-4 & 1-5, Distance From Median (DM)

Here we are also returning the median value as 0 (DM=0) then all the other numbers, their position relative to the median value.

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;
Results
Params  RN   N    DM
------- ---- ---- ----
1,5,1,1 1    1    -2
1,5,1,1 2    2    -1
1,5,1,1 3    3    0
1,5,1,1 4    4    1
1,5,1,1 5    5    2

Params  RN   N    DM
------- ---- ---- ----
1,6,1,1 1    1    -2
1,6,1,1 2    2    -1
1,6,1,1 3    3    0
1,6,1,1 4    4    0
1,6,1,1 5    5    1
1,6,1,1 6    6    2

Note that when dealing with an even number of rows, the median will be represented as a set of two numbers; in the second example the median is represented as the numbers 3 & 4, respectively.

EXAMPLE #5: Leveraging the Gap Parameter (@gap)

Here I am setting @gap to 2, then to 3. Note the difference. The numbers will begin with @low and increase by the value of @gap.

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;
Results
Params   RN   N    DM
-------- ---- ---- ----
1,11,2,1 1    1    -4
1,11,2,1 2    3    -2
1,11,2,1 3    5    0
1,11,2,1 4    7    0
1,11,2,1 5    9    2
1,11,2,1 6    11   4

Params   RN   N    DM
-------- ---- ---- ----
1,13,3,1 1    1    -6
1,13,3,1 2    4    -3
1,13,3,1 3    7    0
1,13,3,1 4    10   3
1,13,3,1 5    13   6

EXAMPLE #6: Gaps & Finite Opposites of N (N_OP)

This is an example of how to get the N, with gaps, in descending order without a sort in the execution plan. 

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;
Results
Params      RN   RN_OP  DM    N     N_OP
----------- ---- ------ ----- ----- ------
0,100,25,1  1    5      -50   0     100
0,100,25,1  2    4      -25   25    75
0,100,25,1  3    3      0     50    50
0,100,25,1  4    2      25    75    25
0,100,25,1  5    1      50    100   0                            
                            
Params      RN   RN_OP  DM    N     N_OP
----------- ---- ------ ----- ----- ------
0,100,25,0  0    4      -50   0     100
0,100,25,0  1    3      -25   25    75
0,100,25,0  2    2      0     50    50
0,100,25,0  3    1      25    75    25
0,100,25,0  4    0      50    100   0

EXAMPLE #7: Using RN, OP and DM together

In these example we will include or exclude rows based on their distance from the middle of the week (“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 

--==== 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;
Results
DayNumber   DaysRemaining  MidWeekDistance
----------- -------------- -----------------
1           7              3
2           6              2
3           5              1
4           4              0
5           3              1
6           2              2
7           1              3

DayNumber   DaysRemaining  MidWeekDistance
----------- -------------- -----------------
1           7              3
2           6              2
6           2              2
7           1              3

DayNumber   DaysRemaining  MidWeekDistance
----------- -------------- -----------------
2           6              2
3           5              1
4           4              0
5           3              1
6           2              2

For generating an ordered list of numbers, without gaps, starting with 0 or 1, fnTally is normally the way to go. Sometimes a physical tally table is better. When adding gaps or to have a sort key and N column with different start values I write them with RangeAB or RangeAB2, whichever tests better. Below are the functions. Note that the comments may still need a bit of cleanup. Note, too, that @Row1 should be a BIGINT treated like a bit, I will make this change soon when I have time to test and review the impact.

Leave a Reply

%d bloggers like this: