Click or drag to resize

SA0148 : Consider using a temporary table instead of a table variable

The topic describes the SA0148 analysis rule.

Message

Consider using a temporary table instead of a table variable

Description

The rule checks the SQL code for usage of table variables.

It is recommended not use table variables to store large amounts of data (more than 100 rows). Execution plan choices may not be optimal or stable when a table variable contains a large amount of data. Consider rewriting such queries to use temporary tables or use the USE PLAN query hint to ensure the optimizer uses an existing query plan that works well for your scenario.

If you find that the table variable is acceptable 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
NameDescriptionDefault Value
RuleSuppressMark

Suppress rule reporting a rule violation in case a comment containing the value of this parameter is found next to the source of violation.

IGNORE:SA0148

Remarks

The rule does not need Analysis Context or SQL Connection.

Categories

Design Rules

Additional Information
Example Test Script
 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
50
51DECLARE @mail2 TABLE 
52(      
53        toAddress NVARCHAR( 100 ) ,
54        fromAddres NVARCHAR( 100 ) ,
55        subject NVARCHAR( 256 ) ,
56        body NVARCHAR( 4000 )
57) -- IGNORE:UseTempTable

Analysis Results

 MessageLineColumn
1SA0148 : Consider using a temporary table instead of a table variable.288
2SA0148 : Consider using a temporary table instead of a table variable.518
See Also

Other Resources