March 24, 2016 at 6:33 pm
Comments posted to this topic are about the item GetNumsAB
-- Itzik Ben-Gan 2001
April 19, 2016 at 4:13 am
Great thanks, very useful function!
April 19, 2016 at 6:44 am
Great script, thanks.
September 13, 2021 at 3:24 am
Nicely done, Alan. Your code runs faster than GetNums and fnTally because it only uses 2 CROSS JOINs instead of 31 and 3 respectively. It also doesn't have the habit of going parallel, which would slow things down a fair bit on larger values like 1 billion and use and insane about of CPU like it currently does in GetNums and fnTally. Of course, most people aren't going to need a list of numbers greater than your implicit hard stop of 100,544,625. Heh... I never thought folks would need more than 11,000 values for physical Tally Tables! 😀
The only thing you might want to add is to do a check for the max number of rows to be returned and if it's larger than your inherent max of 100,544,625 then return nothing in a manner similar to what you've done in your WHERE clause. Maybe something like the following (uses the TOP formula in the WHERE clause).
WHERE @high >= @low and @gap > 0
AND ABS((@high-@low)/ISNULL(NULLIF(@gap,0),1)+1) <= 100,544,625
As a bit of a sidebar, I figured out a way to prevent parallelism in iTVFs without having to use an external MAXDOP 1 and I'm going to write that up as a possible fix for all us crazies that do us such functions to sometimes generate a billion or more numbers in a sequence (I use it as a "Row Source" to generate some insane test tables and sometimes the "N" values themselves). It cuts WAY BACK on the CPU usage. "Going parallel" on these types of things hurts a whole lot more than it helps. Glad to see your good code doesn't have that problem.
--Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply