What is a cursor in SQL Server?
In SQL Server, a cursor is a database object that allows you to retrieve and manipulate rows from a result set one at a time. Cursors are useful when you need to perform operations on each row of a result set rather than on the set as a whole.
There are several types of cursors in SQL Server, including.
- Forward-only cursors: These cursors allow you to move through a result set only in the forward direction, from the first row to the last row.
- Static cursors: These cursors create a static copy of the result set in tempdb, which can be useful when you need to perform multiple operations on the same data.
- Dynamic cursors: These cursors update their result set as changes occur to the underlying data.
- Keyset-driven cursors: These cursors are similar to dynamic cursors, but they maintain a keyset that defines the rows in the result set, which can improve performance.
Using a cursor involves several steps, including declaring the cursor, opening it, fetching rows one at a time, and then closing the cursor when you're finished. Cursors can be a powerful tool in SQL Server, but they should be used with care, as they can have a negative impact on performance if used improperly.
What is the lifecycle of a cursor?
Cursors are database objects, created and executed on the database server itself. A cursor's lifecycle involves the following steps.
- Declare a cursor: A cursor is declared by defining a SQL statement.
- Open a cursor: A cursor is opened for storing data retrieved from the result set.
- Fetch a cursor: When a cursor is opened, rows can be fetched from the cursor one by one or in a block to manipulate data.
- Close a cursor: The cursor should be closed explicitly after data manipulation.
- Deallocate a cursor: CA cursor should be deallocated to delete the cursor definition and release all the system resources associated with the cursor.
SQL Server Cursor Syntax and Example
In SQL, a cursor is declared using the DECLARE statement followed by the cursor name, CURSOR, its scope, and type of cursor followed by a SELECT statement. The SELECT statement selects rowset from the database. Here is the complete syntax of declaring a cursor.
SQL Cursor Syntax
DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ] FOR select_statement
[ FOR UPDATE [ OF column_name [ ,...n ] ] ] [;]
Cursor SQL Example
The following cursor is defined for retrieving employee_id and employee_name from the Employee table. The FETCH_STATUS value is 0 until there are rows. When all rows are fetched, then FETCH_STATUS becomes 1.
use Product_Database
SET NOCOUNT ON;
DECLARE @emp_id int ,@emp_name varchar(20),
@message varchar(max);
PRINT '-------- EMPLOYEE DETAILS --------';
DECLARE emp_cursor CURSOR FOR
SELECT emp_id,emp_name
FROM Employee
order by emp_id;
OPEN emp_cursor
FETCH NEXT FROM emp_cursor
INTO @emp_id,@emp_name
print 'Employee_ID Employee_Name'
WHILE @@FETCH_STATUS = 0
BEGIN
print ' ' + CAST(@emp_id as varchar(10)) +' '+
cast(@emp_name as varchar(20))
FETCH NEXT FROM emp_cursor
INTO @emp_id,@emp_name
END
CLOSE emp_cursor;
DEALLOCATE emp_cursor;
The output of the above program will be as follows
![SQL Server]()
Declare a cursor
The DECLARE SQL statement, followed by the cursor name and CURSOR FOR, declares a new cursor. The SELECT statement is used to select a dataset from the database. The following SQL statement declares a cursor named emp_cursor that selects emp_id and emp_name columns from the Employee table order by emp_id.
DECLARE emp_cursor CURSOR FOR
SELECT emp_id,emp_name
FROM Employee
order by emp_id;
Open a cursor
Once a cursor is declared, it must be opened before you can use it. The OPEN SQL statement is used to open a cursor. The following statement opens a cursor named emp_cursor.
OPEN emp_cursor
Fetch data from a cursor
The FETCH NEXT FROM cursorname INTO statement is used to fetch data from a cursor into variables. Once the data is stored into variables, you can print or use these variables.
The following statement reads from emp_cursor and stores values into @emp_id and @emp_name variables. After that, the value of the variable is printed.
FETCH NEXT FROM emp_cursor
INTO @emp_id, @emp_name
print 'Employee_ID Employee_Name'
Loop through all rows in a cursor
We use the WHILE loop to read all rows in a cursor. The WHILE loop starts with a BEGIN and ends with an END. The FETCH_STATUS is used to check if any rows are left in the row set. If the FETCH_STATUS is 0, there are no more rows left to read.
The following code reads all rows using a cursor in SQL Server.
WHILE @@FETCH_STATUS = 0
BEGIN
print ' ' + CAST(@emp_id as varchar(10)) +' '+
cast(@emp_name as varchar(20))
FETCH NEXT FROM emp_cursor
INTO @emp_id,@emp_name
END
Close a cursor
The CLOSE statement followed by the cursor name closes an opened cursor. The following SQL statement closes the cursor named emp_cursor.
CLOSE emp_cursor;
Deallocate a cursor
A cursor must be released from memory once it is done its work. The DEALLOCATE statement followed by the cursor name deallocates a cursor in SQL.
The following statement deallocates the emp_cursor cursor.
DEALLOCATE emp_cursor;
Why use a SQL Cursor?
In relational databases, operations are made on a set of rows. For example, a SELECT statement returns a set of rows called a result set. Sometimes the application logic needs to work with one row at a time rather than the entire result set at once. This can be done using cursors.
What are the limitations of a SQL Cursor?
A cursor is a memory resident set of pointers -- meaning it occupies memory from your system that may be available for other processes.
Cursors can be faster than a while loop, but they do have more overhead.
Another factor affecting cursor speed is the number of rows and columns brought into the cursor. Time how long it takes to open your cursor and fetch statements.
Too many columns being dragged around in memory, which is never referenced in the subsequent cursor operations, can slow things down.
The cursors are slower because they update tables row by row.
How can we replace SQL Cursors?
There's one replacement for cursors in SQL server joins.
Suppose we have to retrieve data from two tables simultaneously by comparing primary keys and foreign keys. In these types of problems, the cursor gives a very poor performance as it processes through each and every column. On the other hand, using joins in those conditions is feasible because it processes only those columns which meet the condition. So here, joins are faster than cursors.
Suppose, we have two tables, ProductTable, and Brand Table. The primary key of BrandTable is brand_id which is stored in ProductTable as a foreign key brand_id. Now suppose, I have to retrieve brand_name from BrandTable using foreign key brand_id from ProductTable. In these situations, cursor programs will be as follows,
use Product_Database
SET NOCOUNT ON;
DECLARE @brand_id int
DECLARE @brand_name varchar(20)
PRINT '--------Brand Details --------';
DECLARE brand_cursor CURSOR FOR
SELECT distinct(brand_id)
FROM ProductTable;
OPEN brand_cursor
FETCH NEXT FROM brand_cursor
INTO @brand_id
WHILE @@FETCH_STATUS = 0
BEGIN
select brand_id,brand_name from BrandTable where brand_id=@brand_id
--(@brand_id is of ProductTable)
FETCH NEXT FROM brand_cursor
INTO @brand_id
END
CLOSE brand_cursor;
DEALLOCATE brand_cursor;
The output of the above program will be as follows
![SQL Server]()
The same program can be done using joins as follows,
Select distinct b.brand_id,b.brand_name from BrandTable b inner join
ProductTable p on b.brand_id=p.brand_id
The output of the above program will be as follows
![SQL Server]()
As we can see from the above example, using joins reduces the lines of code and gives a faster performance in case huge records need to be processed.
Conclusion
This article taught us the basics of cursors in SQL Server and how to work with cursors to fetch and manipulate data in a SQL Server database.