SA0009 : Consider using a table variable instead temporary table

This rule addresses the inefficient use of temporary tables in SQL batches by highlighting the benefits of using table variables where appropriate.

Description

Temporary tables often involve unnecessary overhead when used in small-scale queries where query plans are static, and recompilation is a concern. In SQL Server, table variables can offer better performance under certain conditions.

Example of problematic temporary table usage:

SQL
Copy
1-- 
2CREATE TABLE #TempTable (Column1 INT);
3INSERT INTO #TempTable VALUES (1);
4SELECT * FROM #TempTable;
5DROP TABLE #TempTable;

The above example shows a temporary table created and dropped within the same batch, without using indexes. In such cases, replacing it with a table variable might be more efficient.

  • Table variables behave like local variables with a defined scope and are automatically cleaned up.

  • They reduce recompilation of stored procedures and require less locking and logging resources than temporary tables.

However, there are limitations to using table variables:

  • Optimal execution plans may not be chosen when handling large amounts of data (over 100 rows).

  • Lack support for cost-based query plan optimizations, which can be crucial for complex queries involving joins, parallelism, and index selection.

  • Indexes cannot be explicitly created, and no statistics are available, possibly reducing performance.

How to fix

Optimize the use of temporary tables by considering the appropriate use of table variables to improve performance and resource management in SQL batches.

Follow these steps to address the issue:

  1. Evaluate the necessity of the temporary table. If the table is used in small-scale queries or when query plans are static, consider replacing it with a table variable for better performance.

  2. Replace the CREATE TABLE statement for a temporary table with a DECLARE statement for a table variable in your T-SQL batch.

  3. Use the table variable similarly to how you would use the temporary table, adjusting commands like INSERT and SELECT to operate on the table variable instead.

  4. Ensure that the scale and complexity of queries involved are suitable for table variables, keeping in mind the limitations such as the inability to create custom indexes and lack of statistics.

For example:

SQL
Copy
 1-- Original query using a temporary table
 2CREATE TABLE #TempTable (Column1 INT);
 3INSERT INTO #TempTable VALUES (1);
 4SELECT * FROM #TempTable;
 5DROP TABLE #TempTable;
 6
 7-- Optimized query using a table variable
 8DECLARE @TableVariable TABLE (Column1 INT);
 9INSERT INTO @TableVariable VALUES (1);
10SELECT * FROM @TableVariable;

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
13 minutes per issue.
Categories

Performance Rules, Code Smells

Additional Information
Example Test SQL
SQL
Copy
 1-- The temporary table ##mail is created and droped inside a single batch, therefore it can be replaced with a local table variable.
 2CREATE TABLE #mail
 3(      
 4        toAddress NVARCHAR( 100 ) ,
 5        fromAddres NVARCHAR( 100 ) ,
 6        subject NVARCHAR( 256 ) ,
 7        body NVARCHAR( 4000 )
 8);
 9
10INSERT INTO #mail(  toAddress ,
11                          fromAddres ,
12                          subject ,
13                          body )
14VALUES(  'support@ubitsoft.com' ,
15         'sqlenight_user@gmail.com' ,
16         'Body' ,
17         'Put your subject here.' );
18
19SELECT     toAddress ,
20           fromAddres ,
21           subject ,
22           body
23FROM       #mail
24
25DROP TABLE #mail;
Analysis Results
  Message Line Column
1 SA0009 : Consider using a table variable instead of the ‘#mail’ temporary table. 2 13
See Also

Other Resources

Analysis Rules