How to create and use table variables in T-SQL
Part three of a three-part series of blogs

Table variables allow you to hold rows of data in temporary storage, without any processing overhead. Read this blog to add this technique to your SQL armoury!

  1. Table Variables in SQL
  2. Worked Example of a Table Variable
  3. Pros and cons of table variables (this blog)

This blog is part of our full online SQL tutorial.  For a more personal experience, have a look at our SQL training courses for businesses.

Posted by Andy Brown on 08 February 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Pros and cons of table variables

Should you use table variables?  Here are some factors which may help you decide.

Advantages of table variables

The advantages of table variables include:

Advantage Notes
Speed Table variables don't require locking and logging resources, nor do they have to be stored in a database (although see hinit below).  For this reason, they will run more quickly than temporary tables.
Work with UDFs It's only a tiny point, but ... you can insert, update and delete records in table variables within user-defined functions (something which isn't true of temporary tables).

So much for the pros - what are the cons?  I can think of 3, which follow under separate headings below.

Actually, there's a bit more to the storage of table variables - as set out here.

Disadvantage of table variables - limited scope

You can't reference a table variable from outside of its scope.  For example, the following SQL will fail:

-- create a procedure to create a table variable

CREATE PROC spCreateOldHands

AS

 

DECLARE @OldHands TABLE (

PersonName varchar(50),

PersonRole varchar(50),

BirthDate datetime

)

GO

 

-- now try using it!

spCreateOldHands

 

-- get the directors

INSERT INTO @OldHands

SELECT

DirectorName AS PersonName,

'Director' AS PersonRole,

DirectorDob AS BirthDate

FROM

tblDirector

WHERE

DirectorDob <>'19140101'

The message you will get when you run this is:

Table variable not declared - message 1087

The table variable is only visible in the procedure spCreateOldHands.

 

Disadvantage of table variables - must join using aliases

I'm not convinced this is a disadvantage at all, but you can't join to a table variable by its name.  This SQL will fail:

-- link table variable to directors table

SELECT

PersonName,

tblDirector.DirectorGender

FROM

@OldHands

LEFT OUTER JOIN tblDirector ON

@OldHands.PersonName = tblDirector.DirectorName

ORDER BY

PersonName

The message you will get is this:

Message 137 - must declare scalar variable

You can't use a table variable like this.

 

However, if you always use aliases, you'll never see this problem.  So the following would work fine:

-- link table variable to directors table

SELECT

oh.PersonName,

d.DirectorGender

FROM

@OldHands as oh

LEFT OUTER JOIN tblDirector as d ON

oh.PersonName = d.DirectorName

ORDER BY

oh.PersonName

Disadvantage of table variables - you can't change them once created

Once you've declared a table variable, you can't add, rename or delete columns - nor can you create them on the fly using the SELECT INTO command:

DECLARE @OldHands TABLE (

PersonName varchar(50),

PersonRole varchar(50),

BirthDate datetime

)

-- get the directors

SELECT

DirectorName AS PersonName,

'Director' AS PersonRole,

DirectorDob AS BirthDate

INTO

@OldHands

FROM

tblDirector

WHERE

DirectorDob <>'19140101'

The above SQL will fail, because you've already created the @OldHands table variable, so can't recreate it using the INTO keyword.

If pushed for a recommendation, I think my own personal preferences are to work with common table expressions and (especially) table-valued functions, rather than table variables and temporary tables, but there's a place for all of these similar techniques within SQL.

  1. Table Variables in SQL
  2. Worked Example of a Table Variable
  3. Pros and cons of table variables (this blog)
This blog has 0 threads Add post