Three Really Handy Tricks With SQL Server Table Variables

Some things are just plain cool. Databases are not one of these things. However, table variables in SQL Server are really, really useful.

What is a table variable? It is a handy feature of SQL Server which lets you define a variable as a table and then (and this is the important bit) use it in SQL just like any other table. Except it is not really like any other table because it 'lives' outside the complex and slow transactional world of normal tables. 

What is the fuss about? Why is a table variable any different to say - a temporary table? The reason they are different is that they are totally ephemeral. The database engine does not have to apply transactional processing to table variables. It does not have write them out to disk so they can be 'rolled forward' in the case of database failure and recovery. They can be stored any way the database engine sees as most efficient. This means that we as programmers can do a load of stuff with a table variable that would be inefficient to the point of stupidity with a temporary table. This lack of transactionality also means we can play a trick on SQL server and make the data we want to survive a rollback do just that - survive.

Using a table variable as outer join enumerator

In this example, I have a 'sparse' table holding date indexed information. In other words, the table holds information indexed by date, but for those days in which there is no information, there is no row. However, I want a report that shows every day, whether there is information on that day or not. The simple solution is to make an outer join between a table holding one record per day and the sparse table. Those days for which there is no information will just show NULL for the joined sparse table. Old school database developers may have come across this in the past and created a special table with all possible dates already stored in it. This approach works but has the drawback that all that extra information must be stored in the database and the code is dependant on that data being there which is a possible cause of errors. On top of this, if you have many such enumeration tasks, you will end up with many such tables just holding enumeration data.

Another alternative would be to create a temporary table each time the report was run. This table would then be populated with the enumerator (dates in this case). The snag is that inserting a lot of rows into any table tends to be slow. This means altering table data (a process referred to as mutation). Mutating data in a transactional context is a complex and expensive business (for the database engine). So, it is bad practise to take this approach to enumeration tasks from a pure performance point of view.

DECLARE @dates As TABLE 
(
    dTime DateTime
)
DECLARE @sDate As DateTime
DECLARE @eDate As DateTime
DECLARE @cDate As DateTime
SET @sDate='Start Date Goes Here'
SET @eDate='End Date Goes Here'
SET @cDate=@sDate
WHILE @cDate;<@eDate
BEGIN
    INSERT INTO @dates (dTime) VALUES (@cDate)
    SET @cDate=DATEADD(day,1,@cDate)
END

SELECT 
    dt.dTime     'DAY',
    sp.Something 'Something'
FROM
    @dates dt
    LEFT OUTER JOIN Sparse sp ON dt.dTime  = sp.dTime 
ORDER BY dt.dTime

The solution above creates a table variable @dates and populates it with every day between the start and end date of the report. It then outer joins this enumeration of possible dates with my sparse table (I have simplified this compared to the original report code). In tests, even populating this table variable with a hundred years' worth of days took no appreciable time and so the solution is simple, robust and efficient.

Table variables as accumulators

The next use of table variables I shall discuss is as 'accumulators'. Here we want to accumulate the results of several queries into one result set. To be purest, this should be done with joins. However, the resultant SQL can often become unmanageably complex and/or impossibly slow. The pattern I have used works like this:

1. Create a table variable
2. Select into it
3. Update the table via a joined update
4. Update the table via a different joined update
5. ...repeat as required...
6. SELECT the table variable to get the accumulated results

Table variables are great for this because normally tables are very expensive to update, but the non transactional table variables are very cheap to update. This makes for a manageable and highly efficient approach. Here is a real example of this approach being run. I have included the full code so that you can easily see why trying to do the whole thing in one SQL query would be unmanageable:

DECLARE @proj     BIGINT
DECLARE @busnUnit BIGINT
DECLARE @locn     BIGINT
DECLARE @sDate      DATETIME
DECLARE @eDate      DATETIME

SET @proj     = ?N
SET @busnUnit = ?N
SET @locn     = ?N
SET @sDate    = ?S
SET @eDate    = ?S

EXEC P_SetCrntProj @proj
EXEC P_SetCrntLang 'EN'

DECLARE @Rept TABLE
(
    MchnId   BIGINT,
    MchnLocnDscr       VARCHAR(1024),
    MchnGUID           VARCHAR(1024),
    UserId             BIGINT,
    FrstName           VARCHAR(50),
    LastName           VARCHAR(50),
    BndlTypeLocnOK     VARCHAR(5),
    BndlTypeBusnUnitOK VARCHAR(5),
    BndlTypeLBUOK      VARCHAR(5),
    BndlTypeSlotCataOK VARCHAR(5),
    AsscSlotsOK        VARCHAR(5),
    AsscSlotCataOK     VARCHAR(5),
    ReadyToBook        VARCHAR(5)
)

INSERT INTO @Rept SELECT
    m.MchnId,
    dbo.F_GetLocnTreeDscr(m.LocnId),
    m.GUID,
    u.UserId,
    u.FrstName,
    u.LastName,
    'FALSE',
    'FALSE',
    'FALSE',
    'FALSE',
    'FALSE',
    'FALSE',
    'FALSE'
FROM
    T_Mchn m
    INNER JOIN T_User u               ON u.UserId = m.UserId
    INNER JOIN T_UserBusnUnit ubu     ON ubu.UserId = u.UserId
    INNER JOIN V_CrntBusnUnitPrnt bup ON bup.BusnUnitId = ubu.BusnUnitId 
    INNER JOIN V_CrntLocnPrnt lp      ON lp.LocnId = m.LocnId
WHERE
    ProjId   = dbo.F_GetCrntProj() AND
    lp.Prnt  = @locn AND
    bup.Prnt = @busnUnit   

UPDATE @Rept 
SET BndlTypeLocnOK='TRUE'
WHERE
    MchnId IN
    (
    SELECT
        r.MchnId
    FROM
        T_bndlTypeBULocn  btbul
        INNER JOIN V_CrntLocnPrnt lp ON btbul.LocnId = Prnt
        INNER JOIN T_Mchn m          ON lp.LocnId    = m.LocnId
        INNER JOIN @Rept  r          ON r.MchnId     = m.Mchnid
    WHERE
        ProjId=dbo.F_GetCrntProj()
    )

IF @@ROWCOUNT = 0 GOTO QUICK_RETURN

UPDATE @Rept
SET BndlTypeBusnUnitOK='TRUE'
WHERE
    MchnId IN
    (
    SELECT
        r.MchnId
    FROM
    T_Mchn m
    INNER JOIN T_User u               ON u.UserId = m.UserId
    INNER JOIN T_UserBusnUnit ubu     ON ubu.UserId = u.UserId
    INNER JOIN V_CrntBusnUnitPrnt bup ON bup.BusnUnitId = ubu.BusnUnitId
    INNER JOIN T_bndlTypeBULocn btbul ON bup.Prnt = btbul.BusnUnitId
    INNER JOIN T_bndlType bt          ON bt.BndlTypeId = btbul.BndlTypeId
    INNER JOIN @Rept  r               ON r.MchnId     = m.Mchnid
    WHERE
        m.ProjId=dbo.F_GetCrntProj() AND
        bt.ProjId=dbo.F_GetCrntProj()
    )

IF @@ROWCOUNT = 0 GOTO QUICK_RETURN

UPDATE @Rept
SET BndlTypeLBUOK='TRUE'
WHERE
    MchnId IN
    (
    SELECT
        r.MchnId
    FROM
        T_bndlTypeBULocn  btbul
        INNER JOIN V_CrntLocnPrnt lp       ON btbul.LocnId = Prnt
        INNER JOIN T_Mchn m                ON lp.LocnId    = m.LocnId
        INNER JOIN T_User u                ON u.UserId = m.UserId
        INNER JOIN T_UserBusnUnit ubu      ON ubu.UserId = u.UserId
        INNER JOIN V_CrntBusnUnitPrnt bup  ON 
            bup.BusnUnitId = ubu.BusnUnitId AND 
            bup.Prnt       = btbul.BusnUnitId
        INNER JOIN T_bndlType bt           ON bt.BndlTypeId = btbul.BndlTypeId
        INNER JOIN @Rept  r                ON r.MchnId     = m.Mchnid
    WHERE
        m.ProjId=dbo.F_GetCrntProj() AND
        bt.ProjId=dbo.F_GetCrntProj()
    )

IF @@ROWCOUNT = 0 GOTO QUICK_RETURN

UPDATE @Rept
SET BndlTypeSlotCataOK='TRUE'
WHERE
    MchnId IN
    (
    SELECT
        r.MchnId
    FROM
        T_bndlTypeBULocn  btbul
        INNER JOIN V_CrntLocnPrnt lp      ON btbul.LocnId = Prnt
        INNER JOIN T_Mchn m               ON lp.LocnId    = m.LocnId
        INNER JOIN T_User u               ON u.UserId = m.UserId
        INNER JOIN T_UserBusnUnit ubu     ON ubu.UserId = u.UserId
        INNER JOIN V_CrntBusnUnitPrnt bup ON 
            bup.BusnUnitId = ubu.BusnUnitId AND 
            bup.Prnt       = btbul.BusnUnitId
        INNER JOIN T_BndlTypeSlotCata btsc ON btsc.BndlTypeId = btbul.BndlTypeId
        INNER JOIN T_bndlType bt           ON bt.BndlTypeId = btbul.BndlTypeId
        INNER JOIN @Rept  r                ON r.MchnId     = m.Mchnid
    WHERE
        m.ProjId=dbo.F_GetCrntProj() AND
        bt.ProjId=dbo.F_GetCrntProj()
    )    

IF @@ROWCOUNT = 0 GOTO QUICK_RETURN

UPDATE @Rept
SET AsscSlotsOK='TRUE'
WHERE
    MchnId IN
    (
    SELECT
        r.MchnId
    FROM
        T_bndlTypeBULocn  btbul
        INNER JOIN V_CrntLocnPrnt lp       ON btbul.LocnId = Prnt
        INNER JOIN T_Mchn m                ON lp.LocnId    = m.LocnId
        INNER JOIN T_User u                ON u.UserId = m.UserId
        INNER JOIN T_UserBusnUnit ubu      ON ubu.UserId = u.UserId
        INNER JOIN V_CrntBusnUnitPrnt bup  ON 
            bup.BusnUnitId = ubu.BusnUnitId AND 
            bup.Prnt       = btbul.BusnUnitId
        INNER JOIN T_BndlTypeSlotCata btsc ON btsc.BndlTypeId = btbul.BndlTypeId
        INNER JOIN V_CrntLocnPrnt lp1      ON lp1.LocnId = m.LocnId
        INNER JOIN V_CrntBusnUnitPrnt bup1 ON bup1.BusnUnitId = ubu.BusnUnitId
        INNER JOIN T_Slot s                ON 
            lp1.Prnt  = s.LocnBabl AND 
            bup1.Prnt = s.BusnUnitId
        INNER JOIN T_bndlType bt           ON bt.BndlTypeId = btbul.BndlTypeId
        INNER JOIN @Rept  r                ON r.MchnId     = m.Mchnid
    WHERE
        s.ProjId=dbo.F_GetCrntProj()  AND
        m.ProjId=dbo.F_GetCrntProj()  AND
        bt.ProjId=dbo.F_GetCrntProj() AND
        s.dTime     < @eDate          AND
        NOT s.dTime < @sDate
    )    

IF @@ROWCOUNT = 0 GOTO QUICK_RETURN

UPDATE @Rept
SET AsscSlotCataOK='TRUE',ReadyToBook='TRUE'
WHERE
    MchnId IN
    (
    SELECT
        r.MchnId
    FROM
        T_bndlTypeBULocn  btbul
        INNER JOIN V_CrntLocnPrnt lp       ON btbul.LocnId = Prnt
        INNER JOIN T_Mchn m                ON lp.LocnId    = m.LocnId
        INNER JOIN T_User u                ON u.UserId = m.UserId
        INNER JOIN T_UserBusnUnit ubu      ON ubu.UserId = u.UserId
        INNER JOIN V_CrntBusnUnitPrnt bup  ON 
            bup.BusnUnitId = ubu.BusnUnitId AND 
            bup.Prnt       = btbul.BusnUnitId
        INNER JOIN T_BndlTypeSlotCata btsc ON btsc.BndlTypeId = btbul.BndlTypeId
        INNER JOIN V_CrntLocnPrnt lp1      ON lp1.LocnId = m.LocnId
        INNER JOIN V_CrntBusnUnitPrnt bup1 ON bup1.BusnUnitId = ubu.BusnUnitId
        INNER JOIN T_Slot s                ON 
            lp1.Prnt      = s.LocnBabl AND 
            bup1.Prnt     = s.BusnUnitId AND
            s.SlotCata    = btsc.SlotCataId
        INNER JOIN T_bndlType bt           ON bt.BndlTypeId = btbul.BndlTypeId
        INNER JOIN @Rept  r          ON r.MchnId     = m.Mchnid
    WHERE
        s.ProjId=dbo.F_GetCrntProj() AND
        m.ProjId=dbo.F_GetCrntProj() AND
        bt.ProjId=dbo.F_GetCrntProj() AND
        s.dTime < @eDate              AND
        NOT s.dTime < @sDate
    )    

QUICK_RETURN:

SELECT
    MchnId              'Machine Id',
    MchnLocnDscr        'Machine Location',
    MchnGUID            'Machine Identifier',
    UserId              'User Id',
    FrstName            'First Name',
    LastName            'Last Name',
    BndlTypeLocnOK      'BndlType Locn OK',
    BndlTypeBusnUnitOK  'BndlType UserGroup OK',
    BndlTypeLBUOK       'BndlType Locn/UserGroup OK',
    BndlTypeSlotCataOK  'BndlType SlotCate OK',
    AsscSlotsOK         'Assoc Slots OK',
    AsscSlotCataOK      'Assoc Slots/SlotCate OK',
    ReadyToBook         'Ready To Book'
FROM 
    @Rept

Letting data live through ROLLBACK

The final pattern I am going to discuss here is letting table data live through a ROLLBACK. Sometimes we might want to return a record set from inside a sequence of steps even though the whole sequence was rolled back. This might seem odd, so please consider the following example:

You have a set of operations that work on a lot of records, maybe even with a cursor. Sometimes a complex business rule requires that the whole lot gets rolled back. How do you know what went off in the set of steps to cause the rollback to occur?

1. Create a table variable @LOG which stores log text for each step and maybe some other key data about the step.
2. In each step, INSERT a row into @LOG.
3. When rollback occurs, INSERT another row in @LOG giving the reason for the rollback.
4. ROLLBACK
5. SELECT * FROM @LOG

All the code calling this batch has to do is interrogate the returned record set. If its last record is the one recording the ROLLBACK, then it knows something went wrong and can alert the user somehow, giving the full log of what happened. You cannot do this with a temporary table because temporary tables are transactional so the rollback will erase the log.

History

• 14^th January, 2010: Initial post