Different Types of SQL Server Cursors

Whilst studying for the 70-461 SQL exam I came to realize that it would be of value to me to learn all the different types of SQL cursors.

I am not a big user of cursors generally, I normally use a loop instead however cursors do have some handy features for certain situations.

However to be come a “Worthy DBA” in Microsofts eyes, it seems to be required.

There are four core types of SQL Cursors and they are

  • Static Cursors
  • Dynamic Cursors
  • Forward-Only Cursors
  • Keyset Driven Cursors

Lets look at each type and identify the key features that we will need to know for the exam.

For each one we will show a brief summary highlighting the key points, and a basic template that you can use.

Static Cursors

  • The result set is built in TempDB
  • Displays the resultset as it was at the beginning (when the cursor was opened)
  • No operations that change the data are reflected in this type of cursor (unless the cursor is closed and reopened
  • These cursors are always read only
  • Slower and a memory hog, compared to other cursor types
  • Can go forwards – and backwards (Use FETCH PRIOR to go back a row)

Ref: Microsoft

Example Template For A Static Cursor

Here is a simple example of a Static Cursor

CREATE table #tmpcursor (id INT)
DECLARE @cursorvalue INT

INSERT INTO #tmpcursor
SELECT 1
UNION 
SELECT 2
UNION 
SELECT 3
UNION 
SELECT 4
UNION 
SELECT 5

DECLARE sample_cursor 
CURSOR 
STATIC -- this is where we tell SQL we want a static cursor

FOR
	SELECT id
	FROM #tmpcursor
	
OPEN sample_cursor
FETCH NEXT FROM sample_cursor
WHILE @@FETCH_STATUS = 0

     FETCH NEXT FROM sample_cursor
 CLOSE sample_cursor
 DEALLOCATE sample_cursor
 
 DROP TABLE #tmpcursor

Dynamic Cursors

Dynamic cursors are the opposite of static cursors.
Ref: Microsoft

  • Any changes to underlying data are immediately reflected
  • Due to allowable changes the data in the result set can be different for each fetch

Example Template For A Dynamic Cursor

Here is a simple example of a Dynamic Cursor

CREATE table #tmpcursor (id INT)
DECLARE @cursorvalue INT

INSERT INTO #tmpcursor
SELECT 1
UNION 
SELECT 2
UNION 
SELECT 3
UNION 
SELECT 4
UNION 
SELECT 5

DECLARE sample_cursor 
CURSOR 
DYNAMIC -- this is where we tell SQL we want a dynamic cursor

FOR
	SELECT id
	FROM #tmpcursor
	
OPEN sample_cursor
FETCH NEXT FROM sample_cursor
WHILE @@FETCH_STATUS = 0

     FETCH NEXT FROM sample_cursor
 CLOSE sample_cursor
 DEALLOCATE sample_cursor
 
 DROP TABLE #tmpcursor
 

Forward-Only Cursors

  • The cursor can only be scrolled from first to last
  • Because of the above point – FETCH NEXT is only command you can use for fetching
  • Fastest updateable cursor

Ref: Microsoft

Example Template For A Forward Only Cursor

Here is a simple example of a Forward Only Cursor

CREATE table #tmpcursor (id INT)
DECLARE @cursorvalue INT

INSERT INTO #tmpcursor
SELECT 1
UNION 
SELECT 2
UNION 
SELECT 3
UNION 
SELECT 4
UNION 
SELECT 5

DECLARE sample_cursor 
CURSOR 
FORWARD_ONLY -- this is where we tell SQL we want a FORWARD_ONLY cursor

FOR
	SELECT id
	FROM #tmpcursor
	
OPEN sample_cursor
FETCH NEXT FROM sample_cursor
WHILE @@FETCH_STATUS = 0

     FETCH NEXT FROM sample_cursor
 CLOSE sample_cursor
 DEALLOCATE sample_cursor
 
 DROP TABLE #tmpcursor
 

Keyset Driven Cursors

The Keyset cursor does it all, you can forwards, backwards, update etc.

  • The keyset is the set of the key values from all the rows that qualified for the SELECT statement at the time the cursor was opened
  • The keyset is created in tempdb
  • Changes to the db outside of the cursor are invisible to the cursor (unless its shut down and reopened)

Ref: Microsoft

Template For A Keyset Driven Cursor

Here is a simple example of a Keyset Driven Cursor

CREATE table #tmpcursor (id INT)
DECLARE @cursorvalue INT

INSERT INTO #tmpcursor
SELECT 1
UNION 
SELECT 2
UNION 
SELECT 3
UNION 
SELECT 4
UNION 
SELECT 5

DECLARE sample_cursor 
CURSOR 
KEYSET -- this is where we tell SQL we want a Keyset Driven cursor

FOR
	SELECT id
	FROM #tmpcursor
	
OPEN sample_cursor
FETCH NEXT FROM sample_cursor
WHILE @@FETCH_STATUS = 0

     FETCH NEXT FROM sample_cursor
 CLOSE sample_cursor
 DEALLOCATE sample_cursor
 
 DROP TABLE #tmpcursor
 

Study Resources for the 70-461 Exam

Training Kit (Exam 70-461) Querying Microsoft SQL Server 2012 (MCSA) (Microsoft Press Training Kit)
Microsoft Exam Page

Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

2 Comments

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.