Static Cursor, Dynamic Cursor, and Keyset-Driven Cursor in SQL Server

In this article, I describe Static Cursor, Dynamic Cursor, and Keyset-Driven Cursor.

Before reading this article visit,Cursor in SQL Server.
 

Introduction

 
In this article, I describe Static Cursor, Keyset-Driven Cursor, and Dynamic Cursor.
 
First, we are creating a table.
 
Creation of table
  1. create table emp(empId int, empName varchar(10))    
Insertion of data
  1. insert into emp  
  2. select 1,'d'union all  
  3. select 2,'e'union all  
  4. select 3,'e'union all  
  5. select 4,'p'union all  
  6. select 5,'a'union all  
  7. select 6,'k'  
Output
  1. select * from emp
cursor-in-sql-server.jpg 
 

Static Cursor

 
A Static Cursor shows the result set as it was when the cursor was opened. If we make change to the table after the opening of Static Cursor then these changes are not reflected in the cursor. For example, if after opening the cursor we delete a row, and fetch that record by the cursor, then the cursor shows that record.
 
Syntax
 
Declare cursorName cursor
static for
statement
 
Here cursorName is the name of your cursor, static is a keyword, and statement is the SQL statement that you are used in the cursor. An example is given below.
 

Creation of Static Cursor 

  1. declare static_cursor cursor  
  2. static for   
  3. select * from emp  

Opening of Static Cursor

  1. open static_cursor  

Fetching data from Static Cursor


Fetching first data from Static Cursor
  1. fetch first from static_cursor  
Output
 
cursor-in-sql-server-first.jpg 
 
Fetching next data from Static Cursor
  1. fetch next from static_cursor  
Output
 
cursor-in-sql-server-next.jpg 
 
Fetching last data from Static Cursor
  1. fetch last from static_cursor  
Output
 
cursor-in-sql-server-last.jpg 
 
Fetching second data from Static Cursor
  1. fetch absolute 2 from static_cursor  
Output
 
cursor-in-sql-server-next.jpg 
 
Fetching next 2nd data from Static Cursor
  1. fetch relative 2 from static_cursor  
Output
 
cursor-in-sql-server-4.jpg 
 

Closing of Static Cursor

  1. close static_cursor  

Deallocating Static Cursor

  1. deallocate static_cursor  

Dynamic Cursor

 
Dynamic Cursor is the opposite of the Static Cursor. When we make changes in the table after opening the Dynamic Cursor and scroll through a Dynamic Cursor all changes made to the table reflects the Dynamic Cursor. For example, If we delete the record from the table after the opening of the Dynamic Cursor, we can't access that record.
 
Syntax
 
Declare cursorName cursor
Dynamic for
statement
 
Here cursorName is the name of your cursor, Dynamic is a keyword, and statement is the SQL statement that you are used in cursor. An example is given below.
 

Creation of Dynamic Cursor

  1. declare dynamic_cursor cursor  
  2. dynamic for   
  3. select * from emp  

Opening of Dynamic Cursor

  1. open dynamic_cursor  
Fetching first data from Dynamic Cursor
  1. fetch First from dynamic_cursor  
Output
 
cursor-in-sql-server-first.jpg 
 
Fetching next data from Dynamic Cursor
  1. fetch next from dynamic_cursor  
Output
 
cursor-in-sql-server-next.jpg 
 
We can fetch last, relative and absolute data same as Static Cursor.
 

Closing of Dynamic Cursor

  1. close dynamic_cursor  

Deallocating  Dynamic Cursor

  1. deallocate dynamic_cursor  

Keyset-Driven Cursor

 
When we open keyset-driven cursor then it creates a list of unique values in the tempdb database. These values are called keyset. Every keyset uniquely identifies a single row in the result set.
 
A Keyset is created after the opening of the cursor so the number of rows is fixed until we close the cursor.
 

Creation of Keyset-driven cursor

  1. declare key_cursor cursor  
  2. keyset for   
  3. select * from emp  

Opening of Keyset-driven cursor

  1. open key_cursor  

Fetching data from Keyset-Driven cursor 

 
Fetching first data from Keyset-driven cursor
  1. fetch First from key_cursor  
Output
 
cursor-in-sql-server-first.jpg 
 
Fetching next data from Keyset-driven cursor
  1. fetch next from key_cursor  
Output
 
cursor-in-sql-server-next.jpg 
 

Closing of Keyset Drivin cursor

  1. close key_cursor  

Deallocation of memory

  1. deallocate key_cursor  

Summary

 
In this article, I described Static Cursor, Dynamic Cursor, and Keyset-Driven Cursor. I hope this article has helped you in understanding this topic. Please share it. If you know more about this, your feedback and constructive contributions are welcome.