This blog will no longer be updated.

New content is available on my new blog

Numbers table - Piotr Rodak

Numbers table

I haven't been here for a while.. Quite busy time in my work, and also a bit of laziness, let's be honest ;)

There are a few things that I came across druring last few months. I will try to write about them in following posts.

Today I would like to write about Numbers or Tally table. The idea of having a table that contains only numbers and is used in various scenarios is not entirely new. Jeff Moden  wrote an excellent article that contains a few ideas about how to use the tally table. These are really only a few samples and I would like to know about other implementations. I personally used one of the ideas from the article to implement string split functionality for parameters passed as CSV arrays. You may like them or not, but in some cases it is still much better to pass such a list than to do some twisted programming on DAL or business layer.

So, the split string is quite same as in Jeff's article. I wrapped the functionality in UDF that returns table. The code requires that the list of values begins and ends with commas. I have lists of values that do not follow this requirement. Since I wanted to have single statement UDF and also pretty simple and clean way of calling it, I decided to use CTE to modify the parameter value so it is suitable for the split string code:

CREATE function [Admin].[fnListToTable](@list varchar(max), @separator varchar(10))
returns table
as
return
(
        --splits list of strings into a table. Uses Admin.tTally as indexer.
        with ParamCte(GroupIDs) as
        (select @separator + @list + @separator)

                SELECT SUBSTRING(GroupIDs,Number+1,CHARINDEX(@separator,GroupIDs,Number+1)-Number-1) Field
                        FROM Admin.tTally, ParamCte
                        WHERE Number < LEN(@list) 
                        and SUBSTRING(GroupIDs,Number,1) = @separator
                       
)

 The other application for tally table I found very useful is to enable decoding of bit masks. I have a table that stores some simple scheduling information.

 
CREATE TABLE [Control].[tFeedSchedule](
        [ScheduleId] [int] IDENTITY(1,1) NOT NULL,
        [FeedId] [int] NOT NULL,
        [DaysOfWeek] [tinyint] NOT NULL,
        [WindowOpen] [smallint] NOT NULL,
        [WindowClose] [smallint] NOT NULL,
 CONSTRAINT [PK_tFeedSchedule] PRIMARY KEY CLUSTERED
(
        [ScheduleId] ASC
)

)

 The DaysOfWeek column contains days of week when certain event is due to occur and WindowOpen and WindowClose numbers of minutes from midnight. Days of week are encoded as bit flags starting from Monday at 00000001 binary and ending at Sunday at 01000000 binary. There are some functions in my database that return real dates steming from the schedules defined in the above table.

The function below is one of such functions: It returns table with time 'windows' for specific feeds to come in week timeframe.

CREATE function [Control].[fnGetScheduleDaysForFeed](@FeedID int = null)
returns table
as
return
(
        --Returns list of days feed is scheduled to run in with their time windows
        select FeedID, case when @@datefirst = 1 then Number else Number + 1 end as [DayOfWeek], WindowOpen WindowOpenMinutes, WindowClose WindowCloseMinutes,

        convert(varchar, Dateadd(minute, WindowOpen, DATEADD(day, 0, DATEDIFF(day, 0, GETDATE()))), 108) WindowOpenTime,

        convert(varchar, Dateadd(minute, WindowClose, DATEADD(day, 0, DATEDIFF(day, 0, GETDATE()))), 108) WindowCloseTime,

        case when WindowOpen > WindowClose then 1 else 0 end CrossMidnight
 from Control.tFeedSchedule a inner join Admin.tTally b
        on a.DaysOfWeek & power(2, Number-1) <> 0
        where Number < 8 and (@FeedID is null or a.FeedID = @FeedID)
)

 There are many more possible applications of numbers table. I wonder, if it would be useful to create a library of such code snippets or ideas? If you know links to such resources, please put them in comments.

 

Published 09 November 2008 20:33 by Piotr Rodak

Comments

# &raquo; Numbers table

Pingback from  &raquo; Numbers table

09 November 2008 21:27 by » Numbers table

# re: Numbers table

Trying again, here are some of my previous posts that use numbers tables -- I'll split it into two comments in case it was rejected before for having too many links:

sqlblog.com/.../splitting-a-string-of-unlimited-length.aspx

sqlblog.com/.../counting-occurrences-of-a-substring-within-a-string.aspx

sqlblog.com/.../dealing-with-very-large-bitmasks.aspx

10 November 2008 02:02 by Adam Machanic

# re: Numbers table

Well I think there must be some serious anti-SPAM measures on your blog because I apparently can't post any comment with a link in it.  So if you want some more ideas for numbers tables, go to my blog and search for dbo.numbers ...

10 November 2008 02:16 by Adam Machanic

# re: Numbers table

Thanks guys for the links - they are really cool.

10 November 2008 23:31 by Piotr Rodak

# re: Numbers table

<a href="www.pythian.com/.../log-buffer-123-a-carnival-of-the-vanities-for-dbas">Piotr Rodak offers his example of the use of a numbers table...</a>

14 November 2008 19:39 by dle

# COLUMNS_UPDATED() for audit triggers

In SQL Server 2005, triggers are pretty much the only option if you want to audit changes to a table

28 April 2010 23:27 by Piotr Rodak