SA0009 : Consider using a table variable instead temporary table
The topic describes the SA0009 analysis rule.
Consider using a table variable instead temporary table
The rule checks for temporary tables which are created, used and deleted in the same batch, and do not have explicit indexes created.
This kind of temporary tables can be replaced with table variables.
The table variables provide the following benefits for small-scale queries that have query plans that do not change and when recompilation concerns are dominant:
A table variable behaves like a local variable. It has a well-defined scope. This is the function, stored procedure, or batch that it is declared in.
Within its scope, a table variable can be used like a regular table. It may be applied anywhere a table or table expression is used in SELECT, INSERT, UPDATE, and DELETE statements.
Table variables are automatically cleaned up at the end of the function, stored procedure, or batch in which they are defined.
Table variables used in stored procedures cause fewer recompilations of the stored procedures than when temporary tables are used when there are no cost-based choices that affect performance.
Transactions involving table variables last only for the duration of an update on the table variable. Therefore, table variables require less locking and logging resources.
Before replacing temporary tables with table variables, consider the limitations of the table variables.
If you find that the temporary table is better in a particular case, you can use the rule suppression mark to ignore it.
The rule has a Batch scope and is applied only on the SQL script.
Rule has no parameters.
The rule does not need Analysis Context or SQL Connection.
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( 'firstname.lastname@example.org' , 15 'email@example.com' , 16 'Body' , 17 'Put your subject here.' ); 18 19SELECT toAddress , 20 fromAddres , 21 subject , 22 body 23FROM #mail 24 25DROP TABLE #mail; 26 27-- Example of a table variable which can replace the ##mail table. 28DECLARE @mail TABLE 29( 30 toAddress NVARCHAR( 100 ) , 31 fromAddres NVARCHAR( 100 ) , 32 subject NVARCHAR( 256 ) , 33 body NVARCHAR( 4000 ) 34); 35 36INSERT INTO @mail( toAddress , 37 fromAddres , 38 subject , 39 body ) 40VALUES( 'firstname.lastname@example.org' , 41 'email@example.com' , 42 'Body' , 43 'Put your subject here.' ); 44 45SELECT toAddress , 46 fromAddres , 47 subject , 48 body 49FROM @mail
|1||SA0009 : Consider using a table variable instead of the '#mail' temporary table.||2||13|