SQL Cursor

A SQL Cursor is a database object to retrieve data from a result set one row at a time, instead of the Transact-SQL commands that operate on all the rows in the result set at a time. We use cursor when we need to update records in a database table in singleton fashion which means row by row.

Advantages of using SQL Cursors

SQL cursors provide an alternative way to retrieve data from result set instead of using WHILE loop, sub queries, Temporary tables and Table variables when there is no option except using cursor to check the integrity of data.

Disadvantages of using SQL Cursors

SQL Cursors impact the performance of the SQL Server since it uses the SQL Server instance’s memory, reduce concurrency, decrease network bandwidth and lock resources, so it increases the overhead to the database server. Hence it is mandatory to understand the cursor types and its functions so that we can use suitable cursor according to our requirements

CREATE a SQL Cursor

Cursor consists of:

·         Declare Cursor:

 A cursor is declared by defining the SQL statement that returns a result set.

Syntax:

DECLARE cursor_name CURSOR

[LOCAL | GLOBAL] -- define cursor scope, by default it is LOCAL

[FORWARD_ONLY | SCROLL] -- define cursor movements (forward/backward), by default  it is FORWARD_ONLY

[STATIC | KEYSET | DYNAMIC | FAST_FORWARD] -- basic type of cursor

[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] -- define locks

FOR select_statement -- define SQL Select statement

FOR UPDATE [col1,col2,...coln] -- define columns that need to be updated

Rules:

·         If the SCROLL option is not specified in an ISO style DECLARE CURSOR statement, NEXT is the         only FETCH option supported.

·         If SCROLL is specified in an ISO style DECLARE CURSOR, all FETCH options are supported.

·         When the Transact-SQL DECLARE cursor extensions are used, these rules apply:

·         If either FORWARD_ONLY or FAST_FORWARD is specified, NEXT is the only FETCH option               supported.

·         If DYNAMIC, FORWARD_ONLY or FAST_FORWARD are not specified, and one of KEYSET,               STATIC, or SCROLL are specified, all FETCH options are supported.

·         DYNAMIC SCROLL cursors support all the FETCH options except ABSOLUTE.

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 we 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 STATIC, FAST_FORWARD and KEYSET driven cursor.

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

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

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

·         Open

A Cursor is opened and populated by executing the SQL statement defined by the cursor.

Syntax:

OPEN [GLOBAL] cursor_name -- by default it is local

·         Fetch

When cursor is opened, rows can be fetched from the cursor one by one or in a block to do the data manipulation.

Syntax:

FETCH [NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE n]

FROM [GLOBAL] cursor_name      INTO @Variable_name[1,2,..n]

-- Fetch the last row in the cursor. 

FETCH LAST FROM contact_cursor; 

-- Fetch the row immediately prior to the current row in the cursor. 

FETCH PRIOR FROM contact_cursor; 

-- Fetch the second row in the cursor. 

FETCH ABSOLUTE 2 FROM contact_cursor; 

-- Fetch the row that is three rows after the current row. 

FETCH RELATIVE 3 FROM contact_cursor; 

-- Fetch the row that is two rows prior to the current row. 

FETCH RELATIVE -2 FROM contact_cursor; 

·         Close

After data manipulation, we should close the cursor explicitly.

Syntax:

CLOSE cursor_name -- after closing it can be reopen

·        Deallocate

Finally, we need to delete the cursor definition and released all the system resources associated with the cursor.

Syntax:

DEALLOCATE cursor_name -- after closing it can’t be reopen

EXAMPLE of Static Cursor 

 

SET NOCOUNT ON

DECLARE @EmployeeID int
DECLARE @EmployeeName varchar(50)
DECLARE @Salary int

-- Declare Cursor

DECLARE CUR_Employee CURSOR STATIC FOR

       SELECT EmployeeID,EmployeeName,Salary FROM dbo.Employee WITH (NOLOCK)

-- Open Cursor

OPEN CUR_Employee

IF @@CURSOR_ROWS > 0

BEGIN

       -- Fetch the first row from Cursor

       FETCH NEXT FROM CUR_Employee INTO @EmployeeID, @EmployeeName, @Salary

       WHILE @@FETCH_STATUS = 0 -- Successfully retrieved a record from Cursor

       BEGIN

              PRINT 'EmployeeID : '+ CONVERT(VARCHAR(20),@EmployeeID) +

                       ', EmployeeName : ' +    @EmployeeName +

                       ', Salary : '+ CONVERT(VARCHAR(20),@Salary)

              -- Fetch the next row from Cursor

              FETCH NEXT FROM CUR_Employee INTO @EmployeeID, @EmployeeName, @Salary

       END

END

-- Close Cursor

CLOSE CUR_Employee

-- Delete Cursor

DEALLOCATE CUR_Employee

SET NOCOUNT OFF

EXAMPLE of Scroll Cursor 

SET NOCOUNT ON

DECLARE @EmployeeID int
DECLARE @EmployeeName varchar(50)
DECLARE @Salary int

-- Declare Cursor

DECLARE CUR_Employee SCROLL CURSOR FOR

       SELECT EmployeeID,EmployeeName,Salary FROM dbo.Employee WITH (NOLOCK)

-- Open Cursor

OPEN CUR_Employee

IF @@CURSOR_ROWS > 0

BEGIN

      
       -- Fetch the 2nd row from Cursor

       FETCH ABSOLUTE 2 FROM CUR_Employee INTO @EmployeeID, @EmployeeName, @Salary

              PRINT 'EmployeeID : '+ CONVERT(VARCHAR(20),@EmployeeID) +

                ', EmployeeName : ' +    @EmployeeName +

                ', Salary : '+ CONVERT(VARCHAR(20),@Salary)

       -- Fetch the 2nd row from the ABSOLUTE position

       FETCH RELATIVE 2 FROM CUR_Employee INTO @EmployeeID, @EmployeeName, @Salary

              PRINT 'EmployeeID : '+ CONVERT(VARCHAR(20),@EmployeeID) +

                ', EmployeeName : ' +    @EmployeeName +

                ', Salary : '+ CONVERT(VARCHAR(20),@Salary)

      

       -- Fetch the last row in the cursor. 

       FETCH LAST FROM CUR_Employee INTO @EmployeeID, @EmployeeName, @Salary

              PRINT 'EmployeeID : '+ CONVERT(VARCHAR(20),@EmployeeID) +

         ', EmployeeName : ' +    @EmployeeName +

         ', Salary : '+ CONVERT(VARCHAR(20),@Salary)

       -- Fetch the row immediately prior to the current row in the cursor.

       FETCH PRIOR FROM CUR_Employee INTO @EmployeeID, @EmployeeName, @Salary

              PRINT 'EmployeeID : '+ CONVERT(VARCHAR(20),@EmployeeID) +

         ', EmployeeName : ' +    @EmployeeName +

         ', Salary : '+ CONVERT(VARCHAR(20),@Salary)

END

-- Close Cursor

CLOSE CUR_Employee

-- Delete Cursor

DEALLOCATE CUR_Employee

SET NOCOUNT OFF