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.

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

Ensure efficient resource usage by properly managing temporary tables.
Follow these steps to address the issue:
-
Identify if the temporary table is used locally within the current batch or SQL module.
-
After the last operation involving the temporary table, use the DROP TABLE statement to explicitly remove it. This will help free up resources immediately.
-
Consider reviewing the session’s logic to ensure that temporary tables are only created and retained for the shortest necessary duration.
For example:
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;

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.


Design Rules, Code Smells

There is no additional info for this rule.

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

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