Did you find this useful?
Socialize it today.


Leveraging Temporary and Variable Tables in SQL

Friday, October 28, 2011

tags SQL

When manipulating data especially from SQL views that require several minutes to produce results which 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 table for large data sets.

Please help us spread the word by socializing it today!

email contact us

Did you find something wrong with the information on this page? Please take a moment to report it to us so that we can continue to improve the quality of the information on this site. Click here to report an issue with this page.



Recommended Books & Training Resources

SQL Cookbook Head First SQL: Your Brain on SQL A Learners Guide