Click or drag to resize

SA0009 : Consider using a table variable instead temporary table

The topic describes the SA0009 analysis rule.

Message

Consider using a table variable instead temporary table

Description

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.

Note Note

Before replacing temporary tables with table variables, consider the limitations of the table variables.

  • Execution plan choices may not be optimal or stable when a table variable contains a large amount of data ( above 100 rows).

  • Table variables are not supported in the SQL Server optimizer's cost-based reasoning model. Therefore, they should not be used when cost-based choices are required to achieve an efficient query plan. Temporary tables are preferred when cost-based choices are required. This typically includes queries with joins, parallelism decisions, and index selection choices.

  • Queries that modify table variables do not generate parallel query execution plans. Performance can be affected when very large table variables, or table variables in complex queries, are modified. In these situations, consider using temporary tables instead. Queries that read table variables without modifying them can still be parallelized.

  • Indexes cannot be created explicitly on table variables, and no statistics are kept on table variables. In some cases, performance may improve by using temporary tables instead, which support indexes and statistics.

  • CHECK constraints, DEFAULT values and computed columns in the table type declaration cannot call user-defined functions.

  • Assignment operation between table variables is not supported.

  • Because table variables have limited scope and are not part of the persistent database, they are not affected by transaction rollbacks.

  • Table variables cannot be altered after creation.

For more information about temporary tables, see CREATE TABLE statement.

If you find that the temporary table is better in a particular case, you can use the rule suppression mark to ignore it.

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.

Categories

Performance Rules

Additional Information
Example Test Script
SQL
 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;
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(  'support@ubitsoft.com' ,
41         'sqlenight_user@gmail.com' ,
42         'Body' ,
43         'Put your subject here.' );
44
45SELECT     toAddress ,
46           fromAddres ,
47           subject ,
48           body
49FROM       @mail

Analysis Results

 MessageLineColumn
1SA0009 : Consider using a table variable instead of the '#mail' temporary table.213
See Also

Other Resources