Heh... listen to you. Did you or did you not start this post off with the following?

Why do people always say "performance doesn't really matter" as soon as they have to learn something new? And, trust me, unless you're making up these ranges yourself, you have no control over them and there may be a day when you get a load with many large ranges. That's when you'll wish you did it right the first time. Performance ALWAYS matters just as much as getting correct answers from your code.

Speaking of "correct", your test data has negative ranges. Was that accidental or is that the way you're really getting data? Here's the code that proves it. Look for the negative RANGEs.

-- sample date

DECLARE @rng-2 TABLE (Id INT, RangeStart BIGINT, RangeEnd BIGINT, Category INT, NumberCount INT)

;

INSERT INTO @rng-2

SELECT 123, 120000006660, 120000006690, 5, 31 UNION ALL

SELECT 123, 120000011660, 120000011670, 5, 11 UNION ALL

SELECT 123, 199000011660, 199000011670, 10, 11 UNION ALL

SELECT 789, 88800001060, 88800001090, 5, 31 UNION ALL

SELECT 789, 99900011660, 99000011675, 10, 16 UNION ALL

SELECT 789, 10000011560, 10000011670, 15, 111 UNION ALL

SELECT 258, 599900010660, 599000010685, 10, 26 UNION ALL

SELECT 258, 510000010560, 510000010565, 10, 6

;

SELECT RANGE = d.RangeEnd-d.RangeStart+1,* FROM @rng-2 d;

Assuming that you're not supposed to get negative ranges, here's the corrected test data with 3 larger ranges included for a total of 3,000,243 rows.

-- sample date

DECLARE @rng-2 TABLE (Id INT, RangeStart BIGINT, RangeEnd BIGINT, Category INT, NumberCount INT)

;

INSERT INTO @rng-2

SELECT 123, 120000006660, 120000006690, 5, 31 UNION ALL

SELECT 123, 120000011660, 120000011670, 5, 11 UNION ALL

SELECT 123, 199000011660, 199000011670, 10, 11 UNION ALL

SELECT 789, 88800001060, 88800001090, 5, 31 UNION ALL

SELECT 789, 99900011660, 99900011675, 10, 16 UNION ALL

SELECT 789, 10000011560, 10000011670, 15, 111 UNION ALL

SELECT 258, 599900010660, 599900010685, 10, 26 UNION ALL

SELECT 258, 510000010560, 510000010565, 10, 6 UNION ALL

SELECT 998, 210000000000, 210000999999, 1, 99 UNION ALL

SELECT 998, 210001000000, 210001999999, 2, 99 UNION ALL

SELECT 998, 210002000000, 210002999999, 3, 99

;

--===== Show the size of the ranges, just because...

SELECT RANGE = d.RangeEnd-d.RangeStart+1,* FROM @rng-2 d;

Let's get "Efficient". You're going to need it for other things in the future so you might as well just go ahead and install the following iTVF.

CREATE FUNCTION [dbo].[fnTally]

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

Purpose:

Return a column of BIGINTs from @ZeroOrOne up to and including @MaxN with a max value of 1 Trillion.

As a performance note, it takes about 00:02:10 (hh:mm:ss) to generate 1 Billion numbers to a throw-away variable.

Usage:

--===== Syntax example (Returns BIGINT)

SELECT t.N

FROM dbo.fnTally(@ZeroOrOne,@MaxN) t

;

Notes:

1. Based on Itzik Ben-Gan's cascading CTE (cCTE) method for creating a "readless" Tally Table source of BIGINTs.

Refer to the following URLs for how it works and introduction for how it replaces certain loops.

http://www.sqlservercentral.com/articles/T-SQL/62867/

http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers

2. To start a sequence at 0, @ZeroOrOne must be 0 or NULL. Any other value that's convertable to the BIT data-type

will cause the sequence to start at 1.

3. If @ZeroOrOne = 1 and @MaxN = 0, no rows will be returned.

5. If @MaxN is negative or NULL, a "TOP" error will be returned.

6. @MaxN must be a positive number from >= the value of @ZeroOrOne up to and including 1 Billion. If a larger

number is used, the function will silently truncate after 1 Billion. If you actually need a sequence with

that many values, you should consider using a different tool. ;-)

7. There will be a substantial reduction in performance if "N" is sorted in descending order. If a descending

sort is required, use code similar to the following. Performance will decrease by about 27% but it's still

very fast especially compared with just doing a simple descending sort on "N", which is about 20 times slower.

If @ZeroOrOne is a 0, in this case, remove the "+1" from the code.

DECLARE @MaxN BIGINT;

SELECT @MaxN = 1000;

SELECT DescendingN = @MaxN-N+1

FROM dbo.fnTally(1,@MaxN);

8. There is no performance penalty for sorting "N" in ascending order because the output is explicity sorted by

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

Revision History:

Rev 00 - Unknown - Jeff Moden

- Initial creation with error handling for @MaxN.

Rev 01 - 09 Feb 2013 - Jeff Moden

- Modified to start at 0 or 1.

Rev 02 - 16 May 2013 - Jeff Moden

- Removed error handling for @MaxN because of exceptional cases.

Rev 03 - 22 Apr 2015 - Jeff Moden

- Modify to handle 1 Trillion rows for experimental purposes.

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

(@ZeroOrOne BIT, @MaxN BIGINT)

RETURNS TABLE WITH SCHEMABINDING AS

RETURN WITH

E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1) --10E1 or 10 rows

, E4(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d) --10E4 or 10 Thousand rows

,E12(N) AS (SELECT 1 FROM E4 a, E4 b, E4 c) --10E12 or 1 Trillion rows

SELECT N = 0 WHERE ISNULL(@ZeroOrOne,0)= 0 --Conditionally start at 0.

UNION ALL

SELECT TOP(@MaxN) N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E12 -- Values from 1 to @MaxN

;

GO

Then your little problem never becomes a big problem and the code becomes child's play.

--===== Generate the 3,000,243 rows "Efficiently".

SELECT d.Id

,NR = RangeStart+t.N

,Category

FROM @rng-2 d

CROSS APPLY dbo.fnTally(0,(d.RangeEnd-d.RangeStart)) t

;

That'll generate all 3,000,243 rows to the screen in just 16 seconds. If you use SELECT INTO, it'll create a table with all those rows in it in just over a second.