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