Data Haunting

Easy Solutions Destination


Understand Sql Server Cursor with Example

Operations in a relational database act on a complete set of rows. This complete set of rows returned by the statement is known as the result set. Sql server Cursor is a database objects to retrieve data from a result set one row at a time, instead of the T-SQL commands that operate on all the rows in the result set at one time. We can use cursor when we need to update records in a database table row by row. In this article we will see how to use Sql Server Cusror with Example.

For example Applications, especially interactive online applications, cannot always work effectively with the entire result set as a unit. These applications need a mechanism to work with one row or a small block of rows at a time. Cursors are an extension to result sets that provide that mechanism. Let’s see some fundamental basics of Sql Server Cursor

Using Sql server Cursors can perform following Process

  1. Cursor can retrieve one row or block of rows from the current position in the result set.
  2. Cursor can allow positioning at specific rows of the data base result set.
  3. Using Cursor data can modify to the rows at the current position in the result set.
  4. Cursor Provides Transact-SQL statements in scripts, stored procedures, and triggers access to the data in a result set.
  5. Cursor Supports different levels of visibility to changes made by other users to the database data that is presented in the result set.

Life Cycle of Sql server Cursor

  1. Declare Cursor
  2. A cursor is declared by defining the SQL statement that returns a result set.

  3. Open
  4. Cursor Opens a Transact-SQL server cursor and populates the cursor by executing the Transact-SQL statement specified on the DECLARE CURSOR.

  5. Fetch
  6. When cursor is opened, rows can be retrieved from the cursor one by one or in a block to do make operation and manipulation.

  7. Close
  8. After retrieving and manipulated the Data, we have to close cursor explicity.

  9. Deallocate
  10. At last need to release all the resources which are occupied by cursor, by deleting cursor defination.

How to Declare Cursor

DECLARE CURSOR accepts both a syntax based on the ISO standard and a syntax using a set of Transact-SQL extensions.

How to Open Cursor

Cursor default open as local. It can be open Globally or Locally. The basic Syntax to open Cursor is given below.

How to Fetch Cursor

Next is the default option for fetch Cursor. It provides many options to retrieve the rows from the Cursor. The basic Syntax to fetch Cursor is given below.

How to Close Cursor

Close statement explicitly close the Cursor. The basic Syntax to close Cursor is given below.

How to Deallocate Cursor

Deallocate Cursor will release resources as well delete the Cursor Defination. The basic Syntax to Deallocate Cursor is given below.

Simple example of Sql server Cursor

Suppose we have following Customer Table which have Customer id, Customer Name, Contact No and Address.

In DataBase table we have customer records as given below.

sql server cursor example

Now suppose you want to achieve sql server database table data in given format.

sql server cursor fetch data example

You can achieve above format using Sql server’s Cursor using following sql server’s script.

Limitation and Restriction of Cursor

You cannot use cursors on a table with a clustered column store index. This restriction does not apply to non clustered column store indexes. you can use cursors on a table with a non clustered column store index.

What is your opinion ?

Here we have seen how to use Sql server’s Cursors with simple example. I hope this will be useful to you understand the basic concepts of Sql server Cursor. If you have any query and question, you can comment or you can mail me.

Leave a Reply