Click here to Skip to main content
15,867,704 members
Articles / Database Development / SQL Server

Three Really Handy Tricks With SQL Server Table Variables

Rate me:
Please Sign up or sign in to vote.
4.95/5 (13 votes)
14 Jan 2010CC (ASA 2.5)5 min read 74.9K   21   12
They might seem like just another not entirely required feature, but actually table variables are a very handy extension to SQL Server

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.

SQL
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:

SQL
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

  • 14th January, 2010: Initial post

License

This article, along with any associated source code and files, is licensed under The Creative Commons Attribution-ShareAlike 2.5 License


Written By
Web Developer
United Kingdom United Kingdom
I am now a Software Systems Developer - Senior Principal at Micro Focus Plc. I am honoured to work in a team developing new compiler and runtime technology for Micro Focus.

My past includes a Ph.D. in computational quantum mechanics, software consultancy and several/various software development and architecture positions.

For more - see

blog: http://nerds-central.blogspot.com

twitter: http://twitter.com/alexturner

Comments and Discussions

 
QuestionVery good article, thank you Alex! Pin
gabrielgl17-Nov-14 18:44
gabrielgl17-Nov-14 18:44 
QuestionWhat with nested procedures? Pin
Wojciech Jakubas23-Jan-12 2:55
Wojciech Jakubas23-Jan-12 2:55 
GeneralMy vote of 5 Pin
fr922022-Dec-10 9:24
fr922022-Dec-10 9:24 
Generalfew comments on a good article Pin
Michael H Lutz15-Jan-10 10:56
Michael H Lutz15-Jan-10 10:56 
Alex nice article, you've made me think of Table Variables in a new light!

Just wanted to mention a few things ....

The opening couple paragraphs in the article imply that a table variable is only stored in memory. This is not the case, table variables are stored in the buffer cache as are permanent tables and also you can see entries for table variables in tempdb. SQL Server can choose to flush table variable memory pages to disk identically to temp tables. Itzik-Ben Gan discussed this in a series of articles in SQL Server magazine a while back. Your article is correct that table variables don't participate in user transactions. Another major difference between table variables and temp tables is that indexes on table variables don't have statistics. It is possible to create indexes on table variables by either creating a PRIMARY KEY (which can be composite) or a UNIQUE constraint on the table variables, both of which must be done inline in the table variable definition. But these indexes will not have statistics on table variables.

In your second scenario where you update the table variable, performance is good here for you because you don't have very large # of rows in the table variable. When you are joining back to the table variable to update it remember there are no statistics on the table variable (and in your case no indexes either). Also, this practice of updating a table is often not the best approach for performance in general when dealing with large numbers of rows in the table being updated. Updates are expenesive relative to inserts particularly if the updates are widening the row. SQL Server handles updates differently depending on whether the table is a heap or a clustered table when the new row/page doesn't fit back into its original location. Either way, if the updated row is wider (e.g. a NULL varchar field is replaced with a non-NULL value) this can cause page splits in a clustered table or forwarding pointers in a heap. Both are expensive. So when dealing with large #'s of rows it's sometimes best to iteratively insert into a new table that grows progressively wider as you add columns with known values as opposed to turning around and updating the same table variable / temp table over and over again.

Nice idea about using a table variable to log progress of a SP. I've heard about this before but your example illustrates the point more clearly. In an OLTP application that manages transactions in the application tier this @log table variable whould have to be passed back to the application to yield utility.

One major performance advantage of table variables over temp tables is that table variables don't have statistics. In highly concurrently OLTP systems, temp tables can cause performance bottlenecks due to SP recompilation. Table Variables avoid this issue, but they introduce the issue of not having statistics which can cause subsuquent queries against the table variable to run slowly.

Just some thoughts. I really liked your article thanks for taking time to share your thoughts!

Mike
GeneralRe: few comments on a good article Pin
alex turner17-Jan-10 6:42
alex turner17-Jan-10 6:42 
GeneralWhile Planes are cool... Pin
JohnGalt1714-Jan-10 7:31
JohnGalt1714-Jan-10 7:31 
GeneralRe: While Planes are cool... Pin
alex turner14-Jan-10 21:48
alex turner14-Jan-10 21:48 
GeneralMy vote of 1 Pin
Baixinho14-Jan-10 3:42
Baixinho14-Jan-10 3:42 
GeneralRe: My vote of 1 Pin
alex turner14-Jan-10 3:44
alex turner14-Jan-10 3:44 
GeneralRe: My vote of 1 Pin
alex turner14-Jan-10 3:46
alex turner14-Jan-10 3:46 
GeneralRe: My vote of 1 Pin
CARPETBURNER14-Jan-10 3:56
CARPETBURNER14-Jan-10 3:56 
GeneralRe: My vote of 1 Pin
alex turner14-Jan-10 3:57
alex turner14-Jan-10 3:57 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.