SA0264 : Temporary table created but not used as table source

Introduction

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:

-- Temporary table created but never used
CREATE TABLE #TempData (Column1 INT, Column2 VARCHAR(100));

-- Some operations that don't use #TempData
SELECT 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:

-- Remove unnecessary temporary table
-- CREATE TABLE #TempData (Column1 INT, Column2 VARCHAR(100));
-- Retain only relevant operations
SELECT 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.

Additional Information

There is no additional info for this rule.

Example Test SQL

CREATE TABLE #Greeting
(
GreetingId INT IDENTITY (1,1) PRIMARY KEY,
Message nvarchar(255) NOT NULL,
)

SELECT * INTO #Greeting2 FROM dbo.Greeting

INSERT INTO #Greeting (Message)
SELECT 'Hello!'
UNION ALL
SELECT 'Hi!'
UNION ALL
SELECT 'Hello, world!'

DELETE  #Greeting WHERE GreetingId = 3

-- 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

Analysis Rules