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
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site