Type of cursor in sql server

Static Cursors

A static cursor populates the result set at the time of cursor creation and query result is cached for the lifetime of the cursor. A static cursor can move forward and backward direction. A static cursor is slower and uses more memory in comparison to other cursor. Hence you should use it only if scrolling is required and other types of cursors are not suitable. We can't update, delete data using static cursor. It is not sensitive to any changes to the original data source. By default static cursors are scrollable.

Dynamic Cursors

A dynamic cursor allows you to see the data updating, deletion and insertion in the data source while the cursor is open. Hence a dynamic cursor is sensitive to any changes to the data source and supports update, delete operations. By default dynamic cursors are scrollable.

Forward Only Cursors

A forward only cursor is the fastest cursor among the all cursors but it doesn't support backward scrolling. We can update, delete data using forward only cursor. It is sensitive to any changes to the original data source.

There are three more types of Forward Only Cursors.

Ø  Forward_Only KEYSET,

Ø  FORWARD_ONLY STATIC and

Ø  FAST_FORWARD

A FORWARD_ONLY STATIC Cursor is populated at the time of creation and cached the data to the cursor lifetime. It is not sensitive to any changes to the data source.

A FAST_FORWARD Cursor is the fastest cursor and it is not sensitive to any changes to the data source.

Keyset Driven Cursors

A keyset driven cursor is controlled by a set of unique identifiers as the keys in the keyset. The keyset depends on all the rows that qualified the SELECT statement at the time of cursor was opened. A keyset driven cursor is sensitive to any changes to the data source and supports update, delete operations. By default keyset driven cursors are scrollable.

Example of static cursor

Here I am feting the data from a table and using cursor I am printing these records.

DECLARE @Id int DECLARE @name varchar(50) DECLARE @empAdd varchar(50) DECLARE @Mobile Varchar(10) DECLARE cur_emp CURSOR STATIC FOR select * from emp OPEN cur_emp IF @@CURSOR_ROWS > 0  BEGIN  FETCH NEXT FROM cur_emp INTO @Id,@name,@empAdd,@Mobile  WHILE @@Fetch_status = 0  BEGIN  PRINT 'ID : '+ convert(varchar(20),@Id)+', Name : '+@name+   ', Address : '+ @empAdd + ' , Mobile number: '+ @Mobile  FETCH NEXT FROM cur_emp INTO @Id,@name,@empAdd,@Mobile  END END CLOSE cur_emp DEALLOCATE cur_emp

See the output

Example of Dynamic Cursors

See the example

DECLARE @Id int DECLARE @name varchar(50) DECLARE @empAdd varchar(50) DECLARE @Mobile Varchar(10) DECLARE Dynamic_cur_empupdate CURSOR DYNAMIC FOR SELECT * FROM EMP OPEN Dynamic_cur_empupdate IF @@CURSOR_ROWS > 0  BEGIN  FETCH NEXT FROM Dynamic_cur_empupdate INTO @Id,@name,@empAdd,@Mobile  WHILE @@Fetch_status = 0  BEGIN  IF @ID = 3  update emp set empAdd='Buxar' where ID=3    FETCH NEXT FROM Dynamic_cur_empupdate INTO @Id,@name,@empAdd,@Mobile  END END CLOSE Dynamic_cur_empupdate DEALLOCATE Dynamic_cur_empupdate

Example of Forward Only Cursors

Below is the example of Forward only cursor.

DECLARE @Id int DECLARE @name varchar(50) DECLARE @empAdd varchar(50) DECLARE @Mobile Varchar(10) DECLARE Forward_only_cur_empupdate CURSOR FORWARD_ONLY FOR SELECT * FROM EMP OPEN Forward_only_cur_empupdate IF @@CURSOR_ROWS > 0  BEGIN  FETCH NEXT FROM Forward_only_cur_empupdate INTO @Id,@name,@empAdd,@Mobile  WHILE @@Fetch_status = 0  BEGIN  IF @ID = 3  update emp set empAdd='Buxar' where ID='3'    FETCH NEXT FROM Forward_only_cur_empupdate INTO @Id,@name,@empAdd,@Mobile  END END CLOSE Forward_only_cur_empupdate DEALLOCATE Forward_only_cur_empupdate SELECT * FROM EMP

Example of Keyset Driven Cursor

Below is the example of Keyset driven cursor.

DECLARE @Id int DECLARE @name varchar(50)  DECLARE Keyset_cur_empupdate CURSOR KEYSET FOR SELECT ID,Name FROM EMP order BY ID OPEN Keyset_cur_empupdate IF @@CURSOR_ROWS > 0  BEGIN  FETCH NEXT FROM Keyset_cur_empupdate INTO @Id,@name  WHILE @@Fetch_status = 0  BEGIN  IF @name='Bagi'  Update Emp SET Mobile='888880ABCD' WHERE CURRENT OF Keyset_cur_empupdate  FETCH NEXT FROM Keyset_cur_empupdate INTO @Id,@name  END END CLOSE Keyset_cur_empupdate DEALLOCATE Keyset_cur_empupdate  Go Select * from Emp