Browse Articles

Cursors In SQL Server

08 Jan 2024
Intermediate
394K Views
5 min read  

Cursors In SQL Server: An Overview

In this SQL Server Tutorial, the cursor is a database object that retrieves 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 a cursor when we need to update records in a database table in a singleton fashion, which means row by row in this SQL Server Course.

Cursor in SQL Server

In SQL Server, a cursor is a database object that allows us to retrieve and manipulate each row one at a time. A cursor is nothing more than a row pointer. It is always used alongside a SELECT command in SQL. It is typically a set of SQL logic that loops through a set number of rows one by one.

Life Cycle of Cursor

The life cycle of a cursor can be divided into five phases, as follows:

  1. Declare Cursor
  2. Open Cursor
  3. Fetch Cursor
  4. Close Cursor
  5. Deallocate Cursor

Life Cycle of Cursor

1. Declare Cursor

Declares and associates a cursor variable with a certain SELECT statement.

SQL Statement

DECLARE cursor_name CURSOR FOR

SELECT column1, column2, ...

FROM table_name

WHERE condition;

2. Open Cursor

Opens the cursor, making it possible to retrieve rows.

SQL Statement

OPEN cursor_name;

3. Fetch Cursor

Retrieves the next row from the cursor's result set.

SQL Statement

FETCH cursor_name INTO variable1, variable2, ...;

4. Close Cursor

The cursor is closed, freeing the resources connected with it.

SQL Statement

CLOSE cursor_name;

5. Deallocate Cursor

The cursor definition is removed and its resources are released.

SQL Statement

DEALLOCATE cursor_name;

Types of Cursors in SQL Server

The different types of cursors in SQL Server are given below:

  1. Static Cursors
  2. Dynamic Cursors
  3. Forward-Only Cursors
  4. Keyset Cursors

Types of Cursors in SQL Server

1. Static Cursors

SQL Server static cursors retrieve a snapshot of the result set at the moment of cursor formation and keep that snapshot for the duration of the cursor's life. Changes made to the underlying data after the cursor is created do not affect the result set displayed by the cursor.

2. Dynamic Cursors

SQL Server dynamic cursors are more flexible because they reflect changes in the underlying data, updating the result set dynamically as changes occur. Because of the dynamic nature of the result set, this form of cursor allows for real-time updates, although it may incur additional expense.

3. Forward-Only Cursors

Forward-only cursors in SQL Server allow for only forward traversal of the result set. They are designed for forward scrolling, making them more efficient for one-way data access and consuming less overall resources than other cursor kinds.

4. Keyset Cursors

In SQL Server, keyset cursors keep a static set of SQL keys from the result set, enabling changes to the data but prohibiting changes to the key values. This strikes a balance between the fixed nature of static cursors and the dynamic adaptability of dynamic cursors, allowing for quick navigation and change.

Limitations of SQL Server Cursor

A cursor has some limitations, thus it should only be used when there are no other options. The limitations are as follows:

  • Cursor uses network resources by requiring a network roundtrip for every record it retrieves.
  • A cursor is a memory resident set of pointers, which implies it consumes memory that other processes on our machine could use.
  • When processing data, it puts locks on a section of the database or the full table.
  • The cursor's performance and speed are slower since it updates table records one row at a time.
  • While cursors are faster than loops, they have additional overhead.
  • Another factor influencing cursor speed is the number of columns and rows brought into the cursor. It is the amount of time required to open your cursor and perform a fetch statement.
Summary

Cursors provide row-by-row control in SQL Server, allowing operations that set-based commands cannot. Understand their lifecycle, types, and restrictions so that you may employ them efficiently as needed, balancing their benefits against any performance drawbacks.

FAQs

Q1. What is a cursor and what are the different types of cursor?

A cursor holds information about a select statement and the rows of data that it accesses. A cursor is a program that is used to retrieve and process the rows returned by the SQL statement one at a time. Cursors are classified into two types: 
  1. Implicit Cursors
  2. Explicit Cursors

Q2. What is the example of cursor?

Cursors are frequently used to draw attention to text or objects on the screen so that they can be selected. In a word processor, for example, the cursor can be used to select text, format it, and enter new text. Cursors are controlled by users via input devices such as mice, touchpads, and trackballs.

Q3. What are SQL Server cursor attributes?

Each cursor contains a set of properties that allow an application programme to test the cursor's condition. These are the properties %ISOPEN, %FOUND, %NOTFOUND, and %ROWCOUNT. This characteristic is used to identify whether or not a cursor is open.

Q4. Which is called cursor?

A cursor is an indicator on a computer display screen that indicates where a user can enter text. In a graphical user interface (GUI) operating system, the cursor is also a visible and moving pointer that the user controls with a mouse, touchpad, or similar input device.

Q5. What are the five different cursors?

Cursor forms are classified into five types: point, polygon, rectangular box, circle, and ellipse.
Share Article
About Author
Shailendra Chauhan (Microsoft MVP, Founder & CEO at Scholarhat by DotNetTricks)

Shailendra Chauhan is the Founder and CEO at ScholarHat by DotNetTricks which is a brand when it comes to e-Learning. He provides training and consultation over an array of technologies like Cloud, .NET, Angular, React, Node, Microservices, Containers and Mobile Apps development. He has been awarded Microsoft MVP 8th time in a row (2016-2023). He has changed many lives with his writings and unique training programs. He has a number of most sought-after books to his name which has helped job aspirants in cracking tough interviews with ease.
Learn to Crack Your Technical Interview

Accept cookies & close this