The previous part of this tutorial explained how to use temporary tables - it's time now to look at their sibling, table variables.
I'll discuss later the pros and cons of table variables, but it's largely a matter of personal preference whether you use them instead of or as well as temporary tables. I like table variables because you never have to worry whether they already exist when you create them!
Syntax of Temporary Variables
In a previous tutorial I showed that you can declare variables as follows:
DECLARE @VariableName VariableType
where VariableType can be any of the standard data types in SQL. So the following are examples of variables:
DECLARE @LifeAnswer int = 42
DECLARE @AlternativeAnswer varchar(50) = 'Forty-two'
However, you can also declare a variable to refer to an entire table. The syntax for this is as follows:
DECLARE @TableName TABLE (
FirstColumn datatype,
...
LastColumn datatype
)
Why would you want to do this? Let's look at a worked example, showing how you could solve it with and without a table variable.
Regular readers will know that many of our SQL examples relate to a simple Movies database, containing a small sample of famous films, with their directors, studios, etc.
Imagine now that you've been asked to draw up a list of experienced people in the film industry, to whose works you can turn for advice (no matter that the people themselves might no longer be alive).
Avoiding a table variable using a UNION query
As is nearly always the case, you don't have to use a table variable at all for our example; you could just use a UNION keyword:
SELECT
DirectorName AS PersonName,
'Director' AS PersonRole,
DirectorDob AS BirthDate
FROM
tblDirector
WHERE
DirectorDob <>'19140101'
UNION ALL
SELECT
a.ActorName,
'Actor',
ActorDob
FROM
tblActor AS a
WHERE (
SELECT COUNT(*) FROM tblCast AS c
WHERE a.ActorId=c.CastActorId
) >= 9
For the Wise Owl database, running this would give 3 directors and 2 actors:
Clearly the Wise Owl database doesn't have enough Meryl Streep films in!
However, what if you now want to perform further processing based on this list of "old hands"? You could create a common table expression, or CTE:
WITH OldHands AS (
SELECT
DirectorName AS PersonName,
'Director' AS PersonRole,
DirectorDob AS BirthDate
FROM
tblDirector
WHERE
DirectorDob <>'19140101'
UNION ALL
SELECT
a.ActorName,
'Actor',
ActorDob
FROM
tblActor AS a
WHERE (
SELECT COUNT(*) FROM tblCast AS c
WHERE a.ActorId=c.CastActorId
) >= 9
)
SELECT
PersonName,
PersonRole,
CONVERT(char(10),BirthDate,103) AS DOB
FROM
OldHands
ORDER BY
PersonName
However, a CTE only allows you to manipulate the table immediately after it has been created. We want a longer-lasting access method. One solution is to create a table variable.
Creating and using a table variable
One way to solve the above problem which produces SQL which is easy to write, understand and maintain is to store the rows in a table variable. The first thing to do is to create this (you can read more on creating tables in SQL here):
DECLARE @OldHands TABLE (
PersonName varchar(50),
PersonRole varchar(50),
BirthDate datetime
)
You can now put the old directors into this table (you can see more about inserting bulk rows using SQL here):
INSERT INTO @OldHands
SELECT
DirectorName AS PersonName,
'Director' AS PersonRole,
DirectorDob AS BirthDate
FROM
tblDirector
WHERE
DirectorDob <>'19140101'
Now it's time to add in the experienced actors:
INSERT INTO @OldHands
SELECT
a.ActorName,
'Actor',
ActorDob
FROM
tblActor AS a
WHERE (
SELECT COUNT(*) FROM tblCast AS c
WHERE a.ActorId=c.CastActorId
) >= 9
And finally, we can manipulate the results:
SELECT
PersonName,
PersonRole,
CONVERT(char(10),BirthDate,103) AS DOB
FROM
@OldHands
ORDER BY
PersonName
The key point is that the @OldHands table will retain the records for the lifetime of this SQL batch (ie until a GO statement is reached).
Now we've seen what table variables are (and how to use them), the final part of this blog will consider their pros and cons.
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 PROC spCreateOldHands
AS
DECLARE @OldHands TABLE (
PersonName varchar(50),
PersonRole varchar(50),
BirthDate datetime
)
GO
spCreateOldHands
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:
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:
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:
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:
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
)
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.
