Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Pros and cons of six SQL table tools

By Andy Owl,

One of the most confusing things about learning SQL is that there are so many ways to do the same thing!  Should you be using temporary tables?  Or table variables?  Or common table expressions?  This article gives 6 different ways to do the same simple thing in SQL, and considers the pros and cons of each.

The example we'll use

You can download and run the simple script attached to this article to generate a simple database.  What you'll get are two tables: one of actors, and one of movie directors:

What I want to show is how to write a simple script to show the names of all of the actors and directors who are female.  The results of running the query in every case would be as follows:

I'm well aware, by the way, that the easiest way to do this would be to use a UNION query, but the point of this article is to show the various techniques available for working with sets of data in Transact-SQL.

The Six Methods

The 6 different techniques this article will show are:

  • Temporary tables
  • Table variables
  • Inline table-valued functions
  • Multi-statement table-valued functions
  • Derived tables
  • Common table expressions (CTEs)

What I'll try to do is to show the pros and cons of each technique, so that you can decide for yourself which one is for you.

Solution 1 - Temporary Tables

I like temporary tables because they're simple to understand: they work in virtually every respect exactly like normal tables.  Here's a query which would solve the above problem:

-- first delete any left over temporary table
BEGIN TRY
 DROP TABLE #females
END TRY

BEGIN CATCH

 -- if table didn't exist, nothing to do here
 
END CATCH

-- put the female directors into a table
SELECT
 DirectorName AS PersonName,
 'Director' AS Job,
 DirectorDob AS Dob
INTO
 #females
FROM
 tblDirector
WHERE
 DirectorGender = 'Female'
 
-- now add in the female actors
INSERT INTO
 #females
SELECT
 ActorName AS PersonName,
 'Actor' AS Job,
 ActorDob AS Dob
FROM
 tblActor
WHERE
 ActorGender = 'Female'
 
-- finally, show the results
SELECT * FROM #females

This would create a temporary table (they always begin with a prefix of #) in the system TEMPDB folder, populate it and show the results.  Here are the advantages:

Advantage Details
Simplicity Because temporary tables work just like normal ones, you can sort, filter and join them just like normal tables.
Speed SQL Server doesn't have as many logging and locking overheads for temporary tables, so they run more quickly.
Security You can do what you like to a temporary table, without worrying about making changes to permanent tables.

Against this are the disadvantages:

Disadvantage Details
Not that fast While temporary tables are faster than permanent ones, they're still possibly not as fast as table variables (see below).
Limitations You can't use INSERT, UPDATE or DELETE statements against temporary tables in user-defined functions.

For further reading, see how to create (and drop) SQL temporary tables at our blog, or have a look at an explanation of the differences between the various types of table..

Solution 2 - Table Variables

An alternative way to solve the above problem is to create a special sort of variable: one to hold a table, rather than a scalar value.  Here's some code which would do this:

-- declare the table variable
DECLARE @people TABLE (
 PersonId int IDENTITY(1,1) PRIMARY KEY,
 PersonName varchar(20),
 Job varchar(10),
 Dob datetime
)

-- add in the female directors 
INSERT INTO
 @people
SELECT
 DirectorName AS PersonName,
 'Director' AS Job,
 DirectorDob AS Dob
FROM
 tblDirector
WHERE
 DirectorGender = 'Female'
  
-- now add in the female actors
INSERT INTO
 @people
SELECT
 ActorName AS PersonName,
 'Actor' AS Job,
 ActorDob AS Dob
FROM
 tblActor
WHERE
 ActorGender = 'Female'
 
-- finally, show the results
SELECT * FROM @people

This is nice and easy to understand: create a table in memory and then add records into it.  Table variables have good points:

Advantage Details
Speed Because table variables exist only in memory, there should in theory be no locking or logging overhead for SQL Server - they therefore run quickly.  However, table variables still write information to and read from the TEMPDB database, so the performance gain is actually illusory.
Security You can do what you like to the rows of a table variable, without having to worry about whether you're corrupting your company's data.

... and bad points:

Disadvantage Details
Scope A table variable is limited in scope (so, for example, you can't refer to a table variable in one stored procedure called from another which has declared it).
Finicky joining If you want to join a table variable to another table, yiou must first give it an alias (it's easy to forget this if you're not in the habit of giving all of your tables aliases).
No SELECT INTO You can't select records into a table variable - you must define it first and then use INSERT INTO.

For me, temporary tables are easier to understand and to use, but I've no doubt a chorus of comments will tell me why I'm wrong!

Solution 3 - Inline Table-Valued Function

An inline table-valued function is just that: a function which instead of returning a single value returns a set of records.  You could use the following code to create 2 table-valued functions and then call them:

-- create function to show a list of directors
CREATE FUNCTION fnDirectors(

 -- the gender we're interested in
 @Gender varchar(20)
)
RETURNS TABLE
AS
RETURN

 -- the only thing which can go in an inline TVF is a single
 -- SELECT statement
 SELECT
  DirectorName AS PersonName,
  'Director' AS Job,
  DirectorDob AS Dob
 FROM
  tblDirector
 WHERE
  DirectorGender = @Gender
GO

-- create another function to get a list of actors
CREATE FUNCTION fnActors(

 -- the gender we're interested in
 @Gender varchar(20)
)
RETURNS TABLE
AS
RETURN

 -- again, the only thing which can go in an inline TVF is a single
 -- SELECT statement
 SELECT
  ActorName AS PersonName,
  'Actor' AS Job,
  ActorDob AS Dob
 FROM
  tblActor
 WHERE
  ActorGender = @Gender

GO

-- now show results using a UNION query (we didn't really need the function)
SELECT * FROM dbo.fnDirectors('Female')
UNION ALL
SELECT * FROM dbo.fnActors('Female')

Although this is a bit of a silly example (it's hard to see what we've gained by using of using table-valued functions in this case), they are a good alternative to views and stored procedures.  We have standard TVFs (that's what you should call them to impress people) to show, for example:

  • A list of all the courses we run between two given dates; and
  • A list of all the delegates for a given course id number.

We'll consider the pros and cons of inline table-valued functions after we've looked at their cousins: multi-statement table-valued functions, or MSTVFs (if ever something needed a different name, this is it). 

Solution 4 - Multi-Statement Table-Valued Functions

A multi-statement table-valued function - aside from being a bit of a mouthful - returns a set of records given some initial input parameters.  The difference is that the function can perform a whole range of processing within it, and in this respect is like a cross between a table variable and a stored procedure.

Here's an example of an MSTVF to solve our problem:

CREATE FUNCTION fnPeople(

 -- pass in the gender: M or F
 @gender varchar(10)
)

-- spit out a table containing the people
RETURNS @people TABLE (
 PersonId int IDENTITY(1,1) PRIMARY KEY,
 PersonName varchar(20),
 Job varchar(10),
 Dob datetime
)

AS

BEGIN

 -- add in the female directors 
 INSERT INTO
  @people
 SELECT
  DirectorName AS PersonName,
  'Director' AS Job,
  DirectorDob AS Dob
 FROM
  tblDirector
 WHERE
  DirectorGender = @gender
   
 -- now add in the female actors
 INSERT INTO
  @people
 SELECT
  ActorName AS PersonName,
  'Actor' AS Job,
  ActorDob AS Dob
 FROM
  tblActor
 WHERE
  ActorGender = @gender
  
 RETURN
 
END

GO

SELECT * FROM dbo.fnPeople('Female')

This function:

  • Creates what is essentially a table variable in memory
  • Populates this with the female (or male) directors and actors; then
  • Spits this set of records back out

Table functions are great when you frequently want to return the same set of records, but with varying input parameters.  For example:

  • All of the accounts for a particular region of a country; or
  • All of the products for a particular sales manager

They do, however, have a number of limitations that you should be aware of:

Disadvantage Details
No side effects A function can't insert, update or delete rows in permanent tables.
No stored procedures A function can't call a standard stored procedure (although it can call extended stored procedures and other functions).
Deterministic functions only A function can't call certain non-deterministic functions (ie ones whose return value isn't predictable) such as the rand function to generate a random number.
No temporary tables You can't use temporary tables within a user-defined function.
Limited error trapping You can't use TRY / CATCH clausee in functions that you create.

Although the limitations of functions aren't particularly onerous, it's sometimes easier to do everything in a stored procedure.  You can see a detailed comparison of the speeds of ITVFs and MSTVFs here.

Solutions 5 and 6 - Derived Tables and CTEs

These two solutions are essentially the same thing: Common Table Expressions (CTEs) were introduced in SQL Server 2005, and make derived tables more readable.

Here's a solution to our problem using a common table expression (and a UNION query, rendering this solution a bit more long-winded than it needs to be!):

-- get list of female directors
WITH FemaleDirectors AS (
 SELECT
  DirectorName AS PersonName,
  'Director' AS Job,
  DirectorDob AS Dob
 FROM
  tblDirector
 WHERE
  DirectorGender = 'Female'
)

SELECT * FROM FemaleDirectors
UNION ALL

SELECT
 ActorName AS PersonName,
 'Actor' AS Job,
 ActorDob AS Dob
FROM
 tblActor
WHERE
 ActorGender = 'Female'

Here's the same solution, but using a derived table:

SELECT * FROM (
 SELECT
  DirectorName AS PersonName,
  'Director' AS Job,
  DirectorDob AS Dob
 FROM
  tblDirector
 WHERE
  DirectorGender = 'Female'
  
 -- you have to give the derived table an alias
 
) AS FemaleDirectors

UNION ALL

SELECT
 ActorName AS PersonName,
 'Actor' AS Job,
 ActorDob AS Dob
FROM
 tblActor
WHERE
 ActorGender = 'Female'

In both cases we've created a temporary set of records on-the-fly, given it an alias of FemaleDirectors, and then referred to this in a subsequent SELECT statement.

I would use CTEs in preference to derived tables any time, since they read far more like English.  However, there are a few things to watch out for - more quirks than disadvantages:

Quirk Details
Semi-colons If the CTE isn't the first statement in a batch, the previous statement must end with ; (ie a semi-colon).
Brackets You must enclose both a CTE and a derived table in parentheses.
Short life span A CTE must be followed immediately by a SELECT statement using it; CTEs don't have a long life!

You can see more about this subject - including how to create recursive CTEs - at our CTEs SQL training page, or a techier article on CTEs on the Microsoft website.

Conclusions

The main conclusion to this article is that there aren't any conclusions!  What I've tried to do is to show in a single article the range of table-based tools available in SQL, so programmers can choose their own.  I've used every one of these in anger apart from derived tables (which I always substitute with CTEs), so they all have their niches. 

Happy programming!

Resources:

Example database.sql | OUTPUT.jpg
Total article views: 8619 | Views in the last 30 days: 1
 
Related Articles
BLOG

SQL Table variable and temporary table

Table variables and Temporary tables  are used interchangeably – but they were designed for differen...

ARTICLE

Comparing Table Variables with Temporary Tables

This articles brings a comparison of temporary tables with table variables from SQL Server author, W...

FORUM

Temporary variables in SQL

Improve the SQL query performance with temporary variables

ARTICLE

Local Temporary Tables and Table Variables

Peter He examines the differences between temporary tables and table variables in SQL Server, showin...

FORUM

Select from Variable Table Name under Function.

Fail to select an input table name under a Function !

Tags
common table expression    
cte    
derived table    
mstvf    
table variables    
table-valued function    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones