Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Script Library
 Number Table Function

Author  Topic 

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-03-29 : 19:25:35
This script is for an in-line table function, F_TABLE_NUMBER_RANGE, that generates a number table. The input parameters are the @START_NUMBER and @END_NUMBER. It returns a sorted result set containing all intergers from @START_NUMBER to @END_NUMBER inclusive.

This is an improved version of a script that I posted on a topic a few weeks ago. I modified it to cross join fewer tables based on powers of 16, instead of powers of 2, because I found that this compiled and ran much faster for small result sets (less than 10,000 rows).

This is the link to the other post:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=46252&whichpage=5





SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists
(select * from dbo.sysobjects
where id = object_id(N'[dbo].[F_TABLE_NUMBER_RANGE]')
and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[F_TABLE_NUMBER_RANGE]
GO
create function dbo.F_TABLE_NUMBER_RANGE
(
@START_NUMBER int,
@END_NUMBER int
)
/*
This function returns an integer table containing all integers
in the range of@START_NUMBER through @END_NUMBER, inclusive.
The maximum number of rows that this function can return
is 16777216.
*/

returns table
as

return
(
select top 100 percent
NUMBER = (a.NUMBER+b.NUMBER)+
-- Add the starting number for the final result set
-- The case is needed, because the start and end
-- numbers can be passed in any order
case
when @START_NUMBER <= @END_NUMBER
then @START_NUMBER
else @END_NUMBER
end
from
(
Select top 100 percent
NUMBER = convert(int,N01+N02+N03)
From
-- Cross rows from 3 tables based on powers of 16
-- Maximum number of rows from cross join is 4096, 0 to 4095
( select N01 = 0 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 union all select 10 union all select 11 union all
select 12 union all select 13 union all select 14 union all
select 15 ) n01
cross join
( select N02 = 0 union all select 16 union all select 32 union all
select 48 union all select 64 union all select 80 union all
select 96 union all select 112 union all select 128 union all
select 144 union all select 160 union all select 176 union all
select 192 union all select 208 union all select 224 union all
select 240 ) n02
cross join
( select N03 = 0 union all select 256 union all select 512 union all
select 768 union all select 1024 union all select 1280 union all
select 1536 union all select 1792 union all select 2048 union all
select 2304 union all select 2560 union all select 2816 union all
select 3072 union all select 3328 union all select 3584 union all
select 3840 ) n03
where
-- Minimize the number of rows crossed by selecting only rows
-- with a value less the the square root of rows needed.
N01+N02+N03 <
-- Square root of total rows rounded up to next whole number
convert(int,ceiling(sqrt(abs(@START_NUMBER-@END_NUMBER)+1)))
order by
1
) a
cross join
(
Select top 100 percent
NUMBER =
convert(int,
(N01+N02+N03) *
-- Square root of total rows rounded up to next whole number
convert(int,ceiling(sqrt(abs(@START_NUMBER-@END_NUMBER)+1)))
)
From
-- Cross rows from 3 tables based on powers of 16
-- Maximum number of rows from cross join is 4096, 0 to 4095
( select N01 = 0 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 union all select 10 union all select 11 union all
select 12 union all select 13 union all select 14 union all
select 15 ) n01
cross join
( select N02 = 0 union all select 16 union all select 32 union all
select 48 union all select 64 union all select 80 union all
select 96 union all select 112 union all select 128 union all
select 144 union all select 160 union all select 176 union all
select 192 union all select 208 union all select 224 union all
select 240 ) n02
cross join
( select N03 = 0 union all select 256 union all select 512 union all
select 768 union all select 1024 union all select 1280 union all
select 1536 union all select 1792 union all select 2048 union all
select 2304 union all select 2560 union all select 2816 union all
select 3072 union all select 3328 union all select 3584 union all
select 3840 ) n03
where
-- Minimize the number of rows crossed by selecting only rows
-- with a value less the the square root of rows needed.
N01+N02+N03 <
-- Square root of total rows rounded up to next whole number
convert(int,ceiling(sqrt(abs(@START_NUMBER-@END_NUMBER)+1)))
order by
1
) b
where
a.NUMBER+b.NUMBER <
-- Total number of rows
abs(@START_NUMBER-@END_NUMBER)+1 and
-- Check that the number of rows to be returned
-- is less than or equal to the maximum of 16777216
case
when abs(@START_NUMBER-@END_NUMBER)+1 <= 16777216
then 1
else 0
end = 1
order by
1
)

GO
GRANT SELECT ON [dbo].[F_TABLE_NUMBER_RANGE] TO [public]
GO

-- Demo using the function to ruturn numbers 1 to 2000
select NUMBER from dbo.F_TABLE_NUMBER_RANGE(1,2000)

-- Demo using the function to ruturn numbers -1500 to 2000
select NUMBER from dbo.F_TABLE_NUMBER_RANGE(-1500,2000)







CODO ERGO SUM

Hunglech
Starting Member

16 Posts

Posted - 2005-08-17 : 22:38:50
Hi Michael Valentine Jones, I thing this function return the same result


CREATE FUNCTION xf_TableNumberRange(@nMin INT, @nMax INT)
RETURNS @t TABLE (number INT) AS
BEGIN
DECLARE @i INT
SET @i = @nMin
WHILE @i <= @nMax
BEGIN
INSERT INTO @t (number) VALUES (@i)
SELECT @i = @i + 1
END
RETURN
END

Go to Top of Page

bertcord
Starting Member

7 Posts

Posted - 2005-09-20 : 14:40:49
Michael,

very cool...and fast thanks

Hunglech
yes you get the same result....but try this
select NUMBER from dbo.F_TABLE_NUMBER_RANGE(1,100000)

select NUMBER from dbo.xf_TableNumberRange(1,100000)

Michael's is much faster

Bert
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-09-21 : 13:14:32
Some more functions over here in this thread.
[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=46252&whichpage=4[/url]

MVJ's looks very fast.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 17:58:30
Interesting results!
declare @a int, @b int
select @a = 1, @b = 10
select * from F_TABLE_NUMBER_RANGE(@a, @b)

select * from F_TABLE_NUMBER_RANGE(1, 10)

select * from F_TABLE_NUMBER_RANGE(@a, @b-1)
I am using SQL Server 2008, and the first resultset is
1
5
9
2
6
10
3
7
4
8
and the second resultset is
1
2
3
4
5
6
7
8
9
10
And the third resultset
1
4
7
2
5
8
3
6
9



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-08-22 : 18:08:30
Guess it doesn't pay any attention to the TOP with the ORDER BY.

Maybe they'll fix it by the time it goes RTM.

Or maybe it's an new "feature".



CODO ERGO SUM
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 18:17:19
That was expected. I was more curious about the order of them.
Hardcoded value went great, variables {1, 10} is not ok...

BUT! Variables {1, 9} got another order than {1, 10}



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-08-22 : 19:43:44
They do say that order is unspecified if there is no ORDER BY clause in a select. Maybe the query engine is coded to ignore the order by in the function if the outer query does not have an ORDER BY.



CODO ERGO SUM
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-23 : 03:36:21
That's true.

I reacted to the difference in output, for same function and same connected session, only by sending hard-coded values vs @variable values.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

ujb
Starting Member

8 Posts

Posted - 2007-08-23 : 18:23:57
Further to this - I am using SQL Server 2005 and replacing the first occurence of

'select top 100 percent'

with

'select top (abs(@START_NUMBER-@END_NUMBER)+1)'

seems to return an ordered list

Unfortunately this is not an option when I go back to using SQL Server 2000 :)
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-06 : 21:45:17
recently I need a increment step feature, so made slight modification to the F_TABLE_NUMBER_RANGE



SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON
GO
IF EXISTS
(SELECT * FROM dbo.sysobjects
WHERE id = object_id(N'[dbo].[F_TABLE_NUMBER_RANGE_STEP]')
AND xtype IN (N'FN', N'IF', N'TF'))
DROP FUNCTION [dbo].[F_TABLE_NUMBER_RANGE_STEP]
GO
CREATE FUNCTION dbo.F_TABLE_NUMBER_RANGE_STEP
(
@START_NUMBER int,
@END_NUMBER int,
@STEP int

)
/*
This FUNCTION RETURNS an integer TABLE containing ALL integers
IN the range of@START_NUMBER through @END_NUMBER, inclusive.
The maximum number of rows that this FUNCTION can RETURN
IS 16777216.
*/


RETURNS TABLE
AS

RETURN
(
SELECT TOP 100 percent
NUMBER = (a.NUMBER+b.NUMBER)+
-- ADD the starting number FOR the final result SET
-- The CASE IS needed, because the start AND END
-- numbers can be passed IN ANY order
CASE
WHEN @START_NUMBER <= @END_NUMBER
THEN @START_NUMBER
ELSE @END_NUMBER
END
FROM
(
SELECT TOP 100 percent
NUMBER = CONVERT(int,N01+N02+N03)
FROM
-- CROSS rows FROM 3 tables based ON powers of 16
-- Maximum number of rows FROM CROSS JOIN IS 4096, 0 to 4095
( SELECT N01 = 0 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 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL
SELECT 15 ) n01
CROSS JOIN
( SELECT N02 = 0 UNION ALL SELECT 16 UNION ALL SELECT 32 UNION ALL
SELECT 48 UNION ALL SELECT 64 UNION ALL SELECT 80 UNION ALL
SELECT 96 UNION ALL SELECT 112 UNION ALL SELECT 128 UNION ALL
SELECT 144 UNION ALL SELECT 160 UNION ALL SELECT 176 UNION ALL
SELECT 192 UNION ALL SELECT 208 UNION ALL SELECT 224 UNION ALL
SELECT 240 ) n02
CROSS JOIN
( SELECT N03 = 0 UNION ALL SELECT 256 UNION ALL SELECT 512 UNION ALL
SELECT 768 UNION ALL SELECT 1024 UNION ALL SELECT 1280 UNION ALL
SELECT 1536 UNION ALL SELECT 1792 UNION ALL SELECT 2048 UNION ALL
SELECT 2304 UNION ALL SELECT 2560 UNION ALL SELECT 2816 UNION ALL
SELECT 3072 UNION ALL SELECT 3328 UNION ALL SELECT 3584 UNION ALL
SELECT 3840 ) n03
WHERE
-- Minimize the number of rows crossed BY selecting only rows
-- WITH a value less the the square root of rows needed.
N01+N02+N03 <
-- Square root of total rows rounded up to next whole number
CONVERT(int,ceiling(sqrt(abs(@START_NUMBER-@END_NUMBER)+1)))
ORDER BY
1
) a
CROSS JOIN
(
SELECT TOP 100 percent
NUMBER =
CONVERT(int,
(N01+N02+N03) *
-- Square root of total rows rounded up to next whole number
CONVERT(int,ceiling(sqrt(abs(@START_NUMBER-@END_NUMBER)+1)))
)
FROM
-- CROSS rows FROM 3 tables based ON powers of 16
-- Maximum number of rows FROM CROSS JOIN IS 4096, 0 to 4095
( SELECT N01 = 0 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 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL
SELECT 15 ) n01
CROSS JOIN
( SELECT N02 = 0 UNION ALL SELECT 16 UNION ALL SELECT 32 UNION ALL
SELECT 48 UNION ALL SELECT 64 UNION ALL SELECT 80 UNION ALL
SELECT 96 UNION ALL SELECT 112 UNION ALL SELECT 128 UNION ALL
SELECT 144 UNION ALL SELECT 160 UNION ALL SELECT 176 UNION ALL
SELECT 192 UNION ALL SELECT 208 UNION ALL SELECT 224 UNION ALL
SELECT 240 ) n02
CROSS JOIN
( SELECT N03 = 0 UNION ALL SELECT 256 UNION ALL SELECT 512 UNION ALL
SELECT 768 UNION ALL SELECT 1024 UNION ALL SELECT 1280 UNION ALL
SELECT 1536 UNION ALL SELECT 1792 UNION ALL SELECT 2048 UNION ALL
SELECT 2304 UNION ALL SELECT 2560 UNION ALL SELECT 2816 UNION ALL
SELECT 3072 UNION ALL SELECT 3328 UNION ALL SELECT 3584 UNION ALL
SELECT 3840 ) n03
WHERE
-- Minimize the number of rows crossed BY selecting only rows
-- WITH a value less the the square root of rows needed.
N01+N02+N03 <
-- Square root of total rows rounded up to next whole number
CONVERT(int,ceiling(sqrt(abs(@START_NUMBER-@END_NUMBER)+1)))
ORDER BY
1
) b
WHERE
a.NUMBER+b.NUMBER <
-- Total number of rows
abs(@START_NUMBER-@END_NUMBER)+1 AND
-- CHECK that the number of rows to be returned
-- IS less than OR equal to the maximum of 16777216
CASE
WHEN abs(@START_NUMBER-@END_NUMBER)+1 <= 16777216
THEN 1
ELSE 0
END = 1
AND ((a.NUMBER + b.NUMBER) + CASE WHEN @START_NUMBER <= @END_NUMBER THEN @START_NUMBER ELSE @END_NUMBER END - @START_NUMBER) % @STEP = 0

ORDER BY
1
)

GO
-- TEST it

SELECT *
FROM F_TABLE_NUMBER_RANGE_STEP(-10, 10, 3)



KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-06-03 : 17:27:03
I can't take credit for this but some very quick tests seems to show that this is actually faster (than F_TABLE_NUMBER_RANGE). I got the idea from hearing about this blog entry:
http://sqlblog.com/blogs/kent_tegels/archive/2009/05/06/13818.aspx

A co-worker described the idea to me before I got the link so my code is a little different then what is posted there but it is basically the same thing. This will return a bunch more numbers too.


if object_id('dbo.f_table_numbers') > 0
drop function dbo.f_table_numbers
go

create function dbo.f_table_numbers (@st bigint, @end bigint)
returns table
as
return
with
a as (select convert(bigint,0) as n union all select 0),
b as (select 0 as n from a as a cross join a as b),
c as (select 0 as n from b as a cross join b as b),
d as (select 0 as n from c as a cross join c as b),
e as (select 0 as n from d as a cross join d as b),
f as (select 0 as n from e as a cross join e as b),
nums as (select row_number() over (order by (select 1)) as num from f as a cross join f as b)
select @st + num - 1 as num
from nums
where num <= @end-@st+1
go


Be One with the Optimizer
TG
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-12 : 12:14:24
Cheers for the number generator -- very useful. I've changed it a bit though (not in anyway that matters - just personal taste).

Don't know if it's any quicker or slower this way but it is shorter

CREATE FUNCTION dbo.F_TABLE_NUMBER_RANGE
(
@startNumber INT
, @endNumber INT
)
RETURNS TABLE AS RETURN
(
WITH powers AS (
SELECT
[N01], [N02], [N03]
FROM
(
SELECT [N01] = 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11
UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15
) NO1

CROSS JOIN (
SELECT [N02] = 0 UNION SELECT 16 UNION SELECT 32 UNION SELECT 48
UNION SELECT 64 UNION SELECT 80 UNION SELECT 96 UNION SELECT 112
UNION SELECT 128 UNION SELECT 144 UNION SELECT 160 UNION SELECT 176
UNION SELECT 192 UNION SELECT 208 UNION SELECT 224 UNION SELECT 240
) NO2

CROSS JOIN (
SELECT [N03] = 0 UNION SELECT 256 UNION SELECT 512 UNION SELECT 768
UNION SELECT 1024 UNION SELECT 1280 UNION SELECT 1536 UNION SELECT 1792
UNION SELECT 2048 UNION SELECT 2304 UNION SELECT 2560 UNION SELECT 2816
UNION SELECT 3072 UNION SELECT 3328 UNION SELECT 3584 UNION SELECT 3840
) N03
)
SELECT TOP 100 PERCENT
[number] = ( a.[number] + b.[number] ) + CASE WHEN @startNumber <= @endNumber THEN @startNumber ELSE @endNumber END
FROM
(
SELECT TOP 100 PERCENT
[number] = CAST([N01] + [N02] + [N03] AS INT)
FROM
powers
WHERE
[N01] + [N02] + [N03] < CAST(CEILING(SQRT(ABS(@startNumber - @endNumber) + 1 )) AS INT)
ORDER BY
1
) a
CROSS JOIN (
SELECT TOP 100 PERCENT
[number] = CAST(([N01] + [N02] + [N03]) * CAST(CEILING(SQRT(ABS(@startNumber - @endNumber) + 1 )) AS INT) AS INT)
FROM
powers
WHERE
[N01] + [N02] + [N03] < CAST(CEILING(SQRT(ABS(@startNumber - @endNumber) + 1 )) AS INT)
ORDER BY
1
) b
WHERE
a.[number] + b.[number] < ABS(@startNumber - @endNumber) + 1
AND (16777216 - ABS( @startNumber - @endNumber ) - 1 ) > 0
ORDER BY
1
)

GO
GRANT SELECT ON [dbo].[F_TABLE_NUMBER_RANGE] TO [public]
GO

á


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-06-12 : 12:49:22
Just a note here about version compatibility and other items.

The original F_TABLE_NUMBER_RANGE that I posted works with SQL Server versions 2000 or greater.

The versions posted by TG and Transact Charlie require SQL Server versions 2005 or greater.

The version posted by TG does not appear to allow the range to be entered in any order; the lowest number must be parameter @st and the highest must be @end. The following code returned zero rows:
select * from dbo.f_table_numbers(1,-2000)




CODO ERGO SUM
Go to Top of Page
   

- Advertisement -