Topic: Transact-SQL DEALLOCATE
The purpose of the DEALLOCATE keyword in sql server is to remove a cursor reference. SQL Server has data structures behind the scenes that are released once DEALLOCATE is executed.
Syntax
DEALLOCATE { { [ GLOBAL ] cursor_name } | @cursor_variable_name }
Example
-- SAFEGUARD
-- not familiar with the .. syntax below? check out this blog <a title="dbo .. syntax and the default database" href="http://examplesql.com/sqlserver-tsql/dbo-dotdot-syntax/" data-mce-href="http://examplesql.com/sqlserver-tsql/dbo-dotdot-syntax/">post</a>
IF OBJECT_ID('tempdb..#Hobbies') IS NOT NULL
BEGIN
DROP TABLE #Hobbies
END
-- create the temp table
CREATE TABLE #Hobbies (
id INT IDENTITY(1,1) NOT NULL,
article_name NVARCHAR(100)
)
-- fill the temp table with sample data
INSERT INTO #Hobbies (article_name)
VALUES
('cooking'),('crafts'),('flying'),
('T-SQL'), ('wood carving'),('gardening'),
('reading'),('beer brewing'),('gaming');
SELECT article_name FROM #Hobbies;
-- create the variable we'll use to store data from the FETCH
DECLARE @articleName NVARCHAR(100);
------------------ CURSOR SPECIFIC CODE BEGIN --------------------
-- declare the fast_forward cursor. notice we are essentially
-- using a SELECT query to build a list to iterate through
DECLARE hobbiesCursor SCROLL CURSOR FOR
SELECT article_name FROM #Hobbies ;
-- now we have to open the cursor and tell it to go
-- acquire (i.e. FETCH) items from the temp table
OPEN hobbiesCursor
FETCH FIRST FROM hobbiesCursor INTO @articleName;
SELECT ('First Hobby Name: ' + @articleName );
FETCH NEXT FROM hobbiesCursor INTO @articleName;
SELECT ('Second Hobby Name: ' + @articleName );
FETCH LAST FROM hobbiesCursor INTO @articleName;
SELECT ('Last Hobby Name: ' + @articleName );
FETCH PRIOR FROM hobbiesCursor INTO @articleName;
SELECT ('Second to Last Hobby Name: ' + @articleName ) ;
CLOSE hobbiesCursor ;
DEALLOCATE hobbiesCursor;
Results
(9 row(s) affected) article_name ---------------------------------------------------------------------------------------------------- cooking crafts flying T-SQL wood carving gardening reading beer brewing gaming (9 row(s) affected) ---------------------------------------------------------------------------------------------------------------------- First Hobby Name: cooking (1 row(s) affected) ----------------------------------------------------------------------------------------------------------------------- Second Hobby Name: crafts (1 row(s) affected) --------------------------------------------------------------------------------------------------------------------- Last Hobby Name: gaming (1 row(s) affected) ------------------------------------------------------------------------------------------------------------------------------- Second to Last Hobby Name: beer brewing (1 row(s) affected)
Notice DEALLOCATE is at the very bottom of the code listing. It’s role is simply to perform memory ‘clean up’. A LOCAL cursor variable does not have to be deallocated since it freed when the variable goes out of scope (end of the batch, stored procedure, or trigger), however GLOBAL cursors should be freed unless you intentionally mean to reuse the cursor during the course of the entire database connection. Additionally, If you are planning to reuse a cursor variable multiple times in your scope then you should deallocate the cursor variable as required.
Example SQL
USE AdventureWorks2008; GO DECLARE @PersonCursor CURSOR SET @PersonCursor = CURSOR LOCAL SCROLL FOR SELECT * FROM Person.Address; DEALLOCATE @PersonCursor; SET @PersonCursor = CURSOR LOCAL SCROLL FOR SELECT * FROM Person.EmailAddress; GO /* CURSOR IS AUTOMATICALLY DEALLOCATED WHEN IT GOES OUT OF SCOPE */
For some of the deeper details of DEALLOCATE you can check out the documentation here.
Valid for SQL Server 2005, 2008, R2, 2012, 2014.
