Counting

Question

Counting

Steve Jones - SSC Editor

SSC Guru

Points: 734449
More actions
March 23, 2016 at 11:14 am

#326014

Comments posted to this topic are about the item Counting

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61136 More actions · Answer 1

This is my "get numbers" or "fntally" function which has served me well.

I started with what I've learned from Jeff Moden including the 0 or 1 start functionality. Then added some the type of functionality Ben-Gan's getnumbers function from his books where you define the start and end number. Then I added my own functionality twist to the functionality:

1. The function increases the numbers by the a third parameter (@gap).

When @gap is 1, the numbers increase by 1: 1,2,3,4,5... When gap is, say, 10 then the numbers increase by 10: 10, 20, 30, etc... What's key is that we're only generating enough rows to satisfy the query request. I've seen people get the numbers 10,20...100 like this:

SELECT N FROM Tally WHERE N <=100 AND N%10=0; This is a bad because 100 rows are generated then filtered whereas GetNumsAB generates 10 rows to get the desired results.

2. Changed the logic to only require two Cross Joins to get up to 100,544,625 rows.

This makes the code a little more obnoxious but the query plan is much easier on the eyes.

3. The function includes a row number at no extra cost

This means that, using the 10,20...100 example, you also have a row number (rn) available and without any extra effort. Note example 4 in the comments.

I include how to get optimal performance in my comments.

CREATE FUNCTION dbo.GetNumsAB

(

@low bigint,

@high bigint,

@gap bigint,

@row1 bit

)

/****************************************************************************************

Purpose:

Creates up to 100,544,625 sequential numbers beginning with @low and ending with @high.

Used to replace iterative methods such as loops, cursors and recursive CTEs to solve SQL

problems. Based on Itzik Ben-Gan's getnums function with some tweeks and enhancements

and added functionality. The logic for getting rn to beginat 0 or 1 is based comes from

Jeff Moden's fnTally function.

Compatibility:

SQL Server 2008+ and Azure SQL Database

Syntax:

SELECT rn, n1, n2 FROM dbo.getnumsAB(@low,@high,@gap,@row1);

Parameters:

@low = a bigint that represents the lowest value for n1.

@high = a bigint that represents the highest value for n1.

@gap = a bigint that represents how much n1 and n2 will increase each row; @gap also

represents the difference between n1 and n2.

@row1 = a bit that represents the first value of rn. When @row = 0 then rn begins

at 0, when @row = 1 then rn will begin at 1.

Return Types:

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.

n1 = bigint; a sequential number starting at the value of @low and incrimentingby the

value of @gap until it is less than or equal to the value of @high.

n2 = bigint; a sequential number starting at the value of @low+@gap and incrimenting

by the value of @gap.

Developer Notes:

1. The lowest and highest possible numbers returned are whatever is allowable by a

bigint. The function, however, returns no more than 100,544,625 rows (465^3).

2. @gap does not affect rn, rn will begin at @row1 and increase by 1 until the last row

unless its used in a query 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 row-number 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.

7. @high must be equal or higher than @low or the function won't return any rows.

8. There is no performance penalty for sorting by rn ASC; there is a large performance

penalty for sorting in descending order. To sort in descending order, first make sure

that @gap = 1. Next create an alias column (called "n_desc" in the example below) and

use the formula: n_desc = ABS(rn-(@high+1)). The example below will return the numbers

1 to 10 in descending order:

DECLARE @low int = 1, @high int = 10, @gap int = 1, @row1 bit = 1;

SELECT n_desc = ABS(rn-(@high+(@row1)))

FROM dbo.getnumsAB(@low,@high,1,@ro1)

ORDER BY rn;

Examples:

--===== 1. Using RN (rownumber)

-- (1.1) The best way to get the numbers 1,2,3...@high (e.g. 1 to 5):

SELECT RN FROM dbo.getnumsAB(1,5,1,1);

-- (1.2) The best way to get the numbers 0,1,2...@high-1 (e.g. 0 to 5):

SELECT RN FROM dbo.getnumsAB(0,5,1,0);

--===== 2. Using N1

-- (2.1) To begin with numbers other than 0 or 1 use N1 (e.g. -3 to 3):

SELECT N1 FROM dbo.getnumsAB(-3,3,1,1);

-- (2.2) ROW_NUMBER() is built in. If you want a ROW_NUMBER() include RN:

SELECT RN, N1 FROM dbo.getnumsAB(-3,3,1,1);

-- (2.3) If you wanted a ROW_NUMBER() that started at 0 you would do this:

SELECT RN, N1 FROM dbo.getnumsAB(-3,3,1,0);

--===== 3. Using N2 and @gap

-- (3.1) To get 0,10,20,30...100, set @low to 0, @high to 100 and @gap to 10:

SELECT N1 FROM dbo.getnumsAB(0,100,10,1);

-- (3.2) 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 N1, N2 FROM dbo.getnumsAB(0,90,10,1);

-- (3.3) Remember that a rownumber is included and it can begin at 0 or 1:

SELECT RN, N1, N2 FROM dbo.getnumsAB(0,90,10,1);

--===== (4) A real life example using RN, N1 and N2:

-- Beginning with @StartDate, to generate ranges of weeks that occur between

-- @startDate & @EndDate:

DECLARE @StartDate datetime = '1/1/2015', @EndDate datetime = '2/28/2015';

SELECT

WeekNbr = 'Week #'+CAST(RN AS varchar(2)),

WeekStart = CONVERT(DATE, DATEADD(DAY,N1,@StartDate)),

WeekEnd = CONVERT(DATE, DATEADD(DAY,N2-1,@StartDate))

FROM dbo.getnumsAB(0,datediff(DAY,@StartDate,@EndDate),7,1);

---------------------------------------------------------------------------------------

Revision History:

Rev 00 - 20140518 - Initial Development - Alan Burstein

Rev 01 - 20151029 - Added 65 rows to make L1=465; 465^3=100.5M. Updated comment section

- Alan Burstein

****************************************************************************************/

RETURNS TABLE WITH SCHEMABINDING AS RETURN

WITH

L1(N) AS

(

SELECT 1

FROM (VALUES

(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),

(NULL),(NULL),(NULL)) t(N) -- 465 values

),

iTally AS

(

SELECT rn = 0 WHERE @row1 = 0

UNION ALL

SELECT rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM L1 a CROSS APPLY L1 b CROSS APPLY L1 c

) --Up to 100,544,625 numbers (select 465*465*465 = 100,544,625)

SELECT TOP (ABS((@high-@low)/ISNULL(NULLIF(@gap,0),1)+1))

rn,

n1 = ((rn-( @row1 ))*@gap+@low),

n2 = ((rn-(@row1-1))*@gap+@low)

FROM iTally

WHERE @high >= @low and @gap > 0

ORDER BY rn;

Perhaps this is worthy of a script submission 😉

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 2

akljfhnlaflkj

SSC Guru

Points: 76202

April 4, 2016 at 6:58 am

#1869500

Thanks for the script.

mailtoashokberwal SSC Journeyman Points: 95 More actions · Answer 3

Thanks 🙂

I tried another approach with CTE and that works fine too.

WITH cte(a)

AS

(

SELECT 1

UNION ALL

SELECT a+1 FROM cte WHERE a<10000

)

SELECT * FROM cte OPTION(MAXRECURSION 10000)

Alan Burstein SSC Guru Points: 61136 More actions · Answer 4

mailtoashokberwal (4/25/2016)
Thanks 🙂
I tried another approach with CTE and that works fine too.
WITH cte(a)
AS
(
SELECT 1
UNION ALL
SELECT a+1 FROM cte WHERE a<10000
)
SELECT * FROM cte OPTION(MAXRECURSION 10000)

That's going to be much, much slower. Jeff Moden wrote a great article about this technique. http://www.sqlservercentral.com/articles/T-SQL/74118/[/url]

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001