//  home   //  advanced search   //  news   //  categories   //  sql build chart   //  downloads   //  statistics
 ASP FAQ 
Home
ASP FAQ Tutorials

   8000XXXX Errors
   ASP.NET 2.0
   Classic ASP 1.0
   Databases
      Access DB & ADO
      General SQL Server & Access Articles
      MySQL
      Other Articles
      Schema Tutorials
      Sql Server 2000
      Sql Server 2005
   General Concepts
   Search Engine Optimization (SEO)

Contact Us
Site Map

Search

Web
aspfaq.com
tutorials.aspfaq.com
sqlserver2000.databases.aspfaq.com

ASP FAQ Tutorials :: Databases :: Sql Server 2000 :: Why should I consider using an auxiliary numbers table?


Why should I consider using an auxiliary numbers table?

In response to a recent newsgroup question, I suggested a solution that would make use of a numbers table. The user responded that it would be a huge waste of space, since they shouldn't have to store every possible integer for this one query. 
 
I disagree. In that individual case (which I will not reproduce here), a query without a numbers table was much more verbose and possibly less efficient. 
 
Besides, there are many other reasons for using a numbers table. These examples will use the following table (which, for my purposes, will not need more than 1,024 rows, but you may need more): 
 
CREATE TABLE dbo.Numbers 

    Number INT IDENTITY(1,1) PRIMARY KEY CLUSTERED 

 
WHILE COALESCE(SCOPE_IDENTITY(), 0) <= 1024 
BEGIN 
    INSERT dbo.Numbers DEFAULT VALUES 
END
 

 
Parsing a string 
 
Let's say you want to accept a comma-delimited list of integers, and you need to grab each value for part of your query. You can use dynamic SQL and an IN clause (but read this article first), you can loop through the list with a function (see Article #2248), or you can use a numbers table to derive the set of numbers from the list. The latter combines coding techniques safer than dynamic SQL and less complex than simulating an array.  
 
DECLARE @csv VARCHAR(255) 
SET @csv = '1,3,5,7,9,14,36,395' 
 
SELECT Number 
    FROM dbo.Numbers 
    WHERE CHARINDEX 
    ( 
        ','+CONVERT(VARCHAR(12),Number)+',', 
        ','+@csv+',' 
    ) > 0 
    ORDER BY Number 
 
You can also do this with a list of strings, though the code is a little more complex. For example: 
 
DECLARE @csv VARCHAR(255) 
SET @csv = 'every,good,boy,deserves,fudge' 
 
SELECT word = SUBSTRING 
    ( 
        ',' + @csv + ',', 
        Number + 1, 
    CHARINDEX 
        ( 
            ',', 
            ',' + @csv + ',',  
            Number + 1 
        ) - Number - 1 
    ) 
    FROM Numbers 
    WHERE Number >= 1 
        AND Number < LEN(',' + @csv + ',') - 1 
        AND SUBSTRING(',' + @csv + ',', Number, 1) = ',' 
    ORDER BY Number 
 
    -- and if you want them sorted alphabetically 
    -- instead of the order they were listed, 
    -- change this to ORDER BY 1 or ORDER BY word 
 

 
Finding IDENTITY gaps 
 
While in most circumstances you shouldn't care if your IDENTITY column has gaps, a numbers table can help identify any breaks in the sequence. 
 
CREATE TABLE dummy 

    ID INT IDENTITY(1,1) 

INSERT dummy DEFAULT VALUES 
INSERT dummy DEFAULT VALUES 
INSERT dummy DEFAULT VALUES 
INSERT dummy DEFAULT VALUES 
INSERT dummy DEFAULT VALUES 
INSERT dummy DEFAULT VALUES 
INSERT dummy DEFAULT VALUES 
DELETE dummy WHERE ID = 3 
GO 
 
SELECT Number 
    FROM dbo.Numbers 
    WHERE Number NOT IN (SELECT ID FROM dummy) 
    AND Number < (SELECT MAX(ID) FROM dummy) 
    ORDER BY Number 
 
    -- if you need only the first available 
    -- integer value, change the query to 
    -- SELECT MIN(Number) or TOP 1 Number 
 
DROP TABLE dummy 
 

 
Generating date ranges 
 
When you need to generate a set of dates in a range, the typical solution is to create a loop and iterate through the range, adding a day each time. However, a numbers table can help us generate this range as a set, instead of treating each date in the range individually. This way, you can use the code directly in a subquery or in a table-valued function, without having to worry about creating a temporary table to hold the values while you iterate through the loop. 
 
DECLARE @sDate SMALLDATETIME, @eDate SMALLDATETIME 
SET @sDate = '20040101' 
SET @eDate = '20040229' 
 
SELECT @sDate + Number  
    FROM dbo.Numbers 
    WHERE @sDate + Number <= @eDate 
    ORDER BY 1 
 
You could extend this example to create your own calendar table (which, like a numbers table, can be very useful—see Article #2519 for more information). The following example will create a calendar table with 1,024 consecutive days, starting at 2004-01-01 (based on the numbers table we created earlier). 
 
CREATE TABLE dbo.Calendar 

    dt SMALLDATETIME PRIMARY KEY CLUSTERED 

GO 
 
DECLARE @sDate SMALLDATETIME 
SET @sDate = '20031231' 
INSERT dbo.Calendar 
    SELECT @sDate + Number 
    FROM dbo.Numbers 
    ORDER BY Number 
 

 
Generating time slices 
 
I often see requests for grouping statistics by 5-minute or 15-minute time intervals. A numbers table can help out with this greatly. To keep it distinct from a normal numbers table: 
 
CREATE TABLE dbo.timeIntervalDigits 

    n INT IDENTITY(1,1) PRIMARY KEY CLUSTERED 

GO
 
Now, let's assume we're working on a 15-minute breakdown. We're going to populate this table with 96 rows (1-96), since there are 4 15-minute intervals per hour, and 24 hours per day. (If we were using 5-minute intervals, we'd have 288 rows.) 
 
SET NOCOUNT ON 
INSERT timeIntervalDigits DEFAULT VALUES  
WHILE SCOPE_IDENTITY() < 96 
BEGIN  
    INSERT dbo.timeIntervalDigits DEFAULT VALUES  
END 
GO
 
So now, what is the relevant data we're looking to slice up? 
 
CREATE TABLE dbo.ArbitraryStats 

    dt SMALLDATETIME, 
    hitCount INT 

GO 
 
SET NOCOUNT ON 
 
-- populate with a bunch of arbitrary data 
 
DECLARE @dt SMALLDATETIME 
SET @dt = CONVERT(CHAR(8), GETDATE(), 112) 
 
INSERT ArbitraryStats SELECT @dt, 12 
INSERT ArbitraryStats SELECT @dt, 15 
INSERT ArbitraryStats SELECT DATEADD(MINUTE, 10, @dt), 12 
INSERT ArbitraryStats SELECT DATEADD(MINUTE, 20, @dt), 10 
INSERT ArbitraryStats SELECT DATEADD(MINUTE, 30, @dt), 12 
INSERT ArbitraryStats SELECT DATEADD(MINUTE, 50, @dt), 12 
INSERT ArbitraryStats SELECT DATEADD(HOUR, 1, @dt), 17 
INSERT ArbitraryStats SELECT DATEADD(MINUTE, 150, @dt), 5 
INSERT ArbitraryStats SELECT DATEADD(HOUR, 3, @dt), 1 
INSERT ArbitraryStats SELECT DATEADD(HOUR, 4, @dt), 6 
INSERT ArbitraryStats SELECT DATEADD(HOUR, 4, @dt), 7 
INSERT ArbitraryStats SELECT DATEADD(HOUR, 6, @dt), 7
 
One way we can approach this is to use a derived subquery which holds the start and end time of each 15-minute slice, and then join that against our stats table. 
 
CREATE PROCEDURE dbo.getStatsBreakdown 
    @dt SMALLDATETIME = NULL 
AS 
BEGIN 
    SET NOCOUNT ON 
 
    -- if date is not passed in, use today 
    -- set to midnight in case time included 
 
    SET @dt = CONVERT(CHAR(8), COALESCE(@dt, GETDATE()), 112) 
 
    SELECT 
        n.StartTime, 
        n.EndTime, 
        HitCount = COALESCE(SUM(s.hitCount), 0) 
    FROM 
        ArbitraryStats s 
    RIGHT OUTER JOIN 
    ( 
        SELECT 
            StartTime = DATEADD(MINUTE, 15 * (n-1), @dt), 
            EndTime = DATEADD(MINUTE, 15 * n, @dt) 
        FROM 
            timeIntervalDigits 
    ) n 
    ON 
        s.dt >= n.StartTime 
        AND s.dt < n.EndTime 
    WHERE 
        n.EndTime <= CURRENT_TIMESTAMP 
    GROUP BY 
        n.StartTime, 
        n.EndTime 
    ORDER BY 
        n.StartTime 
 
END 
GO
 
This procedure will take any date in the past. You can consider raising an error if the user enters a future date. Or, you can remove this limitation by commenting out the WHERE clause. 
 
If the date used is today's date, it will only generate stats for 15-minute periods that have been completed (including the current, in the odd case where you run the query exactly on a time slice boundary). 
 

 
Generating IP ranges 
 
Let's say you have an application that requires checking specific IP addresses against a range (say 128.11.0.x). You can use a numbers table to easily output all 256 possible values for the 4th octet, e.g. 
 
SELECT TOP 256 '128.11.0.'+CONVERT(VARCHAR(3), n-1) 
    FROM dbo.Numbers 
    ORDER BY Number
 

 
For some fun applications of a numbers table, see Vyas' site.

Related Articles

Are there tools available for auditing changes to SQL Server data?
Can I create an index on a BIT column?
Can I have optional parameters to my stored procedures?
Can I implement an input mask in SQL Server?
Can I make SQL Server format dates and times for me?
Can I start IDENTITY values at a new seed?
Can SQL Server tell me which row was inserted most recently?
How can I learn more about undocumented SQL Server stored procedures?
How can I make my SQL queries case sensitive?
How do I audit changes to SQL Server data?
How do I connect to a non-default instance of SQL Server?
How do I connect to SQL Server on a port other than 1433?
How do I create a cross-tab (or "pivot") query?
How do I determine if a table exists in a SQL Server database?
How do I drop a SQL Server database?
How do I find all the available SQL Servers on my network?
How do I get a list of SQL Server tables and their row counts?
How do I get rid of Named Pipes / DBNMPNTW errors?
How do I get the correct date/time from the msdb.sysjob* tables?
How do I get the nth row in a SQL Server table?
How do I get the result of dynamic SQL into a variable?
How do I handle REPLACE() within an NTEXT column in SQL Server?
How do I hide system tables in SQL Server's Enterprise Manager?
How do I know which version of SQL Server I'm running?
How do I limit the number of rows returned in my resultset?
How do I load text or csv file data into SQL Server?
How do I manage changes in SQL Server objects?
How do I monitor SQL Server performance?
How do I prevent linked server errors?
How do I reclaim space in SQL Server?
How do I recover data from SQL Server's log files?
How do I search for special characters (e.g. %) in SQL Server?
How do I start SQL Server Agent from ASP?
How do I time my T-SQL code?
How do I upsize from Access to SQL Server?
How do I upsize my MSDE database to full-blown SQL Server 2000?
How do I use a variable in a TOP clause in SQL Server?
How do I use GETDATE() within a User-Defined Function (UDF)?
How should I store an IP address in SQL Server?
Schema: How do I find all the foreign keys in a database?
SQL Server & MSDE
What are reserved Access, ODBC and SQL Server keywords?
What are the capacities of Access, SQL Server, and MSDE?
What are the main differences between Access and SQL Server?
What do I need to know about SQL Server 2000 SP4?
Where else can I learn about SQL Server?
Where is SP4 for SQL Server 2000?
Why am I having problems with SQL Server 2000 SP3 / SP3a?
Why can't I install SQL Server on Windows Server 2003?
Why can't I install SQL Server on Windows XP?
Why can't I use LIKE '%datepart%' queries?
Why do I get "Login failed for user '\'."?
Why do I get 'object could not be found' or 'invalid object name'?
Why do I get errors about master..spt_values?
Why do I get script errors in Enterprise Manager's 'taskpad' view?
Why do I get SQLSetConnectAttr Failed errors?
Why do I have problems with views after altering the base table?
Why does EM crash when I get an error in a stored procedure?
Why does Enterprise Manager return 'Invalid cursor state'?
Why does my DELETE query not work?
Why does sp_spaceused return inaccurate values?
Why is Enterprise Manager slow at expanding my database list?
Why is my app slow after upgrading from SQL Server 7 to 2000?
Why is tempdb full, and how can I prevent this from happening?
Why should I consider using an auxiliary calendar table?

 

 


Created: 4/2/2004 | Last Updated: 3/12/2005 | broken links | helpful | not helpful | statistics
© Copyright 2006, UBR, Inc. All Rights Reserved. (219)

 

Copyright 1999-2006, All rights reserved.
Finding content
Finding content.  An error has occured...