Tally tables

I first learned about tally tables from the forums at SqlServerCentral which is great resource for information about SQL Server. (Free registration is required to view the content.)

A tally table is simply a single-column table that holds integers from 1 to any arbitrary large number. For example, this code from SSC will create a tally table of numbers from 1 to 11,000:

SELECT TOP 11000
 IDENTITY(int,1,1) AS N
INTO
 dbo.Tally
FROM
 Master.dbo.SysColumns sc1,
 Master.dbo.SysColumns sc2

ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
GO

The benefit of a tally table is that it can be used to implement set-based solutions, thus avoiding cursors or WHILE loops.

Example 1: Let's say that you need to generate a list of all dates from 05/12/2008 to 06/02/2008 inclusive. You might use a temp table or table variable, and construct a loop that inserts records for each date in the range. By using a tally table, all you need is this (using variables to make it flexible):

DECLARE
 @start datetime,
 @end datetime

SELECT
 @start = '05/12/2008',
 @end = '06/02/2008'

SELECT
 DATEADD(d, (N - 1), @start)
FROM
 Tally
WHERE
 N BETWEEN 1 AND DATEDIFF(d, @start, @end) + 1

Example 2: You have an Orders table like this:

ID int IDENTITY (1,1,) NOT NULL,
ProductID int,
OrderDate datetime,
Quantity int

and you need the total orders for one product, for one year, by month. The basic query might look like this:

SELECT
 MONTH(o.OrderDate) AS OrderMonth,
 SUM(o.Quantity) AS Total
FROM
 Orders o
WHERE
 YEAR(o.OrderDate) = 2007
 AND ProductID = 49
GROUP BY
 MONTH(o.OrderDate)
ORDER BY
 MONTH(o.OrderDate)

However, this will not return a row for any month that did not have
an order. You can use a tally table to ensure that you have
totals for all 12 months, even for months without an order:

SELECT
 T.N AS OrderMonth,
 SUM
 (CASE
  WHEN MONTH(o.OrderDate) = T.N
  THEN o.Quantity
  ELSE 0 END
 ) AS Total
FROM
 Orders o
 CROSS JOIN Tally T
WHERE
 YEAR(o.OrderDate) = 2007
 AND T.N BETWEEN 1 AND 12
 AND ProductID = 49
GROUP BY
 T.N
ORDER BY
 T.N

There is one more example that I'd like to share, where you can
insert multiple rows from a single edit record, but it is too long
to post in the blog format, so I will assemble this post into
a Word document and add the URL when complete.

EDIT: The full article with examples is here.

SQL Lookups

I am working with an Access app that was converted to use a SQL Server database, and gradually replacing Access queries with T-SQL or stored procedures for better performance.

If you have used Access for a while, you know that the so-called Domain Aggregate functions such as DCount, DLookup, and DSum are very expensive in terms of resource usage. Even if you write your own VBA function to perform the lookup, the fact is that the Jet database engine is not efficient when looking up a single value.

My challenge was that my form needed to pull data from 3 different queries. However, after binding a query to the form to get the 1st value, using DCount for the 2nd value & then DLookup for the 3rd, the form would open quite slowly. In fact, you could observe the various controls on the form being painted as the 3 values were pulled in.

The solution was to create a stored procedure to look up the values, create a pass-thru query to execute the sp, and then open a DAO recordset in the Form_Open event & grab the values from the pass-thru query.

Each of the Access queries / lookups were re-written using T-SQL. Then we declare 3 variables, use the SQL queries to get the values, and write a SELECT statement to return the values; something like this:

DECLARE
@value_1 int,
@value_2 int,
@value_3 int

SELECT @value_1 = (first query converted to T-SQL)

SELECT @value_2 = (second query converted to T-SQL)

SELECT @value_3 = (third query converted to T-SQL)

SELECT
@value_1 AS value_1,
@value_2 AS value_2,
@value_3 AS value_3