SQL Server Cursors usage and performance - cursed

If you have a requirement to poll through 'n' number of rows then immediately you would think about Cursors in SQL Server. As the solution using any programming language that loops recordsets to build the attendance list that works ok, but really say if you have hundreds of thousands rows to fetch then Cursors are not good option.

I have seen in the forums saying cursors are not good to use, to some extent I would say 'yes' they are good to go. Over the time now in SQL 2005 TSQL cursor operations, such as OPEN or FETCH, are ordinarily batched. With this the optimizer will have no need for the asynchronous generation of TSQL cursors. Whereas when a dynamic cursor is declared over a table with no unique indexes, and a row is deleted outside the cursor, a subsequent refresh of the cursor retrieves a placeholder for the row with NULL data. In earlier versions of SQL Server, the cursor does not return the affected row.

Talking about SQL Server 2005 behavior in better concurrency is useful, but often it opens up gates for a chance of deadlocks for concurrent cursors that are pumped to the database. In this case using locking hints for UPDATE statement with  UPDLOCK hint is useful, I would suggest to look into  Table Hint (Transact-SQL) under BOL. 

Also until SQL 2000 version it will support single-active process per connection,also with this behaviour the client application might need to read through the results and submit other queries to SQL Server based on the row just read from the result set. So the usage of server-side cursor is better in this case. Applications that open cursors and fetch one row (or a small number of rows) at a time can easily become bottlenecked by the network latency, especially on a wide area network (WAN).

Again using a DMV you can determine the number of connections with the API cursors using a fetch buffer size per row.

select  cur.*  from 
    sys.dm_exec_connections con
    cross apply sys.dm_exec_cursors(con.session_id) as cur
where
    cur.fetch_buffer_size = 1      and cur.properties LIKE 'API%'    

Relevant 917905Hotfix for the slower performance in using API cursors. 

Also few references on how better you can use them alongwith significant changes about cursors in SQL 2005:

SignificantChanges to Cursors.

Cursor-embeddedSQL talk.

Sure we will be bemused to see the changes in SQL 2008 which is in CTP at the moment.