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 use cursor when we need to update records in a database table in singleton fashion means row by row.
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.
Life Cycle of Cursor
1. Declare Cursor
A cursor is declared by defining the SQL statement that returns a result set.
A Cursor is opened and populated by executing the SQL statement defined by the cursor.
When cursor is opened, rows can be fetched from the cursor one by one or in a block to do data manipulation.
After data manipulation, we should close the cursor explicitly.
Finally, we need to delete the cursor definition and released all the system resources associated with the cursor.
Types of Cursors
1. 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.
You 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.
2. 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.
3. 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.
4. 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.
Syntax to Declare Cursor
Declare Cursor SQL Comaand is used to define the cursor with many options that impact the scalablity and loading behaviour of the cursor. The basic syntax is given below
Syntax to Open Cursor
A Cursor can be opened locally or globally. By default it is opened locally. The basic syntax to open cursor is given below:
Syntax to Fetch Cursor
Fetch statement provides the many options to retrieve the rows from the cursor. NEXT is the default option. The basic syntax to fetch cursor is given below:
Syntax to Close Cursor
Close statement closed the cursor explicitly. The basic syntax to close cursor is given below:
Syntax to Deallocate Cursor
Deallocate statement delete the cursor definition and free all the system resources associated with the cursor. The basic syntax to close cursor is given below:
SQL SERVER – Simple Examples of Cursors
Static Cursor - Example
Dynamic Cursor - Example
Forward Only Cursor - Example
Keyset Driven Cursor - Example
Real world example :-
> Generating Sample of Sample on basis of various parameter and assigning value to different record & firms.
> Iteration of records to generate statically reports.
>Uses more resources because Each time you fetch a row from the cursor,
>Use Server RAM
>Because of the round trips, performance and speed is slow
Alternatives to using a cursor:
>Perform multiple queries
>Use temp tables
>Use derived tables
>Use correlated sub-queries
>Use the CASE statement