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

 

[Pat Hartman]

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

I haven't used one since I stopped using COBOL but that's only because Access allows me to use linked tables and open a recordset that way. So I would use a query to minimize the rows selected and process them using a DAO loop if I couldn't do what I needed to do entirely in the query. If I had a long running batch process I would probably do it with a sproc and use a server side cursor for efficiency.

 

[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.

 

[Pat Hartman]

I'm sure cursors have their place somewhere

They do, in large scale batch processes such as printing your utilities invoices.

 

[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.

 

[Pat Hartman]

As I mentioned earlier, Access gives us recordset objects which can be bound to tables or queries for any source database. Therefore, it is extremely unlikely that I would ever actually create a Cursor and try to use it with Access (not even sure I can).

However, most applications have some type of sequential processing that a cursor would be used to handle in a different environment. For example, you want to send an email to a group of people. You can't do that with a query. You create the query to select the people but that is as far as the query can take you. You then open a recordset. The VBA reads each record and sends out the appropriate email. That is the kind of processing a cursor in a different language is used for so, I technically use cursors all the time. It is just that Access is creating them for me.

Technically, you could create a function to perform the email but that code usually has other requirements like counting the emails or determining that the client doesn't have an email address and so it prints a report instead and probably updates a different table. It also collects counts, etc. You can handle a lot of this if you use static variables and instantiate your objects before you start the query so you don't have to do it in the function. I work as a consultant so I almost never end up having to do maintenance on the applications I develop so unless some process is very slow, I tend to use the most simple and obvious solution. Most Access developers don't even know what a static variable is. You'd have to create the exact same procedure using a function vs a VBA loop through the recordset to see if the function solution is faster. I doubt there would be much difference so I would use the simple solution.

@Isaac I don't understand why you would look with disdain on any tool in your toolbox. Every tool has a use. Learn how to use each tool and use the correct tool for the task.

 

[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

 

[Pat Hartman]

Apparently I learned from the beginning when to use action queries and when to use "cursors" since I've never had the problem. I guess my point was, you both panned the concept of using a cursor but didn't qualify - when you should be using an action query to do the update. There are lots of serial processes for which the use of a cursor is necessary, not optional. Clarity is important. People read these threads and they see "cursors are bad. don't use cursors" but they don't understand that there is a class of functionality where cursors are the preferred option.

The reason I learned this distinction early on was because I was working with huge (millions) recordsets. If you are working with a small number of rows (50,000 or less), you can get away with poor practices. Access even teaches and promotes poor practices and that is why you can almost never to a flat -change nothing- conversion from Jet/ACE to SQL Server. That's what local filtering and incorrect use of domain functions do. They are easy to implement and so novices use these techniques but they are very inefficient and so it is only after the app has grown to a large volume of data that the slowness gets worse and worse.

 

[Isaac]

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