SQL with Manoj

CURSORS or WHILE loops with temp-tables & counter, what do you prefer, personally and perofrmance wise?

This has been discussed in lots of forums, threads, posts and blogs previously. Many people and experts claim to use the either one and most of them are inclined to WHILE loops, and suggest to avoid CURSORS without any proof and logic.
Links: http://blog.sqlauthority.com/2008/05/21/sql-server-2005-twelve-tips-for-optimizing-sql-server-2005-query-performance/
http://www.c-sharpcorner.com/UploadFile/skumaar_mca/CursorsAlternative09012009011823AM/CursorsAlternative.aspx
http://www.sqlbook.com/SQL/Avoiding-using-SQL-Cursors-20.aspx
… and many more.

To make this more clear I tested this scenario myself and also posted this case in MSDN TSQL forum.

use [tempdb]
GO

create table T1 (sn int identity(1,1) primary key, data varchar(1000))
GO

insert into T1 (data)
values (replicate('a',1000))
GO 10000

select * from T1

create table T2 (sn int primary key, data varchar(1000))
create table T3 (sn int primary key, data varchar(1000))

-- Test the CURSOR, pull record from T1 and insert into T2
set nocount on

declare @stDate datetime
set @stDate = getdate()
declare @sn int , @data varchar(1000)

declare cr cursor FORWARD_ONLY FAST_FORWARD READ_ONLY
for select sn, data from T1

open cr
fetch next from cr into @sn, @data
while @@fetch_status=0
begin
	insert into T2
	select @sn, @data
	fetch next from cr into @sn, @data
end
close cr
deallocate cr

select Datediff(ms,@stDate,getdate()) -- Ran 4 times, it gives me 966, 413, 310, 306 ms
GO

-- Test the WHILE loop with counter (NO CURSOR), pull record from T1 and insert into T3
set nocount on

declare @stDate datetime
set @stDate = getdate()
declare @ctr int
set @ctr=0

while @ctr<=10000
begin
	insert into T3
	select sn, data
	from T1
	where sn = @ctr

	set @ctr = @ctr + 1
end

select Datediff(ms,@stDate,getdate()) -- Ran 4 times, it gives me: 1070, 450, 503, 423 ms
GO

--Final Cleanup
drop table T2
drop table T3
drop table T1

I ran the above code for CURSOR & WHILE loop 4 times and it gave me less execution time for CURSOR, everytime.

While using CURSORS the main thing most people miss out are the options that are available to optimize CURSOR use.
According to MS BOL they are:

DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]
     [ FORWARD_ONLY | SCROLL ]
     [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
     [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]

To know more check this link: http://msdn.microsoft.com/en-us/library/ms180169.aspx

This prove that CURSORs are more performant than WHILE loops. I’m open for your comments, thanks.

MSDN links:
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/e67105a6-0f4a-4a12-85b9-e7e9855279e7/
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/2978d387-fcd0-45bb-bf69-80139b6dac53
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/ba15132e-c26d-4472-92e9-845ce2cc244d/