SA0233 : Temporary table created but not dropped

Introduction

Unnecessary temporary tables in SQL Server can cause resource wastage if not explicitly dropped after use, leading to inefficient memory and system overhead.

Description

In SQL Server, developers often use temporary tables for intermediate data storage within a session. However, if these temporary tables are not explicitly dropped after use, they remain until the session ends. This leads to inefficient resource usage, including memory consumption and system overhead. It is crucial to manage temporary tables efficiently by dropping them once they are no longer needed.

For example:

-- Example of problematic practice
CREATE TABLE #TempTable (ID INT, Name NVARCHAR(100));
-- Operations with the temporary table
SELECT * FROM #TempTable;
-- Temporary table not dropped explicitly

The example demonstrates a scenario where a temporary table is created but not dropped. This can lead to unnecessary memory usage throughout the session, as SQL Server will only clean up the table automatically when the session ends, not immediately.

The server resources allocated for the temporary table are unnecessarily retained for the session duration.
Accumulation of multiple such tables can lead to resource contention and impact performance.

How to fix

Ensure efficient resource usage by properly managing temporary tables.

Follow these steps to address the issue:

1.Identify if the temporary table is used locally within the current batch or SQL module.

2.After the last operation involving the temporary table, use the DROP TABLE statement to explicitly remove it. This will help free up resources immediately.

3.Consider reviewing the session’s logic to ensure that temporary tables are only created and retained for the shortest necessary duration.

For example:

-- Example of corrected practice
CREATE TABLE #TempTable (ID INT, Name NVARCHAR(100));
-- Operations with the temporary table
SELECT * FROM #TempTable;
-- Explicitly drop the temporary table
DROP TABLE #TempTable;

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

2 minutes per issue.

Additional Information

There is no additional info for this rule.

Example Test SQL

CREATE TABLE #mail
(
        toAddress NVARCHAR( 100 ) ,
        fromAddres NVARCHAR( 100 ) ,
        subject NVARCHAR( 256 ) ,
        body NVARCHAR( 4000 )
);

SELECT * FROM #mail

INSERT INTO #mail VALUES ('toAddress','fromAddres','subject','body')

DROP TABLE #mail

CREATE TABLE #mail_1
(
        toAddress NVARCHAR( 100 ) ,
        fromAddres NVARCHAR( 100 ) ,
        subject NVARCHAR( 256 ) ,
        body NVARCHAR( 4000 )
);

INSERT INTO #mail_1 VALUES ('toAddress','fromAddres','subject','body')

SELECT * FROM #mail_1

SELECT * INTO #table1 FROM table1

Analysis Results

	Message	Line	Column
1	SA0233 : Temporary table created but not dropped.	15	13
2	SA0233 : Temporary table created but not dropped.	27	14

Analysis Rules