Temp Tables in SQL ServerRussian Version source
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. |
 faq(c) |
