- Introduction
- Optimizing Loops and Cursors
- Temporary Tables and Table Variables
- Performance Issues with Views
- The Effect of Triggers
- Query Hints and Options
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Temporary Tables and Table Variables
Temporary tables and table variables (supported in SQL Server 2000) can be of great help in speeding up queries. In the earlier versions of SQL Server you could get a major performance improvement by splitting queries with five or more joins. Suppose you had to join tables A, B, C, D, E, and F to get the desired output. A useful trick was to join tables A, B, C, and D in the first query and store the results in a temporary table. Next you would join that temporary table to tables E and F to get the final result set. With SQL Server 2000 you can still see some performance improvements by splitting queries, but usually the number of joins that can be digested in a single query is considerably higher. In fact, if you need to split a query in two because of the large number of joins involved, you should probably reexamine your database design.
In addition to splitting queries, you can create indexes on temporary tables, which can offer significant performance gains. The benefit you get from an index on a temporary table in most cases will outweigh the cost of creating this index. Keep in mind that temporary tables are created and maintained in the TempDB database. All users connected to the same SQL Server instance share TempDB; therefore you need to ensure that TempDB always has sufficient room to grow.
Some developers mistakenly think that using temp tables is dangerous because the rows of the temp table can be read by multiple connections. But there are two types of temp tables: local and global. The local temp tables are prefixed with a single pound sign (#), whereas the global ones are prefixed with two (##). The global temp tables can be shared among the connections, while local tables are exclusively used by the connection that created them. In fact, SQL Server appends a unique string to the name of each local temp table in TempDB so that the local temp tables can't be shared. Suppose I create a temp table with the following statement:
CREATE TABLE #temp_authors ( au_id CHAR(11), au_name VARCHAR(75) )
If I check the sysobjects table in TempDB I'll find that SQL Server has appended a suffix to this table to make it unique for my connection:
USE TempDB GO SELECT name FROM sysobjects WHERE name LIKE '%#%'
Result:
#temp_authors_____________________000000000013
The table variables are slightly different. They behave similarly to temp tables, but they're created in memory. Therefore, you don't have the danger of filling up the TempDB, but you have the danger of hogging memory. Still, the in-memory version of tables can work much faster. Unfortunately you can't build indexes on the table variables; therefore, if you're counting on such indexes you must keep using the temp tables.
There are few other limitations and "gotchas" to the table variables; for instance, you must alias such variables within the JOIN clauses. Suppose I declare a table variable and populate it as follows:
DECLARE @temp_authors TABLE ( au_id CHAR(11), au_name VARCHAR(75) ) INSERT @temp_authors ( au_id, au_name ) SELECT TOP 4 au_id, au_fname + au_lname FROM authors
Now if I try to join @temp_authors to the authors table without an alias (within the same batch), SQL Server returns an error:
SELECT state FROM @temp_authors INNER JOIN authors ON @temp_authors.au_id = authors.au_id
Result:
Server: Msg 137, Level 15, State 2, Line 19 Must declare the variable '@temp_authors'.
Instead, I have to use an alias for the table variable:
SELECT state FROM @temp_authors a INNER JOIN authors ON a.au_id = authors.au_id
Result:
state ----- CA OR CA IN
Table variables are destroyed as soon as the execution of your batch is complete; therefore, you don't have to drop them explicitly as you do with the temporary tables. In addition, you can't use SELECT ... INTO syntax to populate table variables. You have to explicitly declare a variable and populate it with an INSERT statement. Nor can you populate a table variable with the results of executing a stored procedure; if you have a need to do so, you'll have to resort using a temporary table.