SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Number Table Function
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 03/29/2005 :  19:25:35  Show Profile  Reply with Quote
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 - 08/17/2005 :  22:38:50  Show Profile  Reply with Quote
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 - 09/20/2005 :  14:40:49  Show Profile  Reply with Quote
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

Sweden
3279 Posts

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

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

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/22/2007 :  17:58:30  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"

Edited by - SwePeso on 08/22/2007 18:00:38
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 08/22/2007 :  18:08:30  Show Profile  Reply with Quote
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

Edited by - Michael Valentine Jones on 08/22/2007 18:09:47
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/22/2007 :  18:17:19  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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)

USA
7020 Posts

Posted - 08/22/2007 :  19:43:44  Show Profile  Reply with Quote
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

Sweden
30265 Posts

Posted - 08/23/2007 :  03:36:21  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 08/23/2007 :  18:23:57  Show Profile  Reply with Quote
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)

Singapore
17648 Posts

Posted - 01/06/2008 :  21:45:17  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 06/03/2009 :  17:27:03  Show Profile  Reply with Quote
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

Edited by - TG on 06/03/2009 17:28:13
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 06/12/2009 :  12:14:24  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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

Edited by - Transact Charlie on 06/12/2009 12:15:38
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 06/12/2009 :  12:49:22  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.36 seconds. Powered By: Snitz Forums 2000