[ACCEPTED]-MS SQL Server - When is a CURSOR good?-cursor
Normally they are to be avoided, but the 37 feature is there for a reason and there 36 are times to use them. I'd say 90+% of the 35 cursors I've seen are not needed. If you 34 are using them for CRUD operations, that 33 can almost always be redone in a set-based 32 fashion. I've often seen people use cursors 31 for this because they don't know how to 30 use joins in an update or delete or that 29 they can use a select statment instead of 28 a values clause in an insert. Another unnecessary 27 use when people think they need them for 26 slightly more complex processing that actually 25 could easily be handled with a case statement.
Cursors 24 are sometimes faster for calculating something 23 like a running total.
Cursors are also handy 22 for multiple executions of a stored proc 21 that is set up to handle only one input 20 value at a time. I do not use this feature 19 for running user stored procs (unless I 18 know I will be hitting a very small set 17 of data) but it is very handy for database 16 admins when needing to run system procs 15 against multiple tables.
If you are creating 14 emails in SQl (not the best place to do 13 it, but in some systems that's where they 12 do it) and do not want the entire audience 11 of the email to see the other people on 10 the list or you want to personalize each 9 email with information about the addressee, cursors 8 are the way to go.
Cursors or loops can be 7 used also to process batches of records 6 if the entire set-based insert/update/delete 5 will take too long and lock up the tables. This 4 is a sort of a hybrid between the cursors 3 and the set-based solution and is often 2 the best one for large changes on production 1 systems.
I asked a guy on the SQL Server team one 19 time, if you could add one feature that 18 would make the product better for everyone 17 what would it be?
His response was 'Add? Huh, I 16 would take one away. If you get rid of cursors 15 you force programmers all over the world 14 to start thinking about things in a SET 13 based way and that will be the biggest world 12 wide increase in DB performance you will 11 ever see.'
For my part however I tend to 10 see a pattern, there seems to be a lot of 9 procedural coders who use cursors because 8 they need to be able to do an operation 7 one element at a time and miss the old fashion 6 WHILE loop concept. Same basic idea without 5 the cursor overhead. Still not near as fast/effective 4 as something SET based but 90% of the time 3 when someone claims 'I cant do this set 2 based, I have to use cursors' I can get 1 them to do it with a while loop.
Here's an article by a rather opinionated 3 fellow, who gives reasoning for not using 2 Cursors and some answers as to how they 1 came to be: There Must be 15 Ways to Lose Your Cursors.
The MCTS prep manual for SQL Server 2008 9 that I'm studying recommends using external 8 CLR code anywhere that a CURSOR would be 7 required in T-SQL, especially now that SQL 6 Server 2008 supports custom aggregate functions.
5 5 years ago, I worked with them for extensive 4 reporting features, but I don't think I 3 could come up with a good use case for them 2 now. CLR aggregates and functions perform 1 similarly to built-in aggregate functions.
Only time I'll use them is when whatever 7 is been done inside the cursor absolutely 6 has to be done one item at a time and where 5 whatever is been done inside the cursor 4 takes so long that the overhead of the cursor 3 fades into insignificance.
Eg database backups, integrity 2 checks, index rebuilds. In short, admin 1 tasks.
OMG, how did I forget about Group By? I 5 took the cursor based query you see below 4 and replaced it with the one after it. Now 3 I get a single result set so there are no 2 issues with using sqlsrv_next_result() in 1 php.
DECLARE @thisday datetime; DECLARE daycursor CURSOR FOR SELECT DISTINCT DATEADD(day, 0, DATEDIFF(day, 0, TimeCollected)) as thisday FROM computerusedata OPEN daycursor; FETCH NEXT FROM daycursor INTO @thisday; WHILE @@FETCH_STATUS = 0 BEGIN select distinct left(ComputerName,5) as CompGroup,DATEADD(day, 0, DATEDIFF(day, 0, TimeCollected)) as day FROM computerusedata where DATEADD(day, 0, DATEDIFF(day, 0, TimeCollected)) = @thisday order by CompGroup; FETCH NEXT FROM daycursor; END; CLOSE daycursor; DEALLOCATE daycursor;"; select DATEADD(day, 0, DATEDIFF(day, 0, TimeCollected)) as day,left(ComputerName,5) as CompGroup from ComputerUseData group by DATEADD(day, 0, DATEDIFF(day, 0, TimeCollected)),left(ComputerName,5) order by day,CompGroup
I don't typically use cursors but when I 5 do, it must be a "one-off" query that I'm 4 running locally or a daily job. You want 3 to refrain from having production code call 2 a cursor that would be invoked frequently 1 like in response to a web request.
Cursors are useful when 1) you need to do 19 something that you cannot do with a set 18 operation, or 2) it doesn't make sense to 17 do the same work by making iterative calls 16 from the application layer. Or sometimes 15 you have a procedure that must remain on 14 the database layer, and you simply can't 13 break back out to the app layer midstream 12 to iterate over some result set.
One recommendation 11 I would make though, is that people use 10 cursor variables rather than normal cursors, because 9 you avoid the cursor allocation/deallocation 8 issues that surround normal cursors. With 7 a normal cursor, if you don't deallocate 6 them they persist, which can be a source 5 of memory leaks. Not so with variable-based 4 cursors (i.e. DECLARE @cursor CURSOR).
The 3 bottom line is, avoid them if you possibly 2 can, and if you can't, use them minimally 1 and wisely.
You use cursor for rebuilding or reorganizing 2 table indexes individually
unless there 1 is a way of running ALTER INDEX... as a set-based operation.
More Related questions