SA0233 : Temporary table created but not dropped

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:

SQL
Copy
1-- Example of problematic practice
2CREATE TABLE #TempTable (ID INT, Name NVARCHAR(100));
3-- Operations with the temporary table
4SELECT * FROM #TempTable;
5-- 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:

SQL
Copy
1-- Example of corrected practice
2CREATE TABLE #TempTable (ID INT, Name NVARCHAR(100));
3-- Operations with the temporary table
4SELECT * FROM #TempTable;
5-- Explicitly drop the temporary table
6DROP 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.
Categories

Design Rules, Code Smells

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1CREATE TABLE #mail
 2(      
 3        toAddress NVARCHAR( 100 ) ,
 4        fromAddres NVARCHAR( 100 ) ,
 5        subject NVARCHAR( 256 ) ,
 6        body NVARCHAR( 4000 )
 7);
 8
 9SELECT * FROM #mail
10
11INSERT INTO #mail VALUES ('toAddress','fromAddres','subject','body')
12
13DROP TABLE #mail
14
15CREATE TABLE #mail_1
16(      
17        toAddress NVARCHAR( 100 ) ,
18        fromAddres NVARCHAR( 100 ) ,
19        subject NVARCHAR( 256 ) ,
20        body NVARCHAR( 4000 )
21);
22
23INSERT INTO #mail_1 VALUES ('toAddress','fromAddres','subject','body')
24
25SELECT * FROM #mail_1 
26
27SELECT * 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
See Also

Other Resources

Analysis Rules