When and how SQL Server disposes variables from memory in stored procedures?

Question

When we create a stored procedure propably we create some variable like this :

DECLARE @UserCount INT

We use that variable in stored procedure. But we don't dispose that variable at the end of stored procedure. We dispose Cursors with DEALLOCATE but we don't write ant lines for variables which we create.

Another stuation is we create tables and we name them "@table"

When and how Sql Server disposes that kind of variables?

Answer 1

As @Bob Palmer mentioned there is no need to deallocate memory space for variables. But you need to deallocate memory space for cursors. The reason is every variable declared inside the stored procedure are local variables. The server will automatically deallocate the memory after the stored procedure execution.

And for Cursors also you don't need to deallocate the memory. For more details refer here: MSDN Documentation

Answer 2

Adding to Bob's answer above, specifically regarding the point

We dispose Cursors with DEALLOCATE but we don't write ant lines for variables

MSDN states and i quote :

A cursor variable does not have to be explicitly deallocated. The variable is implicitly deallocated when it goes out of scope

This is applicable atleast as far back as SQL Server 2000

Answer 3

Per MSDN:

"The scope of a variable is the range of Transact-SQL statements that can reference the variable. The scope of a variable lasts from the point it is declared until the end of the batch or stored procedure in which it is declared."

http://msdn.microsoft.com/en-us/library/ms187953.aspx

On a side note - you may see some very interesting performance differences between temp tables (#MyTable) and table variables (@MyTable). If we're profiling a sproc and seeing performance problems in the use of our table variable, we'll try it with a temp table instead to see if this can help improve performance.