SA0009 : Consider using a table variable instead temporary table
Introduction
Section titled “Introduction”This rule addresses the inefficient use of temporary tables in SQL batches by highlighting the benefits of using table variables where appropriate.
Description
Section titled “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:
--CREATE TABLE #TempTable (Column1 INT);INSERT INTO #TempTable VALUES (1);SELECT * FROM #TempTable;DROP 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 variablesbehave 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
Section titled “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:
-- Original query using a temporary tableCREATE TABLE #TempTable (Column1 INT);INSERT INTO #TempTable VALUES (1);SELECT * FROM #TempTable;DROP TABLE #TempTable;
-- Optimized query using a table variableDECLARE @TableVariable TABLE (Column1 INT);INSERT INTO @TableVariable VALUES (1);SELECT * FROM @TableVariable;The rule has a Batch scope and is applied only on the SQL script.
Parameters
Section titled “Parameters”Rule has no parameters.
Remarks
Section titled “Remarks”The rule does not need Analysis Context or SQL Connection.
Effort To Fix
Section titled “Effort To Fix”13 minutes per issue.
Categories
Section titled “Categories”Performance Rules, Code Smells
Additional Information
Section titled “Additional Information”What’s the difference between a temp table and table variable in SQL Server?
Example Test SQL
Section titled “Example Test SQL”-- The temporary table ##mail is created and droped inside a single batch, therefore it can be replaced with a local table variable.CREATE TABLE #mail( toAddress NVARCHAR( 100 ) , fromAddres NVARCHAR( 100 ) , subject NVARCHAR( 256 ) , body NVARCHAR( 4000 ));
INSERT INTO #mail( toAddress , fromAddres , subject , body )VALUES( 'support@ubitsoft.com' , 'sqlenight_user@gmail.com' , 'Body' , 'Put your subject here.' );
SELECT toAddress , fromAddres , subject , bodyFROM #mail
DROP TABLE #mail;Analysis Results
Section titled “Analysis Results”| Message | Line | Column | |
|---|---|---|---|
| 1 | SA0009 : Consider using a table variable instead of the ‘#mail’ temporary table. | 2 | 13 |