Navigation

	Home Page
	Index
	All Pages

Quick Search

Advanced Search »

Contributor Links

	Create a new Page
	Administration
	File Management
	Login/Logout
	Your Profile

Other Wiki Sections

Software

Discuss (0) History

Counter Table - Generating one On-The-Fly - SQL Server

RSS

Modified on Tue, Jun 01, 2010, 9:14 AM by Administrator Categorized as SQL Server

Limited Version¶

The following function, taken from here, will return a table with a single column (called "N") and the specified number of rows, to a maximum of 32 rows, and the values range from zero to N - 1.

{copytext|div1}

/*
select * from dbo.GetCounterTable(10)
*/
create function [dbo].[GetCounterTable]
    (
    @V int
    ) returns @t table (N int) as begin

set @v = @v - 1

if @v<>0

    insert into @t 
    select (@v) 
    union select * from dbo.GetCounterTable(@v)

else

    insert into @t values (0)

return

end

Increasing the Range¶

We can work around the limit of 32-levels-of-recursion using the following technique. As written, the following function can return up to 10,000 rows, but can easily be expanded to more.

{copytext|div2}

alter function [dbo].[GetCounterTable]
    (
    @V int
    ) returns @t table (N int) as begin

set @v = @v - 1

if @v > 30 begin

    ;with MyData as (
        select 
            N = a.N + b.N * 10 + c.N * 100 + d.N * 1000
        from
             dbo.GetCounterTable(10) a
            ,dbo.GetCounterTable(10) b        
            ,dbo.GetCounterTable(10) c        
            ,dbo.GetCounterTable(10) d        
        )    
    insert into @t (N)
    select N
    from MyData
	where N <= @v
    order by N
    
    end

else if @v > 0

    insert into @t (N)
    select (@v) 
    union select * from dbo.GetCounterTable(@v)

else if @v = 0

    insert into @t values (0)

return

end

A Simpler Version?¶

This version has simpler code, but suffers from performance issues. Where the above version can generate 10,000 rows in 2 seconds, this version takes 12 seconds.

alter function [dbo].[GetCounterTable]
    (
    @V int
    ) returns @t table (N int) as begin

--declare @rows int
--set @rows = 1000
;
with NumberList as (
    select Number = 0
    union all select Number + 1 from NumberList where Number < @V - 1
    ) 
insert into @t
select * 
from NumberList 
option(maxrecursion 0)

return 

end