DF073: Table variable is used.

The topic describes the DF073 T-SQL code analysis rule.

Category

BEST PRACTICE

Message

Table variable is used.

Description

It is not recommended to use table variables to store large amounts of data (more than 100 rows). Consider using a temporary table instead of a table variable.

Note

By default, the rule is turned off.

Additional information

Table variables are held in memory and are optimized for small-to-moderate result sets. Unlike temporary tables, they don’t support parallelism or advanced indexing, and their lack of statistics can prevent the query optimizer from making efficient execution choices.

Noncompliant code example

DECLARE @Tab TABLE(
  CityId INT NOT NULL,
  CityName VARCHAR(128) NOT NULL
);
 
INSERT INTO @Tab (CityId, CityName)
VALUES(1,'London');
GO

Compliant solution

CREATE TABLE #tab(
  CityId INT NOT NULL,
  CityName VARCHAR(128) NOT NULL
);
 
INSERT INTO #tab (CityId, CityName)
VALUES(1,'London');
GO