December 1, 2015 at 5:03 pm
Hi all,
Can someone please recommend an efficient way to achieve the following. Thank you.
-- 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 * FROM @rng-2
-- expected result
select 123 AS Id, 120000006660 AS NR, 5 AS Category union all
select 123,120000006661,5 union all
select 123,120000006662,5 union all
select 123,120000006663,5 union all
select 123,120000006664,5 union all
select 123,120000006665,5 union all
select 123,120000006666,5 union all
select 123,120000006667,5 union all
select 123,120000006668,5 union all
select 123,120000006669,5 union all
select 123,120000006670,5 union all
select 123,120000006671,5 union all
select 123,120000006672,5 union all
select 123,120000006673,5 union all
select 123,120000006674,5 union all
select 123,120000006675,5 union all
select 123,120000006676,5 union all
select 123,120000006677,5 union all
select 123,120000006678,5 union all
select 123,120000006679,5 union all
select 123,120000006680,5 union all
select 123,120000006681,5 union all
select 123,120000006682,5 union all
select 123,120000006683,5 union all
select 123,120000006684,5 union all
select 123,120000006685,5 union all
select 123,120000006686,5 union all
select 123,120000006687,5 union all
select 123,120000006688,5 union all
select 123,120000006689,5 union all
select 123,120000006690,5 union all
select 123,120000011660,5 union all
select 123,120000011661,5 union all
select 123,120000011662,5 union all
select 123,120000011663,5 union all
select 123,120000011664,5 union all
select 123,120000011665,5 union all
select 123,120000011666,5 union all
select 123,120000011667,5 union all
select 123,120000011668,5 union all
select 123,120000011669,5 union all
select 123,120000011670,5 union all
select 123,199000011660,10 union all
select 123,199000011661,10 union all
select 123,199000011662,10 union all
select 123,199000011663,10 union all
select 123,199000011664,10 union all
select 123,199000011665,10 union all
select 123,199000011666,10 union all
select 123,199000011667,10 union all
select 123,199000011668,10 union all
select 123,199000011669,10 union all
select 123,199000011670,10 union all
select 789,88800001060,5 union all
select 789,88800001061,5 union all
select 789,88800001062,5 union all
select 789,88800001063,5 union all
select 789,88800001064,5 union all
select 789,88800001065,5 union all
select 789,88800001066,5 union all
select 789,88800001067,5 union all
select 789,88800001068,5 union all
select 789,88800001069,5 union all
select 789,88800001070,5 union all
select 789,88800001071,5 union all
select 789,88800001072,5 union all
select 789,88800001073,5 union all
select 789,88800001074,5 union all
select 789,88800001075,5 union all
select 789,88800001076,5 union all
select 789,88800001077,5 union all
select 789,88800001078,5 union all
select 789,88800001079,5 union all
select 789,88800001080,5 union all
select 789,88800001081,5 union all
select 789,88800001082,5 union all
select 789,88800001083,5 union all
select 789,88800001084,5 union all
select 789,88800001085,5 union all
select 789,88800001086,5 union all
select 789,88800001087,5 union all
select 789,88800001088,5 union all
select 789,88800001089,5 union all
select 789,88800001090,5 union all
select 789,99900011660,10 union all
select 789,99900011661,10 union all
select 789,99900011662,10 union all
select 789,99900011663,10 union all
select 789,99900011664,10 union all
select 789,99900011665,10 union all
select 789,99900011666,10 union all
select 789,99900011667,10 union all
select 789,99900011668,10 union all
select 789,99900011669,10 union all
select 789,99900011670,10 union all
select 789,99900011671,10 union all
select 789,99900011672,10 union all
select 789,99900011673,10 union all
select 789,99900011674,10 union all
select 789,99900011675,10 union all
select 789,10000011560,15 union all
select 789,10000011561,15 union all
select 789,10000011562,15 union all
select 789,10000011563,15 union all
select 789,10000011564,15 union all
select 789,10000011565,15 union all
select 789,10000011566,15 union all
select 789,10000011567,15 union all
select 789,10000011568,15 union all
select 789,10000011569,15 union all
select 789,10000011570,15 union all
select 789,10000011571,15 union all
select 789,10000011572,15 union all
select 789,10000011573,15 union all
select 789,10000011574,15 union all
select 789,10000011575,15 union all
select 789,10000011576,15 union all
select 789,10000011577,15 union all
select 789,10000011578,15 union all
select 789,10000011579,15 union all
select 789,10000011580,15 union all
select 789,10000011581,15 union all
select 789,10000011582,15 union all
select 789,10000011583,15 union all
select 789,10000011584,15 union all
select 789,10000011585,15 union all
select 789,10000011586,15 union all
select 789,10000011587,15 union all
select 789,10000011588,15 union all
select 789,10000011589,15 union all
select 789,10000011590,15 union all
select 789,10000011591,15 union all
select 789,10000011592,15 union all
select 789,10000011593,15 union all
select 789,10000011594,15 union all
select 789,10000011595,15 union all
select 789,10000011596,15 union all
select 789,10000011597,15 union all
select 789,10000011598,15 union all
select 789,10000011599,15 union all
select 789,10000011600,15 union all
select 789,10000011601,15 union all
select 789,10000011602,15 union all
select 789,10000011603,15 union all
select 789,10000011604,15 union all
select 789,10000011605,15 union all
select 789,10000011606,15 union all
select 789,10000011607,15 union all
select 789,10000011608,15 union all
select 789,10000011609,15 union all
select 789,10000011610,15 union all
select 789,10000011611,15 union all
select 789,10000011612,15 union all
select 789,10000011613,15 union all
select 789,10000011614,15 union all
select 789,10000011615,15 union all
select 789,10000011616,15 union all
select 789,10000011617,15 union all
select 789,10000011618,15 union all
select 789,10000011619,15 union all
select 789,10000011620,15 union all
select 789,10000011621,15 union all
select 789,10000011622,15 union all
select 789,10000011623,15 union all
select 789,10000011624,15 union all
select 789,10000011625,15 union all
select 789,10000011626,15 union all
select 789,10000011627,15 union all
select 789,10000011628,15 union all
select 789,10000011629,15 union all
select 789,10000011630,15 union all
select 789,10000011631,15 union all
select 789,10000011632,15 union all
select 789,10000011633,15 union all
select 789,10000011634,15 union all
select 789,10000011635,15 union all
select 789,10000011636,15 union all
select 789,10000011637,15 union all
select 789,10000011638,15 union all
select 789,10000011639,15 union all
select 789,10000011640,15 union all
select 789,10000011641,15 union all
select 789,10000011642,15 union all
select 789,10000011643,15 union all
select 789,10000011644,15 union all
select 789,10000011645,15 union all
select 789,10000011646,15 union all
select 789,10000011647,15 union all
select 789,10000011648,15 union all
select 789,10000011649,15 union all
select 789,10000011650,15 union all
select 789,10000011651,15 union all
select 789,10000011652,15 union all
select 789,10000011653,15 union all
select 789,10000011654,15 union all
select 789,10000011655,15 union all
select 789,10000011656,15 union all
select 789,10000011657,15 union all
select 789,10000011658,15 union all
select 789,10000011659,15 union all
select 789,10000011660,15 union all
select 789,10000011661,15 union all
select 789,10000011662,15 union all
select 789,10000011663,15 union all
select 789,10000011664,15 union all
select 789,10000011665,15 union all
select 789,10000011666,15 union all
select 789,10000011667,15 union all
select 789,10000011668,15 union all
select 789,10000011669,15 union all
select 789,10000011670,15 union all
select 258,599900010660,10 union all
select 258,599900010661,10 union all
select 258,599900010662,10 union all
select 258,599900010663,10 union all
select 258,599900010664,10 union all
select 258,599900010665,10 union all
select 258,599900010666,10 union all
select 258,599900010667,10 union all
select 258,599900010668,10 union all
select 258,599900010669,10 union all
select 258,599900010670,10 union all
select 258,599900010671,10 union all
select 258,599900010672,10 union all
select 258,599900010673,10 union all
select 258,599900010674,10 union all
select 258,599900010675,10 union all
select 258,599900010676,10 union all
select 258,599900010677,10 union all
select 258,599900010678,10 union all
select 258,599900010679,10 union all
select 258,599900010680,10 union all
select 258,599900010681,10 union all
select 258,599900010682,10 union all
select 258,599900010683,10 union all
select 258,599900010684,10 union all
select 258,599900010685,10 union all
select 258,510000010560,10 union all
select 258,510000010561,10 union all
select 258,510000010562,10 union all
select 258,510000010563,10 union all
select 258,510000010564,10 union all
select 258,510000010565,10
December 1, 2015 at 5:05 pm
Read this article:
http://www.sqlservercentral.com/articles/T-SQL/62867/
You'll find your solution in there.
_____________
Code for TallyGenerator
December 1, 2015 at 6:09 pm
Hi, Thanks for your response. Not sure if I want to follow that approach due to the BIGINT data types. I'm looking for a simple way of doing this, performance doesn't really matter as the stored procedure would get executed only a couple of times a week.
December 1, 2015 at 6:18 pm
I don't understand what the bigints have to do with it. For a real or virtual table of numbers that goes from 1-N:
select startnum + (num-1)
from numtable
where num <= rangevalue
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 1, 2015 at 7:08 pm
I think using a while loop this should be achievable
DECLARE @rng-2 TABLE ( seq int identity(1,1),Id INT, RangeStart BIGINT, RangeEnd BIGINT, Category INT, NumberCount INT)
DECLARE @RNG_output TABLE ( Id INT, Range_No BIGINT, Category 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 * from @rng-2
-- Define variables
Declare @MinValue int
Declare @MaxValue int
Declare @Counter int
Declare @id int
Declare @RangeStart BIGINT
Declare @RangeEnd BIGINT
Declare @Category int
Declare @NumberCount int
Select @MaxValue = max (seq) from @rng-2
Select @counter = 1
--Print 'The max value ' +convert(varchar,@MaxValue )
While ( @counter <= @MaxValue)
Begin
Select
@id = id
,@RangeStart= RangeStart
,@RangeEnd= RangeEnd
,@Category = Category
,@NumberCount = NumberCount
from @rng-2 where seq = @counter
--Print 'The @counter value ' +convert(varchar,@counter )
While (@RangeStart <= @RangeEnd )
Begin
--Print '@RangeStart ' + convert(varchar,@RangeStart)
INSERT INTO @RNG_output (id,Range_No,Category)
SELECT @id ,@RangeStart,@Category
Select @RangeStart = @RangeStart + 1
End
Select @counter = @counter+ 1
-- Resetting the values
Select
@id = 0
,@RangeStart= 0
,@RangeEnd= 0
,@Category = 0
,@NumberCount = 0
End
select * from @RNG_output
December 1, 2015 at 9:28 pm
First, below is a simple and easy solution that is much less complicated than a loop-based, multi-variable solution. It also performs quite well (for when performance does matter).
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 * FROM @rng-2;
WITH
L1 AS
(
SELECT C=1
FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(c)
),
iTally AS
(
SELECT N = ROW_NUMBER() OVER (ORDER BY (SELECT 1))-1
FROM L1 a, L1 b, L1 c, L1 d
)
SELECT Id, NR = RangeStart+N, Category
FROM @rng-2
CROSS APPLY
(
SELECT TOP(NumberCount) N
FROM iTally
) i;
I would recommend that you give that article that Sergiy posted about numbers/tally tables - it will change your career: http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]
-- Itzik Ben-Gan 2001
December 1, 2015 at 10:29 pm
kiril.lazarov.77 (12/1/2015)
Hi, Thanks for your response. Not sure if I want to follow that approach due to the BIGINT data types. I'm looking for a simple way of doing this, performance doesn't really matter as the stored procedure would get executed only a couple of times a week.
Heh... listen to you. Did you or did you not start this post off with the following?
kiril.lazarov.77 (12/1/2015)
Hi all,Can someone please recommend an [font="Arial Black"]efficient [/font]way to achieve the following. Thank you.
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.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 1, 2015 at 10:48 pm
himanshu.sinha (12/1/2015)
I think using a while loop this should be achievableDECLARE @rng-2 TABLE ( seq int identity(1,1),Id INT, RangeStart BIGINT, RangeEnd BIGINT, Category INT, NumberCount INT)
DECLARE @RNG_output TABLE ( Id INT, Range_No BIGINT, Category 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 * from @rng-2
-- Define variables
Declare @MinValue int
Declare @MaxValue int
Declare @Counter int
Declare @id int
Declare @RangeStart BIGINT
Declare @RangeEnd BIGINT
Declare @Category int
Declare @NumberCount int
Select @MaxValue = max (seq) from @rng-2
Select @counter = 1
--Print 'The max value ' +convert(varchar,@MaxValue )
While ( @counter <= @MaxValue)
Begin
Select
@id = id
,@RangeStart= RangeStart
,@RangeEnd= RangeEnd
,@Category = Category
,@NumberCount = NumberCount
from @rng-2 where seq = @counter
--Print 'The @counter value ' +convert(varchar,@counter )
While (@RangeStart <= @RangeEnd )
Begin
--Print '@RangeStart ' + convert(varchar,@RangeStart)
INSERT INTO @RNG_output (id,Range_No,Category)
SELECT @id ,@RangeStart,@Category
Select @RangeStart = @RangeStart + 1
End
Select @counter = @counter+ 1
-- Resetting the values
Select
@id = 0
,@RangeStart= 0
,@RangeEnd= 0
,@Category = 0
,@NumberCount = 0
End
select * from @RNG_output
As While loops go, that one's pretty fast. It only takes a little over a minute to return all the 3,000,243 rows to the screen using the test data that I used in my previous post above.
When you absolutely must use a While loop (and this case isn't one of those, just to be sure), you can do a couple of things to make it a bit faster. First, use SET NOCOUNT ON. It's not a big thing but it'll cut out a couple of seconds generating all of the singleton row counts. The other thing is to put your outer WHILE loop in an explicit transaction and COMMIT it right after the END of the outer While loop. It doesn't help a huge amount (about 25%) in this case because of the Table Variable and the fact that I have enough memory so it doesn't spool to disk but if need to write to disk, it'll save a lot especially in reducing the time it writes to the log file.
Again, though, it's usually easier to write set based code with a Tally Table or a Tally Function in play. Consider not writing loops for these kinds of things.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 1, 2015 at 11:35 pm
Thanks Jeff for the idea of putting the while loop in TRAN , I think I understand the logic behind that . I hope if anyone who uses the code will make the necessary updates .
December 3, 2015 at 12:51 pm
Thank you very much for taking the time to respond. Works perfectly.
December 3, 2015 at 1:09 pm
himanshu.sinha (12/1/2015)
Thanks Jeff for the idea of putting the while loop in TRAN , I think I understand the logic behind that . I hope if anyone who uses the code will make the necessary updates .
A better way would be to avoid the loop.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 3, 2015 at 3:14 pm
Jeff Moden (12/3/2015)
himanshu.sinha (12/1/2015)
Thanks Jeff for the idea of putting the while loop in TRAN , I think I understand the logic behind that . I hope if anyone who uses the code will make the necessary updates .A better way would be to avoid the loop.
Amen to that. There are cases where a loop is required, but this is certainly not one of them.
December 3, 2015 at 3:23 pm
Jeff Moden (12/3/2015)
himanshu.sinha (12/1/2015)
Thanks Jeff for the idea of putting the while loop in TRAN , I think I understand the logic behind that . I hope if anyone who uses the code will make the necessary updates .A better way would be to avoid the loop.
Jeff, just one change I would make for code running on SQL Server 2008 and newer (yes, I realize people still need to be able support SQL Server 2005):
WITH
E1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(N)) --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
;
December 3, 2015 at 4:19 pm
Recursive Solution
-- 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, 599000010660, 599000010685, 10, 26 UNION ALL
SELECT 258, 510000010560, 510000010565, 10, 6
-- Recursive Solution
-- Should be used carefully
-- Only if gap between ranges wont be massive
;WITH dataCTE AS
(
SELECT Id
, NR = RangeStart
, RangeEnd
, Category
FROM @rng-2
UNION ALL
SELECT Id
, NR = NR + 1
, RangeEnd
, Category
FROM dataCTE
WHERE NR < RangeEnd
)
SELECT Id
, NR
, Category
FROM dataCTE
OPTION (MAXRECURSION 1000)
I also noticed a data issue in below :-
Start > End
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
December 3, 2015 at 4:38 pm
Aditya Daruka (12/3/2015)
Recursive Solution
-- 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, 599000010660, 599000010685, 10, 26 UNION ALL
SELECT 258, 510000010560, 510000010565, 10, 6
-- Recursive Solution
-- Should be used carefully
-- Only if gap between ranges wont be massive
;WITH dataCTE AS
(
SELECT Id
, NR = RangeStart
, RangeEnd
, Category
FROM @rng-2
UNION ALL
SELECT Id
, NR = NR + 1
, RangeEnd
, Category
FROM dataCTE
WHERE NR < RangeEnd
)
SELECT Id
, NR
, Category
FROM dataCTE
OPTION (MAXRECURSION 1000)
I also noticed a data issue in below :-
Start > End
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
This will be much slower than the tally table solutions already presented.
-- Itzik Ben-Gan 2001
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply