Alternative method to using Cursors to view records in SQL Server
Cursors are a widely used feature in Sql Server and other databases such as Oracle to process a record set of data. While they might seem efficient, they're not, and when you do, you should avoid using them or an Alternative Method. A common replacement method is to use a temporary table and then move through the record set. Below is our example Cursor code to print the names of over 127,000 records that I have in a database table.
declare CRS cursor for select ID, Name from Stars order by Name
declare @ID bigint, @Name varchar(200)
declare @Start datetime = getdate()
Open CRS
fetch next from CRS into @ID, @Name
while @@FETCH_STATUS = 0
Begin
Print @Name
fetch next from CRS into @ID, @Name
End
close CRS
Deallocate CRS
Select datediff(ms, @start, getdate())
The total time it took to process and display the records to the screen was 4820 milliseconds or nearly 5 seconds. Four seconds might seem quick, but using temporary tables is even more efficient. The equivalent to the above is shown below:-
Create table #tmp(tmpId bigint identity(1,1), Id bigint, Name varchar(200))
Create index idxtmp on #tmp(tmpId)
declare @Start datetime = getdate()
declare @ID bigint, @Name varchar(200)
insert into #tmp
Select Id, Name from stars order by name
Declare @Top int = (Select top 1 tmpId from #tmp order by Id desc)
declare @ptr int = 1
while @ptr<@Top
Begin
select @ID = ID, @Name = Name from #tmp where tmpId = @ptr
Print @Name
set @Ptr = @Ptr + 1
End
drop table #tmp
Select datediff(ms, @start, getdate())
The alternative method seems a bit wordier but is more efficient. When I ran it, the total time was 3582 milliseconds which is 3.5 seconds. When speed is crucial, you can see the alternate method is the best solution.
Last Modified : June 2023