Introduction

Aside from the physical tables that reside in a database, there are a several other types of tables that database developers should become intimately familiar with. If your goal as a developer is simply to provide an environment for the storage and retrieval of data, then utilizing physical tables will work just fine. If you're tasked with ensuring SQL queries and stored procedures are highly optimized for performance then you should consider experimenting with temporary tables, table variables, and derived tables.

Temporary Tables

There are 2 types of temporary tables, local and global. Local temporary tables are created using a single pound (#) sign and are visible to a single connection and automatically dropped when that connection ends. Global temporary tables are created using a double pound (##) sign and are visible across multiple connections and users and are automatically dropped when all SQL sessions stop referencing the global temporary table.

How to Create:

CREATE TABLE #MyTempTable
(
 PolicyId INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
 LastName VARCHAR(50) NOT NULL 
)

How to Use:

INSERT INTO #MyTempTable
SELECT PolicyId, LastName 
FROM dbo.Policy
WHERE LastName LIKE 'A%'

Table Variables

Existing in memory for the duration of a single T-SQL batch, table variables are declared using syntax similar to local variable declaration.

How to Create:

DECLARE @MyTableVariable TABLE 
(
 PolicyId INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
 LastName VARCHAR(50) NOT NULL 
)

How to Use:

INSERT INTO @MyTableVariable
SELECT PolicyId, LastName 
FROM dbo.Policy
WHERE LastName LIKE 'A%'

Once a GO command is issued from Query Analyzer, the table variable is no longer in scope for any SQL statement issued afterwards. A table variable can be created at the beginning of a stored procedure and referenced throughout since everything declared within the stored procedure remains in scope throughout the lifetime of the stored procedure execution.

Derived Tables

Derived Tables are similar to table variables in that they exist in memory only but they differ in how they are created and used. A derived table is created when a SELECT query is given a name and joined to from another table. Performance benefits of using a derived table can be quite substantial in many situations and should be considered whenever possible. Derived tables exist only for the duration of a single T-SQL statement and although they can be referenced multiple times (through joins and predicates), they cannot be referenced outside the T-SQL statement in which they are created.

Example:
Let's assume we have a table called PolicyVehicle that contains one record for each vehicle on an auto insurance policy with a reference back to the Policy table via the PolicyId foreign key column. Although we could use a simple query to get the newest vehicle and associated policy information, for the sake of this example we will use a derived table to first retrieve the newest Vehicle for each Policy, alias that derived table with the name 'NewestVehicle', and then join to the Policy table to retrieve the PolicyId and LastName columns.

SELECT P.PolicyId, LastName, NewestVehicle
FROM Policy P
INNER JOIN 
(
     SELECT PolicyId,
            MAX(VehicleYear) AS NewestVehicle 
     FROM PolicyVehicle PV
     GROUP BY PolicyId
) MaxVehicles ON P.PolicyId = MaxVehicles.PolicyId

The derived table portion is in bold and it simply returns a virtual table containing PolicyIds along with the highest (max) year of any vehicle (aliased as 'NewestVehicle') on each policy within the Vehicle table. This derived table is given the alias of 'MaxVehicles' which is then used to join to the Policy table via the PolicyId columns. The final resultset of this query contains one record for each Policy, containing PolicyId, LastName, and NewestVehicle year.

Deciding which type of table to use

Things to be aware of when using temp tables versus table variables versus derived tables:

• Temp Tables are created in the SQL Server TEMPDB database and therefore require more IO resources and locking. Table Variables and Derived Tables are created in memory.
• Temp Tables will generally perform better for large amounts of data that can be worked on using parallelism whereas Table Variables are best used for small amounts of data (I use a rule of thumb of 100 or less rows) where parallelism would not provide a significant performance improvement.
• You cannot use a stored procedure to insert data into a Table Variable or Derived Table. For example, the following will work: INSERT INTO #MyTempTable EXEC dbo.GetPolicies_sp whereas the following will generate an error: INSERT INTO @MyTableVariable EXEC dbo.GetPolicies_sp.
• Derived Tables can only be created from a SELECT statement but can be used within an Insert, Update, or Delete statement.
• In order of scope endurance, Temp Tables extend the furthest in scope, followed by Table Variables, and finally Derived Tables.

Summary

As you can see there are several different techniques you can use to work with data within SQL Server.
The scope of this article was simply to introduce you to these table types. A future article will be dedicated to performance tuning a slow-running query using the table types discussed in this article. So, until then, I encourage you to start researching the use of temporary tables, table variables, and derived tables and explore the different ways they can be used to optimize your queries and stored procedures.