Web Geek Step By Step Tutorial

Web Geek Questions and Answers

Corporate Training

Hey! I'm Shailendra Chauhan having more than 6 years of hand over Microsoft .NET technologies and other web technologies. I am available for coporate training on latest technologies like ASP.NET MVC, AngularJS, NodeJS, Entity Framework, Mobile development etc.

Campus Training

Dot Net Tricks is very dedicated to provide high quality training for students. If you would like to conduct a campus training then Dot Net Tricks is the best choice. Our finest trainer will train you in your campus.

Industrial/Summar Training

About Us

Hey! I'm Shailendra Chauhan full-time author, consultant & trainer. I have more than 6 years of hand over Microsoft .NET technologies and other web technologies. I am the founder & chief editor of www.dotnet-tricks.com & www.webgeekschool.com.

I like to share my working experience, research and knowledge through my well-known blog.

Advertise with Us

Dot Net Tricks is very dedicated to provide high quality technical content that is completely free world wide. If you would like to advertise your's product to
specific audience like college students, developers, programmers & software professionals
then Dot Net Tricks is the best choice for placing your product's ad.

A Cursor allow us to retrieve data from a result set in singleton fashion means row by row. Cursor are required when we need to update records in a database table one row at a time. I have already explained the basic of cursor.

A Cursor impacts the performance of the SQL Server since it uses the SQL Server instances' memory, reduce concurrency, decrease network bandwidth and lock resources. Hence it is mandatory to understand the cursor types and its functions so that you can use suitable cursor according to your needs.

You should avoid the use of cursor. Basically you should use cursor alternatives like as WHILE loop, sub queries, Temporary tables and Table variables. We should use cursor in that case when there is no option except cursor.

Types of Cursors

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 use 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.

No UPDATE, INSERT, or DELETE operations are reflected in a static cursor (unless the cursor is closed and reopened). By default static cursors are scrollable. SQL Server static cursors are always read-only.

Dynamic Cursors

A dynamic cursor allows you to see the data updation, 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. You 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.

Dynamic Cursor - Example

--Dynamic Cursor for Update
SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
DECLARE Dynamic_cur_empupdate CURSOR
DYNAMIC
FOR
SELECT EmpID,EmpName from Employee ORDER BY EmpName
OPEN Dynamic_cur_empupdate
IF @@CURSOR_ROWS > 0
BEGIN
FETCH NEXT FROM Dynamic_cur_empupdate INTO @Id,@name
WHILE @@Fetch_status = 0
BEGIN
IF @name='Mohan'
Update Employee SET Salary=15000 WHERE CURRENT OF Dynamic_cur_empupdate
FETCH NEXT FROM Dynamic_cur_empupdate INTO @Id,@name
END
END
CLOSE Dynamic_cur_empupdate
DEALLOCATE Dynamic_cur_empupdate
SET NOCOUNT OFF
Go
Select * from Employee

-- Dynamic Cursor for DELETE
SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
DECLARE Dynamic_cur_empdelete CURSOR
DYNAMIC
FOR
SELECT EmpID,EmpName from Employee ORDER BY EmpName
OPEN Dynamic_cur_empdelete
IF @@CURSOR_ROWS > 0
BEGIN
FETCH NEXT FROM Dynamic_cur_empdelete INTO @Id,@name
WHILE @@Fetch_status = 0
BEGIN
IF @name='Deepak'
DELETE Employee WHERE CURRENT OF Dynamic_cur_empdelete
FETCH NEXT FROM Dynamic_cur_empdelete INTO @Id,@name
END
END
CLOSE Dynamic_cur_empdelete
DEALLOCATE Dynamic_cur_empdelete
SET NOCOUNT OFF
Go
Select * from Employee

Forward Only Cursor - Example

--Forward Only Cursor for Update
SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
DECLARE Forward_cur_empupdate CURSOR
FORWARD_ONLY
FOR
SELECT EmpID,EmpName from Employee ORDER BY EmpName
OPEN Forward_cur_empupdate
IF @@CURSOR_ROWS > 0
BEGIN
FETCH NEXT FROM Forward_cur_empupdate INTO @Id,@name
WHILE @@Fetch_status = 0
BEGIN
IF @name='Amit'
Update Employee SET Salary=24000 WHERE CURRENT OF Forward_cur_empupdate
FETCH NEXT FROM Forward_cur_empupdate INTO @Id,@name
END
END
CLOSE Forward_cur_empupdate
DEALLOCATE Forward_cur_empupdate
SET NOCOUNT OFF
Go
Select * from Employee

-- Forward Only Cursor for Delete
SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
DECLARE Forward_cur_empdelete CURSOR
FORWARD_ONLY
FOR
SELECT EmpID,EmpName from Employee ORDER BY EmpName
OPEN Forward_cur_empdelete
IF @@CURSOR_ROWS > 0
BEGIN
FETCH NEXT FROM Forward_cur_empdelete INTO @Id,@name
WHILE @@Fetch_status = 0
BEGIN
IF @name='Sonu'
DELETE Employee WHERE CURRENT OF Forward_cur_empdelete
FETCH NEXT FROM Forward_cur_empdelete INTO @Id,@name
END
END
CLOSE Forward_cur_empdelete
DEALLOCATE Forward_cur_empdelete
SET NOCOUNT OFF
Go
Select * from Employee

Keyset Driven Cursor - Example

-- Keyset driven Cursor for Update
SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
DECLARE Keyset_cur_empupdate CURSOR
KEYSET
FOR
SELECT EmpID,EmpName from Employee ORDER BY EmpName
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='Pavan'
Update Employee SET Salary=27000 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
SET NOCOUNT OFF
Go
Select * from Employee

-- Keyse Driven Cursor for Delete
SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
DECLARE Keyset_cur_empdelete CURSOR
KEYSET
FOR
SELECT EmpID,EmpName from Employee ORDER BY EmpName
OPEN Keyset_cur_empdelete
IF @@CURSOR_ROWS > 0
BEGIN
FETCH NEXT FROM Keyset_cur_empdelete INTO @Id,@name
WHILE @@Fetch_status = 0
BEGIN
IF @name='Amit'
DELETE Employee WHERE CURRENT OF Keyset_cur_empdelete
FETCH NEXT FROM Keyset_cur_empdelete INTO @Id,@name
END
END
CLOSE Keyset_cur_empdelete
DEALLOCATE Keyset_cur_empdelete
SET NOCOUNT OFF
Go Select * from Employee

Summary

In this article I try to explain the types of Cursor in SQL Server with a simple example. I hope after reading this article you will be able to understand different types of cursors in Sql Server. I would like to have feedback from my blog readers. Please post your feedback, question, or comments about this article.

This is my personal blog and having articles on .net and others .net related technologies.
The opinions expressed here are my own and not belongs to my employer or other organization.
I have listed my own learning experience on this blog.