March 9, 2008 at 10:01 pm
hi,
I didn't really understand the cursor in SQL server.
how is cursor used in SQL Server?
once we declare cursor,processing,deallocate cursor.
after how can data be fetched to Select statement to display in stored procs.
can anyone give me some tips about it
advanced thanks
ts
March 9, 2008 at 10:46 pm
By far the most important tip on Cursors is: Avoid them as though they were leprosy.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 9, 2008 at 10:54 pm
Although it is true that you should try your best to avoid the cursor, a particular feature in any language exists because it has some usage.
See the DECLARE CURSOR Transact-SQL help in SQL Server Books Online and you will find and detailed example right at the bottom of that article.
Hope it helps.
Nitin.
March 10, 2008 at 5:45 am
Ok... once you learn how to use a Cursor, THEN avoid them as if they were Leprosy 😉 There is very very little you actually need a cursor for in the line of production code and then it needs to be a "firehose" cursor.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 10, 2008 at 10:50 am
Jeff Moden (3/10/2008)
Ok... once you learn how to use a Cursor, THEN avoid them as if they were Leprosy 😉 There is very very little you actually need a cursor for in the line of production code and then it needs to be a "firehose" cursor.
..in which case you might be better off doing that in a procedural language... Yes I said it.
But first - work at the problem as if Cursors don't exist. There's a lot of power included in set-based processing, and it usually just takes a little "letting go" to get it to work. You don't need to tell SQL server "how" to do something - just tell it "what" to do, and "what" rows you need that done to, and voila - mostly set-based by then. All that extra control is stuff for procedural code - NOT set-based.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 10, 2008 at 2:50 pm
Hi,
Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time. For example, you can use cursor to include a list of all user databases and make multiple operations against each database by passing each database name as a variable.
Check out the below to get an idea on How the cursor works.
http://www.databasejournal.com/features/mssql/article.php/1439731
But in real lilfe try to avoid using cursor.
Check out the below to get an idea why you should avoid using cursors
http://www.sql-server-performance.com/tips/cursors_p1.aspx
Please let me know if you have any questions.
Thanks -- Vj
March 10, 2008 at 3:18 pm
Not understanding cursors is a benefit to you! Matt has some great advice. Work the problem as if cursors do not exist. If you are going to spend your time trying to understand how cursors work, spend your time instead understanding Set based processing.
March 10, 2008 at 3:26 pm
Cursors may be evil, but they may be a necessary evil. Where possible use set based solutions, but sometimes you may find cursors are necessary, and therefore knowing how to write them and use them can be helpful.
Corrallary (spelling ?), knowing how to write them, can also help you understand existing cursors and how to rewrite them as a set based solution.
March 10, 2008 at 4:43 pm
Problem is... most people give up on trying to find a good set-based solution way too early...
--Jeff Moden
Change is inevitable... Change for the better is not.
March 10, 2008 at 5:55 pm
Lynn Pettis (3/10/2008)
Cursors may be evil, but they may be a necessary evil...
In my experience they are necessary and acceptable for only one thing: variable-izing SQL objects in the repeated execution of dynamic SQL or system procedures. In other words: DBA operational utility procedures (and these are not normally written by the uninitiated, nor should they be). And even in this case, there are very often acceptable ways around it.
Although I have heard many other allusions to possible instances where cursors were necessary, not one has materialized and I have begun to regard it as an urban legend.
I would like to keep an open mind on this but until someone can demonstrate a straight-forward example of cursors being necessary (except for the case that I cited above) or even just better than other options, I cannot see the point any longer.
For now, I regard them as an unnecessary evil.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 10, 2008 at 5:58 pm
Jeff Moden (3/10/2008)
Problem is... most people give up on trying to find a good set-based solution way too early...
Amen to that, Brother.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 10, 2008 at 6:44 pm
rbarryyoung (3/10/2008)
Lynn Pettis (3/10/2008)
Cursors may be evil, but they may be a necessary evil...In my experience they are necessary and acceptable for only one thing: variable-izing SQL objects in the repeated execution of dynamic SQL or system procedures. In other words: DBA operational utility procedures (and these are not normally written by the uninitiated, nor should they be). And even in this case, there are very often acceptable ways around it.
Although I have heard many other allusions to possible instances where cursors were necessary, not one has materialized and I have begun to regard it as an urban legend.
I would like to keep an open mind on this but until someone can demonstrate a straight-forward example of cursors being necessary (except for the case that I cited above) or even just better than other options, I cannot see the point any longer.
For now, I regard them as an unnecessary evil.
Continuing that line of thought... With the advent of SQL Server 2005, the system views, and things like VARCHAR(MAX), there's hardly a need for cursors even then. Some good non-looping dynamic SQL will do the trick. You can even materialize the dynamic SQL into a temporary sproc if you really want to. And, with thinks like what are found in the following article, even things that require "procedural" code don't ever need to resort to a cursor.
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
Throw CTE's and ROW_NUMBER() into the picture and the idea of using a cursor just means you got lazy or you really shouldn't be writting SQL (not anyone personnally here, folks). 😉
No, CLR's are not necessarily an acceptable substitute, ethier... except for certain things like RegEx functionality, it's just another way of someone who doesn't really know T-SQL to make themselves useful to the database. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
March 10, 2008 at 6:55 pm
Jeff Moden (3/10/2008)
Throw CTE's and ROW_NUMBER() into the picture and the idea of using a cursor just means you got lazy or you really shouldn't be writting SQL (not anyone personnally here, folks). 😉
No, CLR's are not necessarily an acceptable substitute, ethier... except for certain things like RegEx functionality, it's just another way of someone who doesn't really know T-SQL to make themselves useful to the database. 😀
true - mostly. CTE's can be cursors in disguise as well - so just because you change the name doesn't change the behavior.
And - in those few cases left where you might need to actually have a "firehose cursor" - CLR actually might give that cursor a run for its money (since CLR loops do NOT necessarily require RBAR inserts). And yes- there really isn't very much left when all is said and done that WOULD require a cursor (once you've exhausted the "tricks").
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 10, 2008 at 7:00 pm
Jeff Moden (3/10/2008)
Continuing that line of thought... With the advent of SQL Server 2005, the system views, and things like VARCHAR(MAX), there's hardly a need for cursors even then. Some good non-looping dynamic SQL will do the trick. You can even materialize the dynamic SQL into a temporary sproc if you really want to. And, with thinks like what are found in the following article, even things that require "procedural" code don't ever need to resort to a cursor.http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
Throw CTE's and ROW_NUMBER() into the picture and the idea of using a cursor just means you got lazy or you really shouldn't be writting SQL (not anyone personnally here, folks). 😉
No, CLR's are not necessarily an acceptable substitute, ethier... except for certain things like RegEx functionality, it's just another way of someone who doesn't really know T-SQL to make themselves useful to the database. 😀
Actually, what I do use them for is an extreme form of Operational Utility procedures. Specifically, to Code-Generate the scripts for entire Data Warehouse environments and their ETL environments.
I know that theoretically, I could do this without Cursors via Varchar(max) template strings and some super-duper-hyper joins, however, my existing code is still pretty readable, maintainable and reasonably debuggable. I'm not sure that I could swing all that using mega-strings and hyper-joins.
Plus the downsides of Cursors don't mean much in this case (isolated development /generation environments) so I have not bothered to recode them.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 10, 2008 at 7:50 pm
And of course many of us inherit code that may contain cursors. Unless you understand cursors you may not be able to translate it into a nicer set-based syntax.
I think the reliance on cursors comes about because people think too much in an OO way. They try to break the code down into small chunks, usually in stored procs, and then don't know how to call that code for their rows unless they do it RBAR in a cursor. Some cunning ones will end up using functions but still not really fix the underlying problem...
Hey, that's why our software runs reasonably well on modest hardware compared to some of the competition - customers like that 🙂 If the competition keep using cursors and other ugly constructs then more power to me! 😀
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply