Fetch-Cursor in SQL Server

A SQL cursor is used when the data needs to be committed row by row. Regardless of their bad performance, we still using cursors for database development, and reporting.

Basic fetch-cursor steps:

1-Declare cursor.

2-What do you use cursor for? Write the select statement that you will do row-by-row operation.

3-Open cursor.

4-Write fetch next statement. Assign the specific values from the cursor to the variables.

5-While loop. Condition to begin and continue data processing.

6-You should use fetch next statement in the while loop.

7-Close cursor then destroy cursor with deallocate statement.

Sample-1

use AdventureWorks2017
go
DECLARE Location_Cursor CURSOR FOR
select LocationID,Name,CostRate 
from Production.Location
OPEN Location_Cursor;
FETCH NEXT FROM Location_Cursor;
WHILE @@FETCH_STATUS = 0
 BEGIN
 FETCH NEXT FROM Location_Cursor;
 END;
CLOSE Location_Cursor;
DEALLOCATE Location_Cursor;
GO

Sample-2

use master
declare @database_name nvarchar(100);
declare @lgname  nvarchar(100);
declare crs cursor for
select original_login_name, DB_NAME(database_id) as db_nm
from sys.dm_exec_sessions where is_user_process=1;
open crs
fetch next from crs into @lgname, @database_name
while @@FETCH_STATUS=0
begin
print 'Login:'+cast(@lgname as nvarchar(100))+' Database Name:'+@database_name
fetch next from crs into @lgname, @database_name
end
close crs
deallocate crs;

We generally use these given default options of cursor samples (sample-1, sample-2). It sometimes may be expensive. However, are there any other options? Yes, there are. And there are lots of methods are explained in this site: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/declare-cursor-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15

You can use this script to compare several fetch-cursor options. Let you uncomment options and see the duration.

drop table if exists tempdb.dbo.#tmpCursor
create table #tmpCursor(
id int identity(1,1),
obid int 
)
go
DECLARE @StartTime datetime,@EndTime datetime 
SELECT @StartTime=GETDATE()
DECLARE @i INT = 1;
 
DECLARE cur CURSOR
 --LOCAL
 --LOCAL STATIC
 --LOCAL FAST_FORWARD
 --LOCAL FORWARD_ONLY
FOR
 SELECT c.[object_id] 
 FROM sys.objects AS c
 CROSS JOIN (SELECT TOP 200 name FROM sys.objects) AS c2
 ORDER BY c.[object_id];
 
OPEN cur;
FETCH NEXT FROM cur INTO @i;
 
WHILE (@@FETCH_STATUS = 0)
BEGIN
 insert into #tmpCursor
 values(@i)
 FETCH NEXT FROM cur into @i
END
 
CLOSE cur;
DEALLOCATE cur;
SELECT @EndTime=GETDATE() 
SELECT DATEDIFF(ms,@StartTime,@EndTime) AS [Duration in microseconds]

If you choose FAST_FORWARD you see it is faster than others.But, according to its good performance. I don’t advise fetch-cursor operations. Because Sql server is more proper for set operations. Cursors operate row by row therefore they may be slow. They use more memory and sometimes cause blocking.