Leveraging Temporary and Variable Tables in SQL

When manipulating data especially from SQL views that require several minutes to produce results that are executed several times while running jobs, you may find the use of temporary or variable SQL tables can alleviate the amount of time it takes to complete the entire task.

For example, if you have an automated job that requires data to be pulled from a view and that view requires 4 minutes to produce results, how long will the job take to complete if the view needs to be accessed 20 times before the job has been completed?

Temporary or Variable Table

Temporary and variable tables are very useful. Creating these types of tables on demand, loading the data from the view into the table, and dropping the table when it is no longer needed, can be done in a snap.

The benefit here is that the view in our example will take 4 minutes to produce the results, but once the data is stored in the temporary table, you can access that data in milliseconds over and over again because the data in the temporary table is static, unlike a view which may have complex joins and sorts.

Here is an example of how to create a temporary table.

CREATE TABLE #tempTable1 (
    id int,
    itemName varchar(50),
    itemDesc varchar(max)
)

INSERT INTO #tempTable1 (ID, itemName, itemDesc)

SELECT id, itemName, itemDesc
FROM dbo.Inventory
Where itemType = ‘Books’

-- Add Code to manipulate tempTable1

DROP TABLE #tempTable1

Temporary tables are quick. Since you are creating and deleting them on the fly, they are generally cached in memory. However, If you are using SQL Server 2000 or higher, you can also take advantage of variable tables.

These are similar to temporary tables except offer more flexibility and stay in memory. In addition, they do not need to be dropped when you no longer need them.

DECLARE @tempTable1 TABLE (
    id int,
    itemName varchar(50),
    itemDesc varchar(max)
)

INSERT INTO @tempTable1 (ID, itemName, itemDesc)

SELECT id, itemName, itemDesc
FROM dbo.Inventory
Where itemType = ‘Books’

-- Add Code to manipulate tempTable1

It is a good idea to use variable tables for small amounts of data. Use temporary tables for large data sets.