Generating a Range
-
Jeff Moden's post using syscolumns gave me inspiration for a simple solution using new features of SQL Server 2005. It is similarly limited to the number of rows in sys.all_columns squared.
DECLARE @first int, @last int
SET @first = 10
SET @last = 500000
SELECT TOP (@last - @first + 1) ROW_NUMBER() OVER(ORDER BY a.object_id) + @first - 1 AS Number
FROM master.sys.all_columns a, master.sys.all_columns b
-
David wrote... "If I was to use the static table approach I would probably stick it in its own database so that it wouldn't artificially inflate the backups."
Yep, I know what you mean but I thought your article was awesome... you had the hair to do a comparison on a difficult subject. Well, done.
Matt, thanks for your post... I didn't realize that 2005 had incorporated an Oracle like Row_Number () Over command... now, that's useful! Because of the order by, I have to ask, how's the performance?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi,
I tested my modification to the function for the aforementioned range (5,567720) and got: f2:9986 and f3:10183 in MS. For my range: (211000,339999,10) I can't reliably measure the difference.
I wanted to add steps to the function:
ALTER FUNCTION dbo.fnCrossJoinRange3 (
@first int, --##PARAM @first The lowest value in the range.
@last int, --##PARAM @last The highest value in the range.
@step int --##PARAM @step The increment value.
)
RETURNS @values TABLE ( value int primary key ) AS
BEGIN
declare @min-2 int
set @min-2 = @first
set @last = (@last - @first) / @step
set @first = 0
INSERT INTO @values(value)
SELECT @min +
(@step *
( -- raw range
units.value +
(tens.value) +
(hundreds.value ) +
(Thousands.value ) +
(TenThousands.value ) +
(CThousands.value ) +
(Millions.value )
-- raw range
)
)
FROM dbo.Digits units
CROSS JOIN (SELECT value * 10 as value from dbo.Digits WHERE value * 10 <=@last) tens
CROSS JOIN (SELECT value * 100 as value from dbo.Digits WHERE value * 100 <=@last) hundreds
CROSS JOIN (SELECT value * 1000 as value from dbo.Digits WHERE value * 1000 <=@last) Thousands
CROSS JOIN (SELECT value * 10000 as value from dbo.Digits WHERE value * 10000 <=@last) TenThousands
CROSS JOIN (SELECT value * 100000 as value from dbo.Digits WHERE value * 100000 <=@last) CThousands
CROSS JOIN (SELECT value * 1000000 as value from dbo.Digits WHERE value * 1000000 <=@last) Millions
where units.value +
(tens.value ) +
(hundreds.value) +
(Thousands.value ) +
(TenThousands.value ) +
(CThousands.value ) +
(Millions.value )
BETWEEN @first and @last
RETURN
END
GO
-
Hi,
Based on all the replies I put together the following (just to compare and it was a slow day at the office
):1. A function that might not be the fastest of all (bit better performance than fn_nums) but the most comprehensive. It includes negative ranges (limited to range of an int), a step value and consists of a combination of using a a form of the original cross join function, identity values and incremental inserts.
2. A modification of the original Cross Join Function fnUnionRange using computed columns which improves performance a bit.
The first function consists of 3 functions
create function dbo.fn_zero_to_giga()
returns @basevalues table
(
unit int not null primary key,
deca as ( unit * 10 ),
hecto as ( unit * 100 ),
kilo as ( unit * 1000 ),
dakilo as ( unit * 10000 ),
hkilo as ( unit * 100000 ),
mega as ( unit * 1000000 ),
damega as ( unit * 10000000 ),
hmega as ( unit * 100000000 ),
giga as ( case when unit < 3 then unit * 1000000000 else 0 end )
)
as
begin
insert @basevalues
select 0 as value
union all
select 1
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
return
end
go
create function dbo.fn_int_range_tab_base
(
@p_startnum int = 1,
@p_endnum int = 1,
@p_increment int = 1
)
returns @rangetab table ( idx int identity(-2147483648,1) primary key, val int )
as
begin
-- Validate the start and end numbers
set @p_startnum = isnull( @p_startnum, 1 )
set @p_endnum = isnull( @p_endnum, 1 )
set @p_increment = isnull( @p_increment, 1 )
declare @order smallint
declare @val int
declare @currcnt int
declare @endidx int
declare @adjendnum int
set @order = 1
-- Swap around the values if required and set the order flag
if @p_startnum > @p_endnum begin
set @order = -1
set @adjendnum = @p_startnum
set @p_startnum = @p_endnum
set @p_endnum = @adjendnum
end --if
set @adjendnum = @p_startnum + ( ( ( ( @p_endnum - @p_startnum ) / @p_increment ) ) * @p_increment )
set @endidx = -2147483648 + ( ( ( @adjendnum - @p_startnum ) / @p_increment ) + 1 )
insert @rangetab
select 1 as value
from ( select unit as value from dbo.fn_zero_to_giga() where -2147483648 + unit <= @endidx ) as u,
( select deca as value from dbo.fn_zero_to_giga() where -2147483648 + deca <= @endidx ) as da,
( select hecto as value from dbo.fn_zero_to_giga() where -2147483648 + hecto <= @endidx ) as h,
( select kilo as value from dbo.fn_zero_to_giga() where -2147483648 + kilo <= @endidx ) as k,
( select dakilo as value from dbo.fn_zero_to_giga() where -2147483648 + dakilo <= @endidx ) as dak,
( select hkilo as value from dbo.fn_zero_to_giga() where -2147483648 + hkilo <= @endidx ) as hk
where -2147483648 + u.value + da.value + h.value + k.value + dak.value + hk.value + 1 <= @endidx
set @currcnt = @@rowcount
while @currcnt < ( ( ( @adjendnum - @p_startnum ) / @p_increment ) + 1 ) begin
if @@identity + @currcnt < @endidx begin
-- Avoid using the where clause on large ranges
insert @rangetab
select val
from @rangetab
end --if
else begin
insert @rangetab
select val
from @rangetab
where idx + @currcnt < @endidx
end --else
set @currcnt = @currcnt + @@rowcount
end --else
if @p_increment > 1 begin
if @order = 1 begin
set @val = @p_startnum - @p_increment
end --if
else begin
set @val = @adjendnum + @p_increment
end --else
set @p_increment = @p_increment * @order
update @rangetab
set @val = val = @val + @p_increment
end --if
return
end
go
create function dbo.fn_int_range_tab
(
@p_startnum int = 1,
@p_endnum int = 1,
@p_increment int = 1
)
returns table
as
return
(
select case isnull( @p_increment, 1 ) when 1 then ( idx - -2147483648 ) + @p_startnum else val end as val
from dbo.fn_int_range_tab_base( @p_startnum, @p_endnum, @p_increment )
)
go
The second function uses the fn_zero_to_giga function above to make it a bit more readable and to improve performance using computed columns. It could be modified to use a 10 row permanent table instead of a table variable to give a bit of a performance increase.
create function dbo.fnUnionRangeNew1
(
@first int ,
@last int
)
returns table
as
return
(
select u.value + da.value + h.value + k.value + dak.value + hk.value + m.value as value
from ( select unit as value from dbo.fn_zero_to_giga() where unit <= @last ) as u,
( select deca as value from dbo.fn_zero_to_giga() where deca <= @last ) as da,
( select hecto as value from dbo.fn_zero_to_giga() where hecto <= @last ) as h,
( select kilo as value from dbo.fn_zero_to_giga() where kilo <= @last ) as k,
( select dakilo as value from dbo.fn_zero_to_giga() where dakilo <= @last ) as dak,
( select hkilo as value from dbo.fn_zero_to_giga() where hkilo <= @last ) as hk,
( select mega as value from dbo.fn_zero_to_giga() where mega <= @last ) as m
where u.value + da.value + h.value + k.value + dak.value + hk.value + m.value between @first and @last
)
go
Viewing 4 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply