What Is Cursor In SQL

A SQL Server Cursor is an object of a database that let you traverse over the rows of a result set. It allows you to process individual rows returned by a query. Unlike other SQL commands that operate on sets of records, a cursor allows a database programmer to process individual rows returned by queries.

You can start practicing the SQL Server CURSOR for free by creating a free account on FreeASPHosting.net and creating a free SQL Server Database.


  1. A SQL Server CURSOR allows traversal over rows of a result set, enabling the processing of individual rows returned by a query.
  2. The lifecycle of a SQL Server CURSOR involves several steps: DECLARE, OPEN, FETCH, CLOSE, and DEALLOCATE.
  3. The DECLARE CURSOR statement is used to define a cursor in SQL Server, specifying its characteristics and associated SELECT statement.
  4. The OPEN CURSOR statement is used to open the cursor, execute the SELECT statement and populate the result set.
  5. The FETCH statement retrieves data into variables and moves the cursor to the next row, while the CLOSE statement deactivates the cursor, and the DEALLOCATE statement deletes it from memory.

What is Cursor in SQL

What is Cursor SQL, let’s look at another definition. It's a database feature that allows developers to control record navigation, updation, and retrieval by providing a way to process individual rows returned by database system queries. A SQL Server Cursor essentially acts as a pointer to one row in a set of rows.

SQL Server Cursor

The lifecycle of a Cursor in SQL comprises several steps:

  2. OPEN
  3. FETCH
  4. CLOSE


The first step in the life cycle of a Cursor in SQL Server is the DECLARE CURSOR statement. It is used to define a Cursor in SQL.

The syntax to declare SQL Server CURSOR looks very cumbersome. As this is a beginner-level article we are going to skip its details. Better to start directly from a real word example!

After reading this article, we recommend you also look at the SQL Server's official documentation for detailed syntax and different types of Cursors in SQL Server (STATIC, KEYSET, DYNAMIC, FAST_FORWARD).

The syntax of a DECLARE SQL CURSOR command in SQL Server looks like the following (don’t be afraid and keep reading, you will learn a lot):

FOR select_statement 
[ FOR UPDATE [ OF column_name [ ,...n ] ] ]


The second step is the OPEN CURSOR statement, which is used to open the SQL Server Cursor. This statement executes the SQL SELECT statement defined in the cursor declaration and populates the result set. It sets the position of the Cursor in SQL Server to just before the first row.

The OPEN CURSOR syntax is simply:

OPEN cursor_name


The FETCH statement is used to retrieve/fetch data into variables and move to a row in the SQL Server CURSOR .

The simplest syntax of the FETCH statement is

FETCH [ NEXT | PRIOR | FIRST | LAST | ABSOLUTE { n | @nvar } | RELATIVE { n | @nvar } ]
FROM cursor_name
INTO @Variable[,..n]


The CLOSE statement is used to deactivate the Cursor in SQL Server and free the resources. It does not delete the definition of the cursor.

The syntax is

CLOSE cursor_name


Finally, the DEALLOCATE statement is used to delete the Cursor in SQL Server from memory. It removes the definition of the cursor.

The syntax DEALLOCATE Cursor SQL is:

DEALLOCATE cursor_name

SQL Cursor Example

As previously discussed, a Cursor in SQL follows a lifecycle that comprises several steps. We have already described the basic syntax for each step. Now, let's see an SQL Server Cursor example with actual data.

Let's say we have a table named 'employees' with the following data:

what is cursor in sql

We will use a SQL Cursor to increment each employee's salary by 10%.

DECLARE @id INT, @name VARCHAR(50), @salary INT;

DECLARE salary_cursor CURSOR FOR
SELECT id, name, salary
FROM employees;

OPEN salary_cursor;

FETCH NEXT FROM salary_cursor
INTO @id, @name, @salary;

    SET @salary = @salary * 1.1;

    UPDATE employees
    SET salary = @salary
    WHERE CURRENT OF salary_cursor;

    FETCH NEXT FROM salary_cursor
    INTO @id, @name, @salary;

CLOSE salary_cursor;
DEALLOCATE salary_cursor;

In this SQL Server Cursor example, we first declare variables to hold the values that will be fetched from the cursor. Then we declare the cursor “salary_cursor” and define a SELECT statement to fetch rows from the “employees” table. After that, we open the cursor and fetch the first row, and put the values into the variables we declared at the start.

The WHILE loop continues fetching the next row and updating the salary until all rows are processed. The “WHERE CURRENT OF” clause points to the most recently fetched row of the cursor. Finally, the cursor is closed and deallocated.

The result of the above SQL Server CURSOR would be:

sql server cursor

SQL Server WHILE Loop

Apart from the SQL Server Cursor, another method to perform row-by-row operations is to use the WHILE Loop. The WHILE Loop in SQL Server allows the repeated execution of a statement block until a specified condition is met.

Here is the same operation performed with a WHILE loop and without using a SQL Server Cursor:

DECLARE @id INT = 1;

WHILE @id <= (SELECT MAX(id) FROM employees)
    UPDATE employees
    SET salary = salary * 1.1
    WHERE id = @id;

    SET @id = @id + 1;


what is cursor in sql

In this example, the WHILE loop is used to iterate through the employee id's one at a time and update the corresponding salary. This is also an alternative to using a Cursor in SQL Server. But remember to use this example we must have an “id” column with unique values like a primary key.

Final Words for SQL Server Cursor

Understanding What is Cursor in SQL and its life cycle is crucial for handling row-by-row operations in SQL Server. While the T-SQL Cursor (this article is about) provides a powerful tool for such operations, it's always a good practice to look for set-based alternatives to ensure better performance. Use the TSQL Cursor when absolutely necessary, and always remember to close and deallocate your cursors when they are no longer needed.

SQL Server CURSOR is especially beneficial when operations on the entire data set are not feasible. SQL Cursors can lead to performance issues if not handled properly because they process data one row at a time. Therefore, always consider whether a set-based operation could solve your problem more efficiently before resorting to cursors. Nevertheless, understanding how to utilize a Cursor in SQL will undoubtedly make you a more effective SQL programmer.

  1. Creating Tables with SSMS
  4. SQL DELETE Statement
  5. SQL Update Statement
  6. Where clause in SQL
  1. SQL Nested Query
  2. SQL JOIN Types
  3. Left Outer Join
  4. SQL CTE
  5. CTE vs Temp Table
  7. What Is Cursor In SQL