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

Temp Tables in SQL Server

By Dinesh Asanka, (first published: 2004/02/11)

Introduction

Temporary tables are always a great help for a developer. In the early days when I used Access I used to create tables that I treated as temporary and then delete them whenever I finished my task. Using SQL Server this is much simpler. Or is it?

Types of Temporary tables

There are two types of temporary data types. They are namely Local and global. Let me first give you and example to start the temporary table. Following example is taken from Books on Line of Microsoft® SQL Server™ 2000.

"The two types of temporary tables, local and global, differ from each other in their names, their visibility, and their availability. Local temporary tables have a single number sign (#) as the first character of their names; they are visible only to the current connection for the user; and they are deleted when the user disconnects from instances of Microsoft® SQL Server™ 2000. Global temporary tables have two number signs (##) as the first characters of their names; they are visible to any user after they are created; and they are deleted when all users referencing the table disconnect from SQL Server" 1

"For example, if you create a table named employees, the table can be used by any person who has the security permissions in the database to use it, until the table is deleted. If you create a local temporary table named #employees, you are the only person who can work with the table, and it is deleted when you disconnect. If you create a global temporary table named ##employees, any user in the database can work with this table. If no other user works with this table after you create it, the table is deleted when you disconnect. If another user works with the table after you create it, SQL Server deletes it when both of you disconnect."2

Note: 1,2 above is taken from the book on line sub heading Creating and Modifying

Unlike in the access days, you do not have to delete these temporary tables manually, instead you can rely SQL Server to do it automatically.

Use of Temporary Tables

Temporary tables are used in several ways. Most commonly uses to keep the result of a called stored  procedure, to reduce the number of rows for joins, to aggregate data from different sources, or to replaces cursors and for parameterized views. SQL Server cursors have huge overhead. Maintenance of code is much easier if you use temporary tables to the T-SQL. It will be much easier to debug your stored procedure when your using temporary tables as the data will be saved in temporary tables.

Alternatives to Temporary Tables

There are few alternatives to temporary tables. Using a derived table is one them. In SQL Server 2000, new data type called "table" offers same properties as temporary tables. Its main purpose is for the temporary storage of a set of rows. "table" act as a local variable. "table" is created in memory unlike the temporary table which will create in tempdb, which is obviously much faster. Another fact is that "table" uses limited resources than that of temporary tables.

Limitations of Temporary Tables

Temporary tables are created in the tempdb database and create additional overhead for SQL Server, reducing overall performances. SQL Server has numerous problems with operations against temporary tables.

Using Temporary Tables Effectively

If you do not have any option other than to use temporary tables, use them affectively. There are few steps to be taken.

  • Only include the necessary columns and rows rather than using all the columns and all the data which will not make sense of using temporary tables. Always filter your data into the temporary tables.
  • When creating temporary tables, do not use SELECT INTO statements, Instead of SELECT INTO statements, create the table using DDL statement and use INSERT INTO to populate the temporary table.
  • Use indexes on temporary tables. Earlier days, I always forget to use a index on temporary. Specially, for large temporary tables consider using clustered and non-clustered indexes on temporary tables.
  • After you finish the using your temporary table, delete them. This will free the tempdb resources. Yes, I agree that temporary tables are deleted when connection is ended. but do not wait until such time.
  • When creating a temporary table do not create them with a transaction. If you create it with a transaction, it will lock some system tables (syscolumns, sysindexes, syscomments). This will prevent others from executing the same query.

Conclusion

Generally, temporary tables should be avoided as much as possible. If you need to use them follow the steps above so that you have the minimum impact on server performance.

Total article views: 79928 | Views in the last 30 days: 74
 
Related Articles
BLOG

Answer - Objective Question (In which database temporary tables are created in SQL Server)

Question :- In which database temporary tables are created in SQL Server? 1) User database (where...

BLOG

SQL Server - Global temporary tables

Global temporary table:- Global temporary table is created in the tempdb  and it is visible to all...

BLOG

SQL Server - Local temporary table

Local temporary table:- Local temporary table is created in the tempdb and it is visible to the cu...

ARTICLE

Temporary Stored Procedures

Temporary Stored Procedures - little known sql server feature

FORUM

Mirroring disconnected.

mirroring disconnected

Tags
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