SQL DEALLOCATE

What is SQL DEALLOCATE?

Releases server resources by removing a prepared statement or cursor previously created in the session.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL DEALLOCATE: PostgreSQL (prepared statements) – Yes SQL Server (cursors) – Yes Snowflake – Yes (DEALLOCATE PREPARE) MariaDB/MySQL – No Oracle – No SQLite – No

SQL DEALLOCATE Full Explanation

SQL DEALLOCATE is a resource-cleanup command. In PostgreSQL it removes a prepared statement created with PREPARE, freeing the memory and execution plan held on the server. In SQL Server it removes a cursor created with DECLARE CURSOR or sp_cursoropen. DEALLOCATE has no effect on transaction state; it simply releases objects that are no longer needed so they do not consume resources for the rest of the session. If the specified object does not exist or has already been closed, the database returns an error. Using DEALLOCATE ALL in PostgreSQL removes every prepared statement defined in the current session, which is helpful for long-running connections or scripts that open many prepared statements.

SQL DEALLOCATE Syntax

-- PostgreSQL
DEALLOCATE [ PREPARE ] { name | ALL };

-- SQL Server
DEALLOCATE { cursor_name | @cursor_variable_name | GLOBAL };

SQL DEALLOCATE Parameters

name (identifier) - Required. The name of the prepared statement (PostgreSQL) or cursor (SQL Server) to remove.
ALL (keyword) - Optional (PostgreSQL only). Frees every prepared statement in the current session.

Example Queries Using SQL DEALLOCATE

-- Example 1: PostgreSQL prepared statement
PREPARE user_by_id (int) AS
  SELECT * FROM users WHERE id = $1;
EXECUTE user_by_id(42);
DEALLOCATE user_by_id;

-- Example 2: PostgreSQL remove all prepared statements
DEALLOCATE ALL;

-- Example 3: SQL Server cursor
DECLARE order_cur CURSOR FOR
  SELECT id, total FROM orders;
OPEN order_cur;
FETCH NEXT FROM order_cur;
DEALLOCATE order_cur;

Expected Output Using SQL DEALLOCATE

The specified prepared statement or cursor is removed from server memory
Subsequent EXECUTE or FETCH calls on that object raise an error indicating it no longer exists

Use Cases with SQL DEALLOCATE

Cleaning up prepared statements in long-running sessions to avoid memory bloat
Explicitly releasing cursors after iteration in SQL Server
Ensuring test scripts leave no lingering server-side objects
Resetting a connection before handing it to a connection pool

Common Mistakes with SQL DEALLOCATE

Forgetting to DEALLOCATE and leaking resources in sessions that stay open for hours
Attempting to DEALLOCATE a statement or cursor that was never created, resulting in an error
Confusing DEALLOCATE ALL (PostgreSQL) with rolling back a transaction; DEALLOCATE does not undo data changes
Assuming MySQL supports DEALLOCATE; it does not

First Introduced In

PostgreSQL 7.3 (prepared statements) and SQL Server 2000 (cursor deallocation)

Frequently Asked Questions

What does SQL DEALLOCATE do?

It releases a prepared statement (PostgreSQL) or cursor (SQL Server) so the server no longer holds its execution plan or memory.

Is DEALLOCATE required before closing a session?

The database automatically frees resources when the session ends, but DEALLOCATE is recommended in long-lived sessions or connection pools to avoid leaks.

Can I deallocate everything at once?

Yes. In PostgreSQL, use DEALLOCATE ALL to remove every prepared statement created in the current session.

Does DEALLOCATE affect my data?

No. It does not commit, roll back, or modify data. It only drops server-side objects tied to your session.