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

Introduction

The use of table variables in T-SQL can lead to performance issues and incorrect query behavior.

Description

In T-SQL code, table variables are often used as an alternative to temporary tables. However, they have certain limitations and characteristics that can result in inefficient query plans and unexpected results.

For example:

-- Example of potentially problematic use of a table variable
DECLARE @TableVar TABLE (ID INT, Name NVARCHAR(50));

INSERT INTO @TableVar (ID, Name) VALUES (1, 'Sample');

SELECT * FROM @TableVar WHERE ID = 1;

In this example, using a table variable might limit SQL Server’s ability to generate optimal query plans because statistics are not maintained on table variables . Hence, query performance might degrade, especially with larger datasets.

Performance issues: Without automatic statistics updating, SQL Server lacks information for executing efficient query plans, potentially slowing down operations.
Limited indexing: Table variables do not support the indexing capabilities of temporary tables, which can further lead to inefficient data access patterns.

How to fix

Avoid using table variables when storing more than 100 rows, as they may lead to suboptimal execution plans. Instead, consider using temporary tables or appropriate query hints to improve performance.

Follow these steps to address the issue:

1.Assess the current usage of the @TableVar by evaluating the number of rows it holds and its impact on performance.

2.If the @TableVar contains more than 100 rows, consider replacing it with a temporary table. Create a temporary table by using the CREATE TABLE #TempTable syntax:

3.Rewrite the query to populate the temporary table instead of the table variable:

For example:

-- Example of using a temporary table instead of a table variable
CREATE TABLE #TempTable (ID INT, Name NVARCHAR(50));
INSERT INTO #TempTable (ID, Name) VALUES (1, 'Sample');
SELECT * FROM #TempTable WHERE ID = 1;

1.If using a temporary table is not feasible, use the USE PLAN query hint to potentially improve the execution plan stability.

2.In cases where performance is acceptable and suppression is warranted, use the rule suppression mark to ignore the warning for specific queries.

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

20 minutes per issue.

Additional Information

table (Transact-SQL)

What’s the difference between a temp table and table variable in SQL Server?

Example Test SQL

DECLARE @mail TABLE
(
        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 ,
           body
FROM       @mail

DECLARE @mail2 TABLE
(
        toAddress NVARCHAR( 100 ) ,
        fromAddres NVARCHAR( 100 ) ,
        subject NVARCHAR( 256 ) ,
        body NVARCHAR( 4000 ))

Analysis Results

	Message	Line	Column
1	SA0148 : Consider using a temporary table instead of a table variable.	2	8
2	SA0148 : Consider using a temporary table instead of a table variable.	25	8

Analysis Rules