-- 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