Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mgreen84
Yak Posting Veteran
94 Posts |
 Posted - 2014-03-26 : 14:21:59
|
| This is just a general question, I noticed someone posted a question about cursors. I've always been told cursors are like the little red headed step child no one wants lol. Atleast thats how DBA's see them. Well being that they can be a stress on the database's performance, what are some methods one might take to avoid using a cusor or use in place of a cursor? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-03-26 : 14:33:10
|
| It depends on what the cursor is doing. Please post an example and we'll show you the alternative.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
artistlover
Starting Member
4 Posts |
Posted - 2014-03-26 : 14:43:04
|
| I'm sorry i didn't mean i had a cursor. I'm lost as to how to do what i need. I was showing the code i have and explained what i needed it to do. I've never written a cursor.artistlover |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2014-03-26 : 15:45:37
|
| I'm not sure if you know WHY cursors shouldn't be used or not. You'll hear people say that you should be using a set-based approach, rather than an iterative one (i.e. Cursor); and that is generally true. However, the main reason, in my mind, to avoid cursors is that they are not memory safe. By that I mean you can take out a server if you implement a bad cursor (i.e. you can use up all the server memory).Ultimately, you should be using set-based queries. But, if you do need to do some for of iteration, a cursor might be the right answer. However, to implement "cursor-like" solution, you could also use a WHILE loop or a temp table-type solution that would be memory safe. It really depends on what you need to do.If you have any specific questions or scenarios, feel free to let us know and we can try to help answer or address them. |
|
|
|
mgreen84
Yak Posting Veteran
94 Posts |
Posted - 2014-03-26 : 15:54:08
|
| Thanks for replies guys, didn't have a specific example just was just out of general curiosity. |
|
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2014-03-26 : 18:42:50
|
| Yes, the main reason to avoid cursors is their huge overhead.Often tally tables can be used to avoid cursors. As can CROSS APPLY and table-valued functions (inline-table-valued functions perform much better, so use those when possible). |
|
|
|
|
|
|
|
|
What are the alternatives for using a CURSOR?