A not very serious question but asking the experts.

[DickyP]

In my early days of using SQL on an IBM mainframe using PL/I in the 1980s an important element of its usage was SQL cursors. Recently looking at SQL Server whilst comparing different SQL flavours I noticed that SQL Server has cursor in its vocabulary.

Does anyone actually use cursors in SQL Server or is it just a leftover from spec compliance?

 

[theDBguy]

I haven't had to use them, but I thought they're equivalent to recordsets in Access, so I suppose they could be useful too.

SQL Server Cursor Explained By Examples

In this tutorial, you will learn how to use the SQL Server cursor to process a result set, one row at a time.

www.sqlservertutorial.net

 

[Minty]

I try and avoid them as I can't see any obvious benefit to using them. As I came from Access to SQL I was used to using a simple For Next loop with a record counter if required.
In SQL Server I generally use temp tables and windowing functions(row numbers and the like) in complicated stored procedures, as I can then normally use set based operations or the row number if I really do need to step through a record set.

 

[plog]

My experience is they are weapons used by software programmers who aren't good with SQL but have to fight with a database anyhow. Cursors are a concept the programmers can relate to and use cursors instead of proper JOINS and subqueries. I'm sure cursors have their place somewhere, but have only seen them used improperly.

 

[GPGeorge]

I've only used a cursor once or twice, maybe three times.

They are reputed to be very slow compared to other methods, if one is available.

 

[Isaac]

In my early days of using SQL on an IBM mainframe using PL/I in the 1980s an important element of its usage was SQL cursors. Recently looking at SQL Server whilst comparing different SQL flavours I noticed that SQL Server has cursor in its vocabulary.

Does anyone actually use cursors in SQL Server or is it just a leftover from spec compliance?

It's highly frowned upon [edited: for standard DML needs] as theoretically, you can do anything you need to with set-based processing, but it's handy as a last resort, I use them once in a while - which is far more than I ought, and I'm not proud of it.

 

[adhoustonj]

One good use is if you have a lot of SQL database changes to make. Rather than hardcoding table names in a script, you can have the names of the objects in a table, and then iterate through to perform whatever truncate, copy, call sp's, etc. for each object name value in the table.

 

[DickyP]

I couldn't see why anyone would as their were little more than what a recordset does- you just had to do the navigation yourself.

When I used them they were the only way to iterate the result of an SQL selection, hence my original query.

 

[Isaac]

@Pat Hartman I edited my post some time ago to say 'for standard dml operations'. plog is right, cursors are mostly used for when someone can't figure out how to do it set-based and that's me sometimes, I admit - but it's not a good thing.

your example is right, but we're talking about two different things, I am referring to using a cursor for some standard data retrieval situation. obviously you need a program (not necessarily sql) to loop through things for other reasons, yes.

If you want to learn more about why avoiding them is USUALLY the best course, read this discussion

Cursors - need to help to explain why to not use them – SQLServerCentral Forums

Cursors - need to help to explain why to not use them Forum – Learn more on SQLServerCentral

www.sqlservercentral.com

....which brings up most of the relevant points, I won't try to repeat them all here. mostly the emphasis is on they're usually not needed, there will still be the occasional time when they are needed but the problem comes when people new to sql generally are the ones trying to use them for things that set-based processing could achieve. in other words, what plog george and minty said

 

[Isaac]

yep. cursors are appealing to someone coming from vba to t-sql, but not for the right reasons