SA0264 : Temporary table created but not used as table source

Identify and eliminate unused temporary tables in T-SQL code to improve database efficiency and prevent unnecessary resource allocation.

Description

A common inefficiency occurs when temporary tables are created but not actually used in any subsequent SELECT, UPDATE, DELETE, or MERGE statements. This can lead to unnecessary resource allocation and potential performance issues.

For example:

SQL
Copy
1-- Temporary table created but never used
2CREATE TABLE #TempData (Column1 INT, Column2 VARCHAR(100));
3
4-- Some operations that don't use #TempData
5SELECT Column1 FROM AnotherTable;

The above example is problematic because the temporary table #TempData is created but never utilized in any subsequent SQL operations. This could consume resources unnecessarily, reduce overall performance, and complicate code maintenance.

  • Wasted resources from unnecessary temporary table creation.

  • Potential confusion and maintenance difficulty with cluttered code.

How to fix

Eliminate unused temporary tables to improve SQL efficiency and reduce resource waste.

Follow these steps to address the issue:

  1. Identify temporary tables that are created but not utilized in subsequent SQL statements such as SELECT, UPDATE, DELETE, or MERGE.

  2. Review the SQL code to determine if the temporary table serves any purpose that might not be immediately obvious, such as being a placeholder for future operations.

  3. If the temporary table is truly unnecessary, remove the CREATE TABLE statement for that temporary table.

  4. Refactor the remaining code to maintain functionality and improve clarity.

For example:

SQL
Copy
1-- Remove unnecessary temporary table
2-- CREATE TABLE #TempData (Column1 INT, Column2 VARCHAR(100));
3-- Retain only relevant operations
4SELECT Column1 FROM AnotherTable;

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
3 minutes per issue.
Categories

Design Rules, Code Smells

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1CREATE TABLE #Greeting
 2(
 3GreetingId INT IDENTITY (1,1) PRIMARY KEY,
 4Message nvarchar(255) NOT NULL,
 5)
 6
 7SELECT * INTO #Greeting2 FROM dbo.Greeting
 8
 9INSERT INTO #Greeting (Message) 
10SELECT 'Hello!'
11UNION ALL 
12SELECT 'Hi!'
13UNION ALL
14SELECT 'Hello, world!'
15
16DELETE  #Greeting WHERE GreetingId = 3
17
18-- SELECT * FROM #Greeting g WHERE g.Message like 'Hello%'
Analysis Results
  Message Line Column
1 SA0264 : Temporary table created but not used as table source. 1 13
2 SA0264 : Temporary table created but not used as table source. 7 14
See Also

Other Resources

Analysis Rules