Skip to content

SA0267 : Table variable is used before it has any data inserted

Creating temporary tables that are never used for data insertion or as source tables in INSERT, SELECT INTO, or MERGE statements, or as output tables in OUTPUT INTO clauses results in unnecessary resource consumption and inefficiency in SQL Server.

In SQL Server, temporary tables are essential for storing intermediate results, which can assist in data processing and transformation tasks. However, using temporary tables incorrectly can lead to inefficiencies and errors in your SQL code.

For example:

-- Example of problematic usage of a temporary table
CREATE TABLE #TempTable (ID INT, Name NVARCHAR(50));
SELECT * FROM #TempTable;

This query is problematic because it attempts to select data from #TempTable without first inserting data into it. This misuse can lead to runtime errors or unexpected behavior.

  • Uninitialized temporary tables can lead to empty result sets, causing logical errors in applications relying on such data.

  • Accessing a temporary table before inserting data might indicate incomplete or misunderstood T-SQL logic, potentially leading to inefficiencies in query execution.

Ensure the correct use of temporary tables by inserting data before referencing them in SQL operations.

Follow these steps to address the issue:

1.Verify that the temporary table is created and named correctly using CREATE TABLE .

2.Ensure that data is inserted into the temporary table before any select operations are attempted. Use INSERT INTO to populate the table.

3.After inserting data, you can safely execute a query to select from the temporary table.

For example:

-- Correct usage of a temporary table
CREATE TABLE #TempTable (ID INT, Name NVARCHAR(50));
INSERT INTO #TempTable (ID, Name) VALUES (1, 'John Doe');
SELECT * FROM #TempTable;

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.

5 minutes per issue.

Design Rules, Bugs

There is no additional info for this rule.

DECLARE @mail TABLE(
toAddress NVARCHAR( 100 ) ,
fromAddres NVARCHAR( 100 ) ,
subject NVARCHAR( 256 ) ,
body NVARCHAR( 4000 )
);
SELECT * FROM @mail
DELETE FROM @mail
UPDATE @mail set toAddress =''
INSERT INTO @mail
SELECT * from mail
SELECT * FROM @mail
  Message Line Column
1 SA0267 : Table variable is used before it has any data inserted. 8 14
2 SA0267 : Table variable is used before it has any data inserted. 10 12
3 SA0267 : Table variable is used before it has any data inserted. 11 7

Analysis Rules