This page has 0 threads | Add post

Finding our blogs useful? Please consider helping us to share more!

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! Table Variables in SQL Worked Example of a Table Variable 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.

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!

Table Variables in SQL
Worked Example of a Table Variable
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

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

DECLARE @OldHands TABLE (

PersonName varchar(50),

PersonRole varchar(50),

BirthDate datetime

)

-- 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.

Parts of this blog
Table Variables in SQL Worked Example of a Table Variable Pros and cons of table variables (this blog)

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

Google Sheets

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Pros and cons of table variables

Advantages of table variables

Disadvantage of table variables - limited scope

Disadvantage of table variables - must join using aliases

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

Head office

London

Manchester