Auxiliary Tables

Auxiliary tables (also referred as utility or helper tables) are a great tool for many needs in SQL. The good part is they are portable between systems, easy to create and normally do not take much storage. There are many benefits: faster SQL, simplified problem solutions, provide flexibility and options that are not easy to replicate with pure SQL code, and in many cases will help to avoid procedural coding and offer a set based way.

Here are samples for creating the two most common auxiliary tables: table with numbers and calendar table, as well as a couple brief examples of utilizing them.

-- Create table structure

CREATE TABLE Numbers (

 nbr INT NOT NULL

   CONSTRAINT df_numbers_nbr

   DEFAULT 1

   CONSTRAINT ck_numbers_nbr

   CHECK (nbr > 0)

   CONSTRAINT pk_numbers

   PRIMARY KEY);

 

-- Populate via cross joining CTEs

-- Very fast, can be implemented as UDF

WITH

Num1 (n) AS (SELECT 1 UNION ALL SELECT 1),

Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),

Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),

Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y),

Num5 (n) AS (SELECT 1 FROM Num4 AS X, Num4 AS Y),

Num6 (n) AS (SELECT 1 FROM Num5 AS X, Num5 AS Y),

Nums (n) AS

(SELECT ROW_NUMBER() OVER(ORDER BY n)

 FROM Num6)

INSERT INTO Numbers (nbr)

SELECT n FROM Nums

WHERE n <= 1000000;

 

-- Populate via generating digits and multiplication

-- for tens, hundreds, thousands, etc.

WITH Digits (n) AS

( SELECT 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),

Nums (n) AS

( SELECT VII.n * 1000000 +

        VI.n * 100000 +

        V.n * 10000 +

        IV.n * 1000 +

        III.n * 100 +

        II.n * 10 +

        I.n

  FROM Digits AS I

  CROSS JOIN Digits AS II

  CROSS JOIN Digits AS III

  CROSS JOIN Digits AS IV

  CROSS JOIN Digits AS V

  CROSS JOIN Digits AS VI

  CROSS JOIN Digits AS VII

)

INSERT INTO Numbers (nbr)

SELECT n FROM Nums

WHERE n BETWEEN 1 AND 1000000;

 

-- One example of using table with numbers

-- to parse a list string to table format

-- with index for each element

DECLARE @list VARCHAR(100);

DECLARE @delimiter CHAR(1);

 

SET @list = 'Mon,Tue,Wed,Thu,Fri,Sat,Sun';

SET @delimiter = ',';

 

-- Split the list based on delimiter

SELECT SUBSTRING(@list, nbr,

                CHARINDEX(@delimiter,

                           @list + @delimiter, nbr) - nbr) AS list_value,

       nbr + 1 - LEN(REPLACE(LEFT(@list, nbr), @delimiter, '')) AS list_index

FROM Numbers

WHERE SUBSTRING(@delimiter + @list, nbr, 1) = @delimiter

  AND nbr < LEN(@list) + 1;

 

/*

 

-- Results

list_value list_index

---------- -----------

Mon        1

Tue        2

Wed        3

Thu        4

Fri        5

Sat        6

Sun        7

 

 

*/

 

-- Another example to clean non-numeric characters from string

-- The table with numbers is used to slice the string to characters

-- then non-numeric characters are eliminated and the string

-- is concatenated back using FOR XML PATH with blank element name

DECLARE @string VARCHAR(30);

 

SET @string = '01234ab23-97z@';

 

SELECT SUBSTRING(@string, nbr, 1)

FROM Numbers

WHERE nbr <= LEN(@string)

  AND SUBSTRING(@string, nbr, 1) LIKE '[0-9]'

FOR XML PATH('');

 

/*

 

-- Result

012342397

 

*/

 

-- Calendar table structure

CREATE TABLE Calendar (

 cdate DATETIME NOT NULL

   CONSTRAINT df_calendar_cdate

   DEFAULT DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)

   CONSTRAINT ck_calendar_cdate

   CHECK (cdate = DATEADD(day, DATEDIFF(day, 0, cdate), 0))

   CONSTRAINT pk_calendar

   PRIMARY KEY,

 cweek INT NOT NULL

   CONSTRAINT df_calendar_cweek

   DEFAULT 1

   CONSTRAINT ck_calendar_cweek

   CHECK (cweek BETWEEN 1 AND 53),

 cweekday INT NOT NULL

   CONSTRAINT df_calendar_cweekday

   DEFAULT 1

   CONSTRAINT ck_calendar_cweekday

   CHECK (cweekday BETWEEN 1 AND 7),

 cyear INT NOT NULL

   CONSTRAINT df_calendar_cyear

   DEFAULT 1900

   CONSTRAINT ck_calendar_cyear

   CHECK (cyear >= 1900),

 cquarter INT NOT NULL

   CONSTRAINT df_calendar_cquarter

   DEFAULT 1

   CONSTRAINT ck_calendar_cquarter

   CHECK (cquarter BETWEEN 1 AND 4),

 cmonth INT NOT NULL

   CONSTRAINT df_calendar_cmonth

   DEFAULT 1

   CONSTRAINT ck_calendar_cmonth

   CHECK (cmonth BETWEEN 1 AND 12),

 cday INT NOT NULL

   CONSTRAINT df_calendar_cday

   DEFAULT 1

   CONSTRAINT ck_calendar_cday

   CHECK (cday BETWEEN 1 AND 31),

 cday_of_year INT NOT NULL

   CONSTRAINT df_calendar_cday_of_year

   DEFAULT 1

   CONSTRAINT ck_calendar_cday_of_year

   CHECK (cday_of_year BETWEEN 1 AND 366),

 cmonth_name VARCHAR(9) NOT NULL

   CONSTRAINT df_calendar_cmonth_name

   DEFAULT 'N/A',

 cday_name VARCHAR(9) NOT NULL

   CONSTRAINT df_calendar_cday_name

   DEFAULT 'N/A',

 holiday VARCHAR(35) NOT NULL

   CONSTRAINT df_calendar_holiday

   DEFAULT 'N/A');

 

 

-- Calculate number of days for 10 years from '20080101'

SELECT DATEDIFF(day, '20080101', '20180101') AS days;

 

/*

 

-- Result

3653

 

*/

 

-- Insert 3653 days, which is 10 years

-- Another use for table with numbers

INSERT INTO Calendar (cdate)

SELECT DATEADD(dd, N.nbr - 1, '20080101')

FROM Numbers AS N

WHERE N.nbr <= 3653;

 

-- Populate calendar columns

-- First day of the week is logically set to Monday

-- Can be enhanced to have column for

-- ISO week as it differs from internal SQL Server

-- week date part

UPDATE Calendar

SET cweek = DATEPART(wk, cdate - DATEPART(dw, cdate + @@DATEFIRST - 1) + 1),

    cweekday = DATEPART(dw, cdate + @@DATEFIRST - 1),

    cyear = DATEPART(yyyy, cdate),

    cquarter = DATEPART(qq, cdate),

    cmonth = DATEPART(mm, cdate),

    cday = DATEPART(day, cdate),

    cday_of_year = DATEPART(dy, cdate),

    cmonth_name = DATENAME(mm, cdate),

    cday_name = DATENAME(dw, cdate);

 

-- Add some fixed date official holidays

-- See http://en.wikipedia.org/wiki/List_of_holidays_by_country

-- for full list by country

 

-- New Year's Day

UPDATE Calendar

SET holiday = 'New Year''s Day'

WHERE cday = 1

  AND cmonth = 1;

 

-- Christmas Eve

UPDATE Calendar

SET holiday = 'Christmas Eve'

WHERE cday = 24

  AND cmonth = 12;

 

-- Christmas Day

UPDATE Calendar

SET holiday = 'Christmas Day'

WHERE cday = 25

  AND cmonth = 12;

 

-- New Year's Eve

UPDATE Calendar

SET holiday = 'New Year''s Eve'

WHERE cday = 31

  AND cmonth = 12;

 

-- Get work days for year 2008

-- Note that not all official holidays are defined

-- and this is only counting non-weekend days

-- and existing holidays

SELECT COUNT(*) AS work_days

FROM Calendar

WHERE cyear = 2008

  AND cweekday NOT IN (6, 7) -- 6 = Saturday; 7 = Sunday

  AND holiday = 'N/A';

 

/*

 

-- Result

work_days

-----------

258

 

*/

To get ISO week calculation use the function example IsoWeek in SQL Server Books Online under CREATE FUNCTION:
http://msdn2.microsoft.com/en-us/library/ms186755.aspx

In SQL Server 2008 the DATEPART function has been enhanced and has date part parameter ISO_WEEK, which supports ISO 8601 week numbering:
http://msdn2.microsoft.com/en-us/library/ms174420(SQL.100).aspx

On SQL Server 2008 the calendar table can be modeled using the new DATE data type which will eliminate the need of constraints to handle the time portion:
http://technet.microsoft.com/en-us/library/bb630352(SQL.100).aspx

Prevent Table Drop

Since there is no direct permission in SQL Server to drop a table, here are two techniques to implement that.

The first one is based on creating a view on the table with option SCHEMABINDING. When the SCHEMABINDING option is used the table cannot be modified in a way that will affect the view definition, as well as it cannot be dropped unless the view is dropped first.

The second method is using the new DDL triggers in SQL Server 2005. Defining a trigger for DROP_TABLE with rollback in the body will not allow dropping tables.

CREATE TABLE dbo.Foo (

 keycol INT PRIMARY KEY,

 datacol CHAR(1));

 

GO

 

-- Using view with SCHEMABINDING

CREATE VIEW DoNotDropFoo WITH SCHEMABINDING

AS

SELECT keycol, datacol

FROM dbo.Foo;

 

GO

 

-- Attempt to drop table Foo

DROP TABLE dbo.Foo;

 

GO

 

Msg 3729, Level 16, State 1, Line 3

Cannot DROP TABLE 'dbo.Foo' because it is being referenced by object 'DoNotDropFoo'.

 

 

-- Using DDL trigger

CREATE TRIGGER DoNotDropTables

ON DATABASE

FOR DROP_TABLE

AS

  RAISERROR ('Cannot drop tables!', 10, 1);

  ROLLBACK;

 

GO

 

-- Attempt to drop table Foo

DROP TABLE dbo.Foo;

 

GO

 

Cannot drop tables!

Msg 3609, Level 16, State 2, Line 3

The transaction ended in the trigger. The batch has been aborted.