As a DBA we should always try to avoid row based processing and cursors as they are often seen in the dba world as a no-no. Cursors require a round trip every time you go through an iteration and they require a lot more overhead. Often times developers grab a set of records that they want to iterate through and then apply some logic to perform the actions one by one. Let’s create a simple example with a table of fruit. We want to select the top 2 fruit and then update the top 2 fruit to ‘bananas’
Using a cursor this is probably something like what the developer would have written:
create table test(Id int identity(1,1) Primary Key,fruit varchar(25))
Insert into Test(fruit)
values (’apples’), (’peaches’),(’pears’), (’plums’)
declare @fruit varchar(25)
declare @getfruit cursor
set @getfruit = cursor for
select top 2 fruit
from test
open @getfruit
fetch next
from @getfruit into @fruit
while @@fetch_status= 0
begin
Declare @sql nvarchar(max)
Set @sql = ‘Update test Set fruit = ”bananas” from test where fruit=”’ + @fruit +”’;’
exec sp_executesql @sql
fetch next
from @getfruit into @fruit
end close @getfruit
deallocate @getfruit
drop table test
While working with a colleague of mine, recently we found a cool trick to create sets with dynamic SQL and avoid cursors all together. Normally I do not reccommend using dynamic sql but this has been one of the few exceptions. Using the set based method we can simplify the process by creating the set and then just apply the logic to the set itself:
create table test(Id int identity(1,1) Primary Key,fruit varchar(25))
Insert into Test(fruit)
values (’apples’), (’peaches’),(’pears’), (’plums’)
declare @sql as nvarchar(max)
set @sql= ”
select top 2 @sql = @sql + ‘Update test Set fruit = ”bananas” from test where fruit=”’ + [fruit]+”’;’+ CHAR(13)
from test
exec sp_executesql @SQL
drop table test
Summary: As you can see it requires a lot less code to implement right off the bat. It is also a lot easier to understand as well. If you run the two together as a batch in SSMS with show execution plan on you will see that the set based operations will give you a much better outcome and perform the tasks a lot faster as well. In this example we got 40% gain!
