So when and why should you consider using cursors? Here I'll spotlight cursor advantages.
Cursors are best used when performing row-by-row operations that can't be accomplished with set-based operations (i.e., when you need to fire a stored procedure once per row in a table).
Here is an example of a cursor used in the Microsoft shipped routine sp_helppublication
DECLARE hC CURSOR LOCAL FAST_FORWARD FOR SELECT pubid, name FROM syspublications WHERE name like @publication OPEN hC FETCH hC INTO @pubid, @pubname WHILE (@@fetch_status <> -1) BEGIN IF is_member(N'db_owner') <> 1 BEGIN exec @retcode = sp_MSreplcheck_pull @publication = @pubname, @raise_fatal_error = 0, @given_login = @username END IF (is_member(N'db_owner') = 1) OR (@retcode = 0 AND @@error = 0) INSERT INTO #accessiblepubs values(@pubid) FETCH hC INTO @pubid, @pubname END CLOSE hC DEALLOCATE hC
This same procedure can be accomplished in a singly nested while loop, but cursors are a better option for performance. For more than one nested loop, you should use a while loop.
Here is an example with the above batch rewritten as a while loop.
DECLARE @count int DECLARE @pubid int DECLARE @publication varchar(10) DECLARE @pubname varchar(10) DECLARE @retcode int SET @publication='pubs' DECLARE @username sysname SET @username=suser_name() SELECT @count=count(pubid) FROM pubs.dbo.syspublications WHERE name like @publication WHILE (@count > 0 ) BEGIN IF is_member(N'db_owner') <> 1 BEGIN SELECT @pubid=pubid, @pubname=name FROM syspublications WHERE name like @publication exec @retcode = sp_MSreplcheck_pull @publication = @pubname, @raise_fatal_error = 0, given_login = @username END IF (is_member(N'db_owner') = 1) OR (@retcode = 0 AND @@error = 0) INSERT INTO #accessiblepubs values(@pubid) SELECT @count=@count-1 END
Quick and dirty
SQL developers are often under the gun to write code fast. Writing a cursor requires less mental effort than writing its set-based equivalent. Unfortunately these shortcuts often remain in production and cause problems further down the line. (Thanks for the above two observations from SQL MVPs Itzik Ben Gan and Erland Sommarskog.)
Cursors are faster than using while loops. Here is an example illustrating the timings.
USE PUBS GO CREATE TABLE NUMBERS (pk INT NOT NULL IDENTITY PRIMARY KEY , CHARCOL char(20)) DECLARE @intcol INT SET @intcol=1 WHILE @intcol<8001 BEGIN INSERT INTO NUMBERS (charcol) VALUES (@intcol) SELECT @intcol=@intcol+1 END DECLARE @PK VARCHAR(20) DECLARE @datetime DATETIME SET @datetime=GETDATE() DECLARE test CURSOR FOR SELECT charcol FROM NUMBERS OPEN test FETCH NEXT FROM test INTO @PK WHILE (@@FETCH_STATUS =0) BEGIN SELECT @PK FETCH NEXT FROM test INTO @PK END CLOSE TEST DEALLOCATE TEST SELECT DATEDIFF(ms, @datetime, GETDATE()) --3786 ms DECLARE @counter INT DECLARE @datetime DATETIME SET @datetime=GETDATE() SET @counter=1 WHILE (@counter< 8001) BEGIN SELECT charcol from numbers where pk=@counter SELECT @counter=@counter+1 END SELECT DATEDIFF(ms, @datetime, GETDATE()) --4676 ms – almost a full second longer
This is a trivial example, but it does illustrate the performance advantage of cursors over while loops. In our case of 8,000 rows there is nearly a one-second speed advantage in using a cursor over a while loop.
Classic ADO made use of cursors for scrolling or paging through a results set on the server, using server-side cursors. These cursors provided performance benefits over pure T-SQL implementations for paging through a results set. For more information on pure T-SQL implementations consult ASPFAQ.COM. In these tests the T-SQL provides marginally better performance, but cursors are faster for larger results sets. In SQL Server 2005 you can also use Common Table Expressions (CTEs) for server-side paging.
By default cursors will query the base tables with each fetch and, as such, they are always current with the most recently updated values. Here is an example:
CREATE TABLE test (pk INT NOT NULL IDENTITY PRIMARY KEY, charcol CHAR(30)) GO DECLARE @int INT SET @int=1 WHILE @int <6 BEGIN INSERT INTO test (charcol) VALUES ('this is a test '+convert(VARCHAR(2),@int)) SELECT @int=@int+1 END GO DECLARE @pk INT SET @pk=1 DECLARE @charcol varchar(30) DECLARE testcursor cursor for select pk, charcol from test OPEN testcursor FETCH testcursor INTO @pk, @charcol WHILE @@fetch_status=0 BEGIN SELECT @charcol UPDATE test SET charcol=convert(VARCHAR(2),pk)+' '+charcol SELECT charcol FROM test FETCH testcursor INTO @pk, @charcol END CLOSE testcursor DEALLOCATE testcursor
Notice how each cursor fetch updates the value of charcol and the cursor picks up the updates in the underlying base tables.
With advantages like these, you may wonder what are the disadvantages of using cursors. Please, oh please, oh please keep reading.
Migrating to SQL Server 2005
Part 1: How cursors work
Part 2: Cursor advantages
Part 3: Cursor disadvantages
Part 4: Cursor example
|ABOUT THE AUTHOR:|
Hilary Cotter has been involved in IT for more than 20 years as a Web and database consultant. Microsoft first awarded Cotter the Microsoft SQL Server MVP award in 2001. Cotter received his bachelor of applied science degree in mechanical engineering from the University of Toronto and studied economics at the University of Calgary and computer science at UC Berkeley. He is the author of a book on SQL Server transactional replication and is currently working on books on merge replication and Microsoft search technologies.
Copyright 2006 TechTarget
This was first published in March 2006