SQL – Temporary Tables

Introduction Temporary Tables are a great T-SQL feature that lets you store and process intermediate results by using the same selection, update, and join capabilities that you can use with typical SQL Server tables. “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.” Types of Temporary Tables in SQL You can create two types of temporary tables in SQL, local and global temporary tables. The two types of temporary tables, local and global, differ from each other in their names, their visibility, and their availability.

Local Temporary Tables Local temporary tables prefix with single number sign (#) as the first character of their names, like (#table_name). Local temporary tables are visible only in the current session OR you can say that they are visible only to the current connection for the user. They are deleted when the user disconnects from instances of Microsoft SQL Server.

Global Temporary Tables Global temporary tables prefix with double number sign (##) as the first character of their names, like (##table_name). Global temporary tables are visible to all sessions OR you can say that they are visible to any user after they are created. They are deleted when all users referencing the table disconnect from Microsoft SQL Server.

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. SQL statements for creating the temporary table using the CREATE TABLE statement:

CREATE TABLE #MyTempTable (cola INT PRIMARY KEY) INSERT INTO #MyTempTable VALUES (1) SELECT * FROM #MyTempTable

When you create local or global temporary tables, the CREATE TABLE syntax supports constraint definitions with the exception of FOREIGN KEY constraints. If a FOREIGN KEY constraint is specified in a temporary table, the statement returns a warning message indicating that the constraint was skipped, and the table is still created without the FOREIGN KEY constraints. Temporary tables cannot be referenced in FOREIGN KEY constraints.

Naming Of Temporary Tables Temporary tables are always created in tempdb. No matters it is created from the stored procedure internally or from the SQL Query Analyzer window. If a local temporary table is created in a stored procedure or application that can be executed at the same time by several users, SQL Server has to be able to distinguish the tables created by the different users. SQL Server does this by internally appending a numeric suffix to each local temporary table name. The full name of a temporary table as stored in the sysobjects table in tempdb consists of table name specified in the CREATE TABLE statement and the system-generated numeric suffix. To allow for the suffix, table_name specified for a local temporary name cannot exceed 116 characters. Reason: “Table names in SQL are the combination of owner.table_name and it must be unique within the database. Table_name can contain a maximum of 128 characters, except for local temporary table names (names prefixed with a single number sign (#)) that cannot exceed 116 characters.” For example: CREATE TABLE #Yaks (YakID int,YakName char(30)) SELECT Name FROM tempdb.dbo.sysobjects WHERE Name LIKE ‘#yak%’ The above select query will return the result something like below displayed. name ——————————————————————- #Yaks__________________________________________________00000000001D (1 row(s) affected) SQL Server stores temporary tables as a database object with a type of unique number appended on the end of the name. But in case of temporary tables it may be the possibility that two users can create the temporary table with the same name as we know that all temporary tables stored in the tempdb database, so if the name of both temporary tables will be same then SQL server will through an error to the user because objects name in the SQL server must be unique in the database. So, to avoid this problem whenever anyone creates any temporary table SQL server automatically appends a unique string with the table name that is given by user while creating the table, But don’t worry you need to do anything with this extra string, It does all this for you automatically. You just have to refer to your #table_name with which you have created the temporary table. You can refer this name anywhere in your code where you want.

Deletion of Temporary Tables

A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table. All other local temporary tables are dropped automatically at the end of the current session. Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.

For example: SQL statements for deleting the temporary table using the DROP TABLE statement: DROP TABLE #Table_name A local temporary table created within a stored procedure or trigger is distinct from a temporary table with the same name created before the stored procedure or trigger is called.

Use of Temporary Table 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.

Limitations of Temporary Tables Temporary tables are created in the tempdb database and create additional overhead for SQL Server, reducing overall performances. This happens because all reads and writes to the temporary tables are done within the tempdb database.

Using Temporary Tables Effectively If you do not have any option other than to use temporary tables, use them effectively. 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, as it places locks on system objects. Instead of SELECT INTO statements, create the table using Transact-SQL DDL statements and use INSERT INTO to populate the temporary table. Use indexes on temporary tables. Earlier days, I always forget to use an index on temporary. Specially, for large temporary tables consider using clustered and non-clustered indexes on temporary tables. You will have to test to see if indexes help or hurt overall performance. 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.

In general, temp tables should be avoided, if possible. Because they are created in the tempdb database, they will create additional overhead for SQL Server, slowing overall performance. As an alternative to temp tables, consider the following alternatives:

Rewrite your code so that the action you need completed can be done using a standard query or stored procedure, without using a temp table. Use a derived table. Consider using a correlated sub-query. Use a permanent table instead. Use a UNION statement to mimic a temp table. Use a table variable. It all depends on your requirement at that time when you are creating your query, but as written in Microsoft SQL Server Book Online, “Consider using table variables instead of temporary tables. Temporary tables are useful in cases when indexes need to be created explicitly on them, or when the table values need to be visible across multiple stored procedures or functions. In general, table variables contribute to more efficient query processing.” If you are using SQL Server 2000 or higher, you can take advantage of the new TABLE variable type. These are similar to temporary tables except with more flexibility and they always stay in memory. The code using a table variable might look like below: —————————————————————– DECLARE @TibetanYaks TABLE ( YakID int, YakName char(30) ) INSERT INTO @TibetanYaks (YakID, YakName) SELECT YakID, YakName FROM dbo.Yaks WHERE YakType = ‘Tibetan’ —————————————————————– Table variables don’t need to be dropped when you are done with them.

Which to use

If you have less than 100 rows generally use a table variable. Otherwise use a temporary table. This is because SQL Server won’t create statistics on table variables. If you need to create indexes on it then you must use a temporary table. When using temporary tables always create them and create any indexes and then use them. This will help reduce recompilations. The impact of this is reduced starting in SQL Server 2005 but it’s still a good idea.

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.

*******

Reference from and many thanks to following websites and articles: Microsoft’s Book Online For SQL Server 2000 Sql-Server-Performance Sql Server Central SqlTeam Prashant Pandey (TechAhead)