InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 20 Jun 2013 03:54:41 GMT20http://www.sqlservercentral.com/Forums/Topic696297-1226-1.aspx[quote][b]mschroeppel (7/4/2010)[/b][hr]I also found this article rather insulting. I almost never use cursors. In fact, I think I should use them more. I sometimes find myself dong mental jujitsu, trying to write set-based T-SQL, when the task lends itself more easily to procedural code. Of course they are slower, but sometimes performance is not as important as writing something that is simple and easily maintainable. Arguing that programmers are lazy and dumb is all the more reason to write something in a way that your (lazy, dumb) successors will understand.Obviously anyone reading this article is interested in avoiding cursors, so why start off with a self-indulgent rant? JMHO.[/quote]Set-based processing does stretch the old brain cells a bit, but as you point out everyone understands a loop.I just had a recent discussion with a group about the benefits of writing extremely simplistic code versus efficiency (and several other programming oddities, btw). I listed out several coding priorities and challenged them to put their coding priorities in the correct order for their business. If you try this exercise and "efficiency" is at the bottom of the list, then you obviously have to code to your priorities.Mike CSun, 04 Jul 2010 23:14:49 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic696297-1226-1.aspxI also found this article rather insulting. I almost never use cursors. In fact, I think I should use them more. I sometimes find myself dong mental jujitsu, trying to write set-based T-SQL, when the task lends itself more easily to procedural code. Of course they are slower, but sometimes performance is not as important as writing something that is simple and easily maintainable. Arguing that programmers are lazy and dumb is all the more reason to write something in a way that your (lazy, dumb) successors will understand.Obviously anyone reading this article is interested in avoiding cursors, so why start off with a self-indulgent rant? JMHO.Sun, 04 Jul 2010 21:45:15 GMTmschroeppelhttp://www.sqlservercentral.com/Forums/Topic696297-1226-1.aspxZZZZZZZZZZZZZZZZZZZZZZ ... mmha grmmah mrmma ... ZZZZZZZZZZZZZZZZZZZZFri, 02 Jul 2010 06:55:39 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic696297-1226-1.aspxI agree - don't awaken the beast. In the eyes of developers, cursors are like Marmite.Fri, 02 Jul 2010 01:52:25 GMTphil.wood 94423http://www.sqlservercentral.com/Forums/Topic696297-1226-1.aspxHmmm...this thread seems familiar...but let's not poke that sleeping tiger. :-)Tue, 29 Jun 2010 07:38:21 GMTGabyYYZhttp://www.sqlservercentral.com/Forums/Topic696297-1226-1.aspx[quote][b]jeffwilson2 (6/25/2010)[/b][hr]Hi my name is Jeff.I am new to this forum. About a year ago I started at a company and last year they upgraded to SQL 2005 and nearly every procedure uses a cursor, in some of the most bizarre circumstances.:sick: I have been trying to rewrite them as I have had to modifiy them, but some of them are so embedded in other processes or so long and confusing with cursors inside other cursors :sick: that I hesitate to do so.:unsure:But I have come across a scenario that i am at a loss on how to avoid a loop. And maybe this is an exception to the rule about using loops.Any help or advice would be appreciated.The purpose of the procedure is to back into the SDI tax rate when the total SDI Tax has exceeded it's limit, so the new net Tax value can be recalculated. It usally has to loop through the process 5-10 times to return.See everytime the SDI tax rate changes the Total net tax changes. But SDI tax i based on the Net Total Tax.So everytime you adjust the tax rate so that the SDI TAX is at the limit, then the NetTotal Tax changes and the SDI rate is not accurate, But every time you make the adjustment you get closer to the SDI tax.I realize that this is circular logic, but it does retrun the accurate data because the SDI rate keeps getting closer and closer to the correct rate needed to set the SDI tax at its limit.Here is a function that I use a loop for.[code="plain"]CREATE FUNCTION dbo.fn_TaxLimitCalcTable ( @startVal decimal(25,18), @LimitVal decimal(25,18), @YTDVal decimal(25,18), @NetBenefit decimal(25,18), @Fed decimal(25,18), @staterate decimal(25,18), @Soc decimal(25,18), @Med decimal(25,18), @CurRate decimal(25,18) )RETURNS @TaxLimit TABLE ( NewSDIRate decimal(25,18), adNetval decimal(25,18), adjustedval decimal(25,18), Fed decimal(25,18), Med decimal(25,18), staterate decimal(25,18), Soc decimal(25,18))AS/**************************************************** SP Name: fn_TaxLimitCalcTable Description: This function re-calculates the associated tax and rates when the maximum tax limit has been reached or exceeded. ****************************************************/ BEGIN DECLARE @w2amt1 decimal(25,18), @NewSDIRate decimal(25,18), @adjustedval decimal(25,18), @adNetval decimal(25,18),@Returned decimal(25,18), @Fed1 decimal(25,18),@Med1 decimal(25,18), @staterate1 decimal(25,18),@Soc1 decimal(25,18) SELECT @NewSDIRate = @CurRate, @adjustedval=@startVal, @adNetval=@NetBenefit*(1/(1-(@CurRate+@Fed+@staterate+@Soc+@Med))) --set if startvalue matches IF @YTDVal > 0 and @LimitVal > 0 BEGIN WHILE @adjustedval <> (@LimitVal-@YTDVal) BEGIN SELECT @NewSDIRate = (@LimitVal-@YTDVal)/@adNetval SELECT @adNetval = ROUND(@NetBenefit*(1/(1-(@NewSDIRate+@Fed+@staterate+@Soc+@Med))),2) SELECT @adjustedval = ROUND(@NewSDIRate * @adNetval,2) END SELECT @Fed1 = round(@Fed * @adNetval,2) SELECT @Med1 = ROUND(@Med * @adNetval,2) SELECT @staterate1 = ROUND(@staterate * @adNetval,2) SELECT @Soc1 = ROUND(@Soc * @adNetval,2) END -- Return the result of the function insert @TaxLimit select @NewSDIRate, @adNetval, @adjustedval, @Fed1, @Med1, @staterate1, @Soc1 RETURN ENDGO[/code]Thank you,Jeff[/quote]I'm not 100% sure about your calculation here. SDI tax is usually set at a certain % by the state with a limit on the amount of taxable income. For CA, for instance, I think the rate is 1.1% on (up to) the first ~$90,600 paid. So if the employee has paid their 1.1% SDI on the first ~$90,600 of pay then the rate is now 0.0% -- no need to back into it.But all that aside, can you provide 4 or 5 sample data inputs and expected outputs for this function?ThanksMike CSat, 26 Jun 2010 15:55:44 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic696297-1226-1.aspx[quote][b]jeffwilson2 (6/25/2010)[/b][hr]Hi my name is Jeff.I am new to this forum. About a year ago I started at a company and last year they upgraded to SQL 2005 and nearly every procedure uses a cursor, in some of the most bizarre circumstances.:sick: I have been trying to rewrite them as I have had to modifiy them, but some of them are so embedded in other processes or so long and confusing with cursors inside other cursors :sick: that I hesitate to do so.:unsure:But I have come across a scenario that i am at a loss on how to avoid a loop. And maybe this is an exception to the rule about using loops.Any help or advice would be appreciated.The purpose of the procedure is to back into the SDI tax rate when the total SDI Tax has exceeded it's limit, so the new net Tax value can be recalculated. It usally has to loop through the process 5-10 times to return.See everytime the SDI tax rate changes the Total net tax changes. But SDI tax i based on the Net Total Tax.So everytime you adjust the tax rate so that the SDI TAX is at the limit, then the NetTotal Tax changes and the SDI rate is not accurate, But every time you make the adjustment you get closer to the SDI tax.I realize that this is circular logic, but it does retrun the accurate data because the SDI rate keeps getting closer and closer to the correct rate needed to set the SDI tax at its limit.[/quote]It's a "memory-only" loop and isn't likely going to be a performance problem (except that such loops can't be used in an iTVF). What will be a performance problem is the fact that it's a mlTVF (multi line table valued function) which can be nearly as slothful as a scalar function. My question would be how many times do you use the function in a given proc (ie... against how many rows)?My other question would be... what happens if it "hits" the correct number on the 5th iteration but we make it run for 10 iterations? Will it still come in on the correct answer (not taking the time to analyze the algorithm just yet)?Sat, 26 Jun 2010 12:45:42 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic696297-1226-1.aspx[quote][b]Nick Walton (6/25/2010)[/b][hr][quote][b]Thomas-282729 (6/25/2010)[/b][hr]DBA: A. Why is it my problem that someone else did not do a better job of planning ahead? B. why are you pulling one record at a time instead of pulling a batch of records? C. Why not write a small app that batches and processes the data? [/quote]I agree, I've been there, so I can give you the responses.a. Because you're holding the project up. It would be on-schedule if you didn't just let the developers write the code. there's no Right and Wrong answer. Just write an SP to do it, and the project can get delivered.[/quote]"...there's no Right and Wrong answer." --> I disagree with the "everyone gets a trophy" mentality.I think a more complete assessment is that the "Right" SP that seems to run well today when your table has 1,000,000 rows is going to run terribly a year from now when it grows to 10,000,000 rows. It's going to push out your processing time by delaying other resource-intensive processes and cause you to miss your SLAs. The customer will complain and you'll go and try to optimize that cursor in the SP to shave off a couple of seconds.The more accurate (...and just as satisfying) answer might be that there's no need to do it "Right" the first time because we can fix it later when it causes problems. Besides, the ongoing project maintenance costs come from a different budget.[quote]b. We're not. We're batch processing the records, however each row requires handling in a different, and complex way depending on the data which 'hangs off' it. There are up to 300 different 'events' which can fire depending on the processing logic.[/quote]When you say 'events' which can fire depending on processing logic, SQL is not the development tool of choice that comes to mind. Rather I think of procedural languages that natively support user-defined event handling. It would be interesting to see the performance affects of a custom-built user-defined event-handling framework built completely in T-SQL.[quote]c. that would require development time which we don't have. It wasn't specified *whinge whinge*[/quote]More than that this type of project generally requires planning time which many companies aren't willing to invest in. I've read studies by IBM and others that peg the cost of fixing broken solutions at 100's to 1000's of times the cost of doing it "Right" the first time, even calculating in the cost of proper planning and design. But many managers don't care since the code fixes are often hidden away in a different budget after delivery.[quote]In defence of point c, The app option is a red herring anyway, cos it would be external to the DB, and would still have to pore through each row, do the very specific logic to each row, requiring the pulling-in of yet more data. The pull/process/push time would be rather extensive. Even with 1000 simultaneous connections (assuming the DB didn't fall over due to locking on the tables), it'd still take more than 10 times longer to complete that way, and would be yet another service to have to monitor, document, maintain, etc.[/quote]Some tasks, such as row-by-row processing, are often better done in other tools. SSIS and other ETL tools are prime examples of tools that are optimized to act on individual rows at a time.Sat, 26 Jun 2010 11:10:03 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic696297-1226-1.aspx[quote][b]ehaustin78737 (6/25/2010)[/b][hr]I agree with Brad Neufeld this article bites. Another condescending rant.[/quote]Everyone appreciates such specific criticism. It helps the author fine-tune his style, lists plenty of well-defined arguments that provoke thoughtful consideration, and rises far above the level of the "background noise" generated by (for instance) a YouTube "I Hate!" video.I thank you on behalf of the author.Mike C.Sat, 26 Jun 2010 10:46:20 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic696297-1226-1.aspx[quote][b]Nick Walton (6/25/2010)[/b][hr][quote][b]Thomas-282729 (6/25/2010)[/b][hr]DBA: A. Why is it my problem that someone else did not do a better job of planning ahead? B. why are you pulling one record at a time instead of pulling a batch of records? C. Why not write a small app that batches and processes the data? [/quote]I agree, I've been there, so I can give you the responses.a. Because you're holding the project up. It would be on-schedule if you didn't just let the developers write the code. there's no Right and Wrong answer. Just write an SP to do it, and the project can get delivered.[/quote]To this I would answer, that if the project's delivery rests entirely upon a last minute hacked solution, it was doomed from the start. If this isn't a last minute hack, then I would think that any solution should consider aspects such as speed, accuracy, reliability, extensibility and maintainability. Given that, doing this in a small app or middle-tier component rather than row-by-row in the database (assuming that is required) will win on all those fronts. Is it that the developer wants to intentionally slow down the project, create one that is harder to test and has more cumbersome reuse and is harder to maintain?Unfortunately, this scenario is all too common. The developers wait until the last second and then decide that the "only" solution is to build a poor solution. If the problem is really set-based, then yes doing a one-time procedure shouldn't be an issue. If this is an on-going problem, and "must" be done row-by-row, then doing it in the business tier is a better solution and that should be everyone's goal I would think.[quote]b. We're not. We're batch processing the records, however each row requires handling in a different, and complex way depending on the data which 'hangs off' it. There are up to 300 different 'events' which can fire depending on the processing logic.[/quote]If you can encapsulate the logic into a cursor, then you can encapsulate it into middle-tier code that will do the same *and* you get a bunch of other bonus features such as better testing tools (we are going to test it right?), parallel processing and so on.[quote]c. that would require development time which we don't have. It wasn't specified *whinge whinge*In defence of point c, The app option is a red herring anyway, cos it would be external to the DB, and would still have to pore through each row, do the very specific logic to each row, requiring the pulling-in of yet more data. The pull/process/push time would be rather extensive. Even with 1000 simultaneous connections (assuming the DB didn't fall over due to locking on the tables), it'd still take more than 10 times longer to complete that way, and would be yet another service to have to monitor, document, maintain, etc.[/quote]I think we also need to differentiate between a one-off emergency, never to be used again solution vs a baked-in part of the system architecture solution. I'm talking about the later. In the case of the former, since the goal is still to find the fastest, best solution, it would probably still be faster to write an app to do it simply because of testing reasons. RE: Connections, it wouldn't be 1000 simultaneous connections. It would be more like 10-100 depending on the size of the data and the amount of information being pulled. You can pull a million rows rather quickly to a middle-tier component. Remember that connections are dropped once you pull a batch of data to process and you are presumably using something that uses connection pooling like ADO or ADO.NET which is reusing those connections. The problems with locking will still exist in the cursor solution *and* you don't get parallel processing. I.e., the whole process would be forced to be run in serial. RE: Monitoring and time to developThe claim that it would be slower to develop, IMO is a factor of expertise. If you have developers that know how to build small components or utility apps, it should in fact be much faster than trying to do in stored procs for the simple fact that there are better testing tools for these types of applications and services. In addition to all that, even if it is a cursor solution in SQL Server, it still needs to be monitored, documented and maintained. That isn't going to change. I realize that you agree and are playing devil's advocate. I think in many of these cases, it comes down to understanding the alternatives and the developer expertise. If the only thing available is a crack DBA and a bunch of inexperienced or undisciplined developers, then it might be the case that a cursor solution (assuming that is actually required) is the "better" solution. Of course, in that scenario, a better HR person would also be a better solution :). Sat, 26 Jun 2010 10:34:02 GMTThomas-282729http://www.sqlservercentral.com/Forums/Topic696297-1226-1.aspx[quote][b]Steve-524674 (4/14/2009)[/b][hr]OK, the issue is simply one of coding style for the most part. The optimizer should handle "declarative" code [whatever that is] in much the same way that it would handle cursors or the product [database] is not built right. I write cursors all the time without any appreciable difference from other code. It's all basic procedural programming. There is no declarative and cursor-driven difference. [/quote]This is absolutely untrue, and indicates that (1) you haven't actually compared the performance difference, or (2) your data sets are extremely small in which case the wall-clock difference may be negligible. Try looking at the query plans to see how many resources SQL has to throw at your queries to make them run. Wall clock speed is not the only aspect that needs to be considered.When you use a cursor you are explicitly overriding SQL Server's built-in ability to optimize execution of your code. You're also not going to scale well with a cursor since performance is generally linear. 10X more iterations of the cursor = 10X worse performance.Don't get me wrong, I personally feel there are some uses for cursors, but they are few and far between. My general rule of thumb is:(1) If the dataset is extremely small and must be acted on in a predefined order (many common DBA tasks fall into this category; sending administrative emails or managing indexes are such tasks), or(2) The business logic required is so complex it's infeasible to do it without row-by-row processing (generally these are prime candidates to do in a more optimal language or tool like an ETL tool or OO language).A lot of people take reason #2 above as free license to write *everything* in cursors and poor man's cursors (loops) because they don't understand declarative programming. Many of those try to apply object-oriented design patterns to SQL, which is not designed to handle such design patterns in an optimal fashion. Cursors are a hammer and not every problem is a nail.ThanksMike CSat, 26 Jun 2010 10:26:51 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic696297-1226-1.aspx[quote][b]Tom.Thomson (6/26/2010)[/b][hr][quote][b]Thomas-282729 (6/25/2010)[/b][hr][quote][b]Nick Walton (6/25/2010)[/b][hr]I'm really looking forward to this series, as there's been a higher than average number of "Cursors are baaaaaad" posts on SSC recently. Your article seems to indicate that cursors can be eliminated entirely....I'm really looking for a solution to the following:-1. How do I execute a stored procedure for each row in a table (temporary or otherwise), which does some very complex calculations (using other tables) and then updates (or inserts if it doesn't already exist) a row in another table (a common operation for month-end tasks, for example), or if spooling out to external consumers, such as Broker peers?I don't believe SQL Server 2005 allows a) an sp to be executed per-row or b) a function to update a table...Sadly, logistics and transfer speeds make the option of external processing not possible. I hope that one of your articles covers this situation, as I'd love to get rid of all these cursors.[/quote]There is an answer, but you probably won't like it: don't do it in a stored procedure. If you cannot accomplish a given piece of work in a set-based format, it was probably not meant to be done in a database. Stored procedures were not meant to encapsulate complex business logic of the type you suggest. Databases are first and foremost meant to serve up data. Further, if there is so much data that streaming it to an external service is impractical, then I would suggest that it is also impractical to do it row-by-row in a stored proc. If you can do it row-by-row in a proc, then you can do it row-by-row in an external service.[/quote]That answer sort of scares me - it seems to assume that because something row-oriented has been written one should assume that whatever it is is too complicated to do in SQL. I would approach it very differently, because I know full well that starting from a row-oriented Stored Procedure and assuming that that row-oriented SP can exist unchanged in a set-oriented solution is pure nonsense. [/quote]No. That is not what I'm saying. I'm saying that if you have something that is row-oriented that [b]can't[/b] be written set-oriented or requires doing dynamic SQL in the stored procedure, it does not belong in a stored procedure. T-SQL was not designed to encapsulate complex business logic. Middle-tier components and their languages are substantially better at that. [quote]The row oriented SP computing a new/updated collection of values based on a single row of one of the source tables can be re-jigged to be a set oriented procedure producing a set of new/updated collections of values based on the set of rows in that base table; this is usually fairly trivial to do, and won't involve an explicit cursor. [/quote]The row oriented SP computing a new/updated collection of values based on a single row of one of the source tables [b][i]OFTEN[/i][/b] can be re-jigged to be a set oriented procedure producing...I've seen too many examples of people that have tried to encapsulate too much into stored procedures. Eventually, then end up with a series of heinously long stored procs that encapsulate business logic on a single set of data and run that proc using a cursor over a series of rows because the logic is too complex to put into a single set-based proc. That is what business layer components and services are meant to solve. T-SQL was not meant to replace a business tier or layer.Sat, 26 Jun 2010 09:58:17 GMTThomas-282729http://www.sqlservercentral.com/Forums/Topic696297-1226-1.aspx[quote][b]Nick Walton (6/25/2010)[/b][hr]DBA: "So what's your solution?"Dev: "I will pass in an XML file with a full list of all the record IDs which need to be processed, and the database will expand it and process each of the rows, and return a total."DBA: "No, that's silly, and will require a Cursor, which is *bad*"[/quote](a) the Dev is unhelpful suggesting an XML file (verbose junk that is slow to parse) instead of a newline-separated list here (unless the record IDs can include newlines).(b) the DBA is not very competent if he thinks he needs to use a Cursor and go row by row procedural just because he is given a list of the records that have to be processed - evidently not really up to set-oriented thinking. Importing a list of IDs into a table and using that table as one of the sources in a query surely isn't all that bizarre and difficult to conceive a course of action?Sat, 26 Jun 2010 08:58:20 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic696297-1226-1.aspx[quote][b]Thomas-282729 (6/25/2010)[/b][hr][quote][b]Nick Walton (6/25/2010)[/b][hr]I'm really looking forward to this series, as there's been a higher than average number of "Cursors are baaaaaad" posts on SSC recently. Your article seems to indicate that cursors can be eliminated entirely....I'm really looking for a solution to the following:-1. How do I execute a stored procedure for each row in a table (temporary or otherwise), which does some very complex calculations (using other tables) and then updates (or inserts if it doesn't already exist) a row in another table (a common operation for month-end tasks, for example), or if spooling out to external consumers, such as Broker peers?I don't believe SQL Server 2005 allows a) an sp to be executed per-row or b) a function to update a table...Sadly, logistics and transfer speeds make the option of external processing not possible. I hope that one of your articles covers this situation, as I'd love to get rid of all these cursors.[/quote]There is an answer, but you probably won't like it: don't do it in a stored procedure. If you cannot accomplish a given piece of work in a set-based format, it was probably not meant to be done in a database. Stored procedures were not meant to encapsulate complex business logic of the type you suggest. Databases are first and foremost meant to serve up data. Further, if there is so much data that streaming it to an external service is impractical, then I would suggest that it is also impractical to do it row-by-row in a stored proc. If you can do it row-by-row in a proc, then you can do it row-by-row in an external service.[/quote]That answer sort of scares me - it seems to assume that because something row-oriented has been written one should assume that whatever it is is too complicated to do in SQL. I would approach it very differently, because I know full well that starting from a row-oriented Stored Procedure and assuming that that row-oriented SP can exist unchanged in a set-oriented solution is pure nonsense. The row oriented SP computing a new/updated collection of values based on a single row of one of the source tables can be re-jigged to be a set oriented procedure producing a set of new/updated collections of values based on the set of rows in that base table; this is usually fairly trivial to do, and won't involve an explicit cursor. This can then be taken a step further: based on the set produced, a couple of lines can be written to use it to do the updates and inserts, and that can be included in the stored procedure. So now there's a single set-oriented SP which deals with the whole base table instead of with a single row. There was no need to map an row-oriented SP across all the rows of the table, in fact trying to do that would be pointeless because it retains the row-oriented nature of the solution, what's needed is to get rid of the row-orientation altogether.Barry covers moving from single row processing to set-oriented processing at an elementary level in part two of the series, it is easy to take what he's written there and generalize it to a situation like this, since it makes no difference at all that the processing code inside the loop is expressed as a stored procedure.Sat, 26 Jun 2010 08:41:51 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic696297-1226-1.aspx[quote][b]RBarryYoung (6/25/2010)[/b][hr][quote][b]Thomas-282729 (6/25/2010)[/b][hr][quote][b]Nick Walton (6/25/2010)[/b][hr]I'm really looking forward to this series, as there's been a higher than average number of "Cursors are baaaaaad" posts on SSC recently. Your article seems to indicate that cursors can be eliminated entirely....I'm really looking for a solution to the following:-1. How do I execute a stored procedure for each row in a table (temporary or otherwise), which does some very complex calculations (using other tables) and then updates (or inserts if it doesn't already exist) a row in another table (a common operation for month-end tasks, for example), or if spooling out to external consumers, such as Broker peers?I don't believe SQL Server 2005 allows a) an sp to be executed per-row or b) a function to update a table...Sadly, logistics and transfer speeds make the option of external processing not possible. I hope that one of your articles covers this situation, as I'd love to get rid of all these cursors.[/quote]There is an answer, but you probably won't like it: don't do it in a stored procedure. If you cannot accomplish a given piece of work in a set-based format, it was probably not meant to be done in a database. ...[/quote]Actually there is a much better answer, rewrite your stored procedures so that instead of needing to be called row-by-row, they can operate on your entire rowsets at one time.[/quote]Thanks for your response. Believe me, I've tried, but there are some things which just seem mutually exclusive. 4th Normal Form vs. rapid reporting. Iterative processing vs. set-based programming. Anything to do with finance vs. any rational logic. It's just one of those things.The previous posts' example of asymptotic tax calculation isn't unique, or even rare. I'm looking forward to reading about any SQL tricks I've not come across, however, as performance improvement takes up a lot of my time. :)Best Wishes,NickSat, 26 Jun 2010 08:13:29 GMTNick W*http://www.sqlservercentral.com/Forums/Topic696297-1226-1.aspxYou can't honestly believe this.[quote][b]Steve-524674 (4/14/2009)[/b][hr]OK, the issue is simply one of coding style for the most part. The optimizer should handle "declarative" code [whatever that is] in much the same way that it would handle cursors or the product [database] is not built right. I write cursors all the time without any appreciable difference from other code. It's all basic procedural programming. There is no declarative and cursor-driven difference. [/quote]Sat, 26 Jun 2010 06:22:25 GMTsw3090-620912http://www.sqlservercentral.com/Forums/Topic696297-1226-1.aspx[quote][b]Tom Garth (6/25/2010)[/b][hr]Wow! I can't believe some of the readers took this article so personally.In my case you are preaching to the choir, and never the less produced a highly entertaining piece. I look forward to following the series.[/quote]Thanks Tom! And I'm looking forward to finishing it, just as soon as I get some free time ... :(Fri, 25 Jun 2010 22:34:51 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic696297-1226-1.aspx[quote][b]Thomas-282729 (6/25/2010)[/b][hr][quote][b]Nick Walton (6/25/2010)[/b][hr]I'm really looking forward to this series, as there's been a higher than average number of "Cursors are baaaaaad" posts on SSC recently. Your article seems to indicate that cursors can be eliminated entirely....I'm really looking for a solution to the following:-1. How do I execute a stored procedure for each row in a table (temporary or otherwise), which does some very complex calculations (using other tables) and then updates (or inserts if it doesn't already exist) a row in another table (a common operation for month-end tasks, for example), or if spooling out to external consumers, such as Broker peers?I don't believe SQL Server 2005 allows a) an sp to be executed per-row or b) a function to update a table...Sadly, logistics and transfer speeds make the option of external processing not possible. I hope that one of your articles covers this situation, as I'd love to get rid of all these cursors.[/quote]There is an answer, but you probably won't like it: don't do it in a stored procedure. If you cannot accomplish a given piece of work in a set-based format, it was probably not meant to be done in a database. ...[/quote]Actually there is a much better answer, rewrite your stored procedures so that instead of needing to be called row-by-row, they can operate on your entire rowsets at one time.Fri, 25 Jun 2010 22:23:45 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic696297-1226-1.aspx[quote][b]Jeff Moden (6/25/2010)[/b][hr][quote][b]Michael Jenck (6/25/2010)[/b][hr]I have been trying to figure out a way to eliminate the following cursor. I figure this would be a good place to post this seeing the title of this article :-).The only conditions are that I don't know in advance what the child and parent records are - just that at least 1 of each exists.(Note: I'm not a DBA. I fall in the category of knowing just enough SQL to be dangerous.)[code="sql"]IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Temp]') AND type in (N'U'))DROP TABLE [dbo].[Temp]GOIF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Temp2]') AND type in (N'U'))DROP TABLE [dbo].[Temp2]GO/****** Object: Table [dbo].[Temp] Script Date: 10/28/2009 08:20:47 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[Temp]( [ObjectId] [int] NOT NULL, [ObjectName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]INSERT INTO [dbo].[Temp] ([ObjectId], [ObjectName])SELECT 1, 'Parent 1' UNION ALLSELECT 2, 'Parent 2' UNION ALLSELECT 3, 'Parent 3' UNION ALLSELECT 4, 'Parent 4' CREATE TABLE [dbo].[Temp2]( [ObjectId] [int] NOT NULL, [ObjectName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [ParentId] [int] NOT NULL, [ChildId] [int] NOT NULL) ON [PRIMARY]INSERT INTO [dbo].[Temp2] ([ObjectId], [ObjectName], [ParentId], [ChildId])SELECT 1, 'Option 1',1, 10 UNION ALLSELECT 1, 'Option 1',2, 20 UNION ALLSELECT 1, 'Option 1',3, 30 UNION ALLSELECT 1, 'Option 1',4, 40 UNION ALLSELECT 1, 'Option 2',1, 11 UNION ALLSELECT 1, 'Option 2',2, 21 UNION ALLSELECT 1, 'Option 2',3, 31 UNION ALLSELECT 1, 'Option 2',4, 41 -- FROM: http://www.sqlservercentral.com/articles/T-SQL/63681/SELECT ObjectId, ObjectName, --(4) [1] AS [Parent 1], --(3) [2] AS [Parent 2], [3] AS [Parent 3], [4] AS [Parent 4] FROM (SELECT ObjectId, ObjectName, ParentId, ChildId FROM dbo.Temp2 WHERE (ObjectId = 1)) AS src --(1) PIVOT (Min(ChildId) FOR ParentId IN ([1],[2],[3],[4])) AS pvt --(2) ORDER BY ObjectNameDECLARE @ObjectId intDECLARE @ObjectName varchar(25)DECLARE @NewColumnName varchar(8000)DECLARE @NewColumnValue varchar(8000)DECLARE @NewSQL varchar(8000)DECLARE Object_cursor CURSOR FOR SELECT ObjectId, ObjectName FROM Temp FOR READ ONLY OPEN Object_cursorFETCH FROM Object_cursor INTO @ObjectId, @ObjectName WHILE @@FETCH_STATUS = 0 BEGIN IF(@NewColumnName IS NULL) BEGIN SET @NewColumnName = '[' + CAST(@ObjectId as varchar(5)) + '] AS [' + @ObjectName + ']' SET @NewColumnValue = '[' + CAST(@ObjectId as varchar(5)) + ']' END ELSE BEGIN SET @NewColumnName = @NewColumnName + ',[' + CAST(@ObjectId as varchar(5)) + '] AS [' + @ObjectName + ']' SET @NewColumnValue = @NewColumnValue + ',[' + CAST(@ObjectId as varchar(5)) + ']' END FETCH NEXT FROM Object_cursor INTO @ObjectId, @ObjectName ENDCLOSE Object_cursorDEALLOCATE Object_cursorSET @NewSQL = 'SELECT ObjectId, ObjectName, ' + @NewColumnName + ' FROM (SELECT ObjectId, ObjectName, ParentId, ChildId FROM dbo.Temp2 WHERE (ObjectId = 1)) AS src PIVOT (Min(ChildId) FOR ParentId IN (' + @NewColumnValue + ')) AS pvt ORDER BY ObjectName'EXEC (@NewSQL)[/code][/quote]You've already cited one article to get you started in your code. Here's part 2 which should help you get over the hump on such a dynamic crosstab...[url]http://www.sqlservercentral.com/articles/Crosstab/65048/[/url][/quote]Thanks Jeff, I was hoping that you would catch this one. :-)Fri, 25 Jun 2010 22:15:20 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic696297-1226-1.aspx[quote][b]xor 52318 (6/25/2010)[/b][hr]The topic of the series is interesting and timely, but the writing style in the introduction is goofy. It could have been replaced by a single sentence:"Cursors are slow and resource intensive, but there is a better way."[/quote]Read back in this thread, I've already addressed this nit before. Short answer is: your approach has been tried before by many fine writers, yet has failed to reach many who need to hear this message. I have used a different approach this time in order to reach some who may have passed over a more standard introduction.Fri, 25 Jun 2010 22:13:38 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic696297-1226-1.aspx[quote][b]Colin Betteley (6/25/2010)[/b][hr]I have seen a number of articles recently on moving away from cursors and fully understand the performance benefits in doing so.That said, I use cursors as I need to get data, manipulate it and then send specific Emails based on that manipulated data. At the moment I cannot see a way of doing this in SQL other than by using cursors. An article on this particular aspect would be most welcome to me and I suspect a large number of others.Thank you in advance.Colin[/quote]Colin: I have heard this before (that is, needing cursors for customized merge-mailing from SQL Server) and I would love to address it, but in order to do so, I need concrete examples to work with. Despite several request on my part, I have yet to receive any such example that I can test and compare to a non-cursor solution. If you could the data and could to demonstrate your cursor Email solution, then I would be happry to include my analysis and (attempted) solution in a future article.Fri, 25 Jun 2010 22:08:00 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic696297-1226-1.aspx[quote][b]Nick Walton (6/25/2010)[/b][hr][quote][b]Thomas-282729 (6/25/2010)[/b][hr]There is an answer, but you probably won't like it: don't do it in a stored procedure. If you cannot accomplish a given piece of work in a set-based format, it was probably not meant to be done in a database. Stored procedures were not meant to encapsulate complex business logic of the type you suggest. Databases are first and foremost meant to serve up data. Further, if there is so much data that streaming it to an external service is impractical, then I would suggest that it is also impractical to do it row-by-row in a stored proc. If you can do it row-by-row in a proc, then you can do it row-by-row in an external service.[/quote]Thanks for the response. I agree, in general, but until you've worked with SmartClients, remote connectors, WFC, Web Services, and all that other supposedly "good" stuff Microsoft is spewing out on the dev community, you've not had to have the following argument with developers:-Dev: "We need to put this logic in the Database"DBA: "That's business logic, and should live in the service BL Layer"Dev: "We must pull out all billing and banking records for each ident. Each connection takes around one tenth of a second, and therefore can take around three seconds per record to process. To process the whole database will take around sixty-four days".DBA: "Is there no faster way to do this?"Dev: "Possibly, but we have a deadline, and it's almost the end of the <insert financial deadline here>".DBA: "So what's your solution?"Dev: "I will pass in an XML file with a full list of all the record IDs which need to be processed, and the database will expand it and process each of the rows, and return a total."DBA: "No, that's silly, and will require a Cursor, which is *bad*"Dev: "You'll have to explain to the customer why they won't be able to collect their <insert financial term>, they will lose about eight million pounds."..rinse repeat.There are many occasions where the slide into poor practice is unavoidable or just plain impossible by export/import. A cursor achieves the above two-month operation in around an hour (still too slow for the customers, but hey, it's better than 64-days).Anyway, this is a very specific issue, and I'm interested to see how I can get rid of cursors effectively for such things....[/quote]DBA: "Don't tell the customer to go on a diet just yet. Maybe we could pass in a table-valued parameter with all the customer IDs and just do a simple INNER JOIN to get *all* our data back?"Dev: "But we're not on SQL 2008."DBA: "Then pass in the IDs as XML and use the XML data type nodes method to shred it to relational data. Perform INNER JOIN."Dev: "But XML is so complicated!"DBA: "Google Erland Sommarskog's article 'Arrays and Lists in SQL Server' and learn how to pass it in as a delimited list/simulated array. You have used delimited lists and arrays, right?"...I love these philosophical thought experiments!ThanksMike CFri, 25 Jun 2010 17:19:23 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic696297-1226-1.aspx[quote][b]Thomas-282729 (6/25/2010)[/b][hr]DBA: A. Why is it my problem that someone else did not do a better job of planning ahead? B. why are you pulling one record at a time instead of pulling a batch of records? C. Why not write a small app that batches and processes the data? [/quote]I agree, I've been there, so I can give you the responses.a. Because you're holding the project up. It would be on-schedule if you didn't just let the developers write the code. there's no Right and Wrong answer. Just write an SP to do it, and the project can get delivered.b. We're not. We're batch processing the records, however each row requires handling in a different, and complex way depending on the data which 'hangs off' it. There are up to 300 different 'events' which can fire depending on the processing logic.c. that would require development time which we don't have. It wasn't specified *whinge whinge*In defence of point c, The app option is a red herring anyway, cos it would be external to the DB, and would still have to pore through each row, do the very specific logic to each row, requiring the pulling-in of yet more data. The pull/process/push time would be rather extensive. Even with 1000 simultaneous connections (assuming the DB didn't fall over due to locking on the tables), it'd still take more than 10 times longer to complete that way, and would be yet another service to have to monitor, document, maintain, etc.[quote][b]Thomas-282729 (6/25/2010)[/b][hr]IMHO[/quote]I think your opinion is right in the absolute sense, but sometimes is not possible from a physical, conceptual or practical viewpoint.Cheers,NickFri, 25 Jun 2010 14:49:49 GMTNick W*http://www.sqlservercentral.com/Forums/Topic696297-1226-1.aspxI find that most times I end up using a cursor, its because I'm doing something that would be better done via a regular program, like an emailer or some other non data processing action.I think its one of those cases where you use what you are familiar with. An example, our C# dev asked me if I had a tool for comparing data differences in identical tables. I did not, but I wrote one, using just no loops but a bit of dynamics sql. He turned out a C# program within the same time frame. Mine ran quicker, but goes to show there are many ways to achieve the same result.Have a great weekend everybody!Fri, 25 Jun 2010 12:54:23 GMTmarklegoszhttp://www.sqlservercentral.com/Forums/Topic696297-1226-1.aspxI created my FIRST Cursor yesterday. I had to read up on how to do it. It was obvious this was creating a RBAR process as I watched it run. I'm looking forward to your follow-up articles and maybe I can go back and put a stake in that thing's heart.Fri, 25 Jun 2010 12:43:16 GMTgitmohttp://www.sqlservercentral.com/Forums/Topic696297-1226-1.aspxHi my name is Jeff.I am new to this forum. About a year ago I started at a company and last year they upgraded to SQL 2005 and nearly every procedure uses a cursor, in some of the most bizarre circumstances.:sick: I have been trying to rewrite them as I have had to modifiy them, but some of them are so embedded in other processes or so long and confusing with cursors inside other cursors :sick: that I hesitate to do so.:unsure:But I have come across a scenario that i am at a loss on how to avoid a loop. And maybe this is an exception to the rule about using loops.Any help or advice would be appreciated.The purpose of the procedure is to back into the SDI tax rate when the total SDI Tax has exceeded it's limit, so the new net Tax value can be recalculated. It usally has to loop through the process 5-10 times to return.See everytime the SDI tax rate changes the Total net tax changes. But SDI tax i based on the Net Total Tax.So everytime you adjust the tax rate so that the SDI TAX is at the limit, then the NetTotal Tax changes and the SDI rate is not accurate, But every time you make the adjustment you get closer to the SDI tax.I realize that this is circular logic, but it does retrun the accurate data because the SDI rate keeps getting closer and closer to the correct rate needed to set the SDI tax at its limit.Here is a function that I use a loop for.[code="plain"]CREATE FUNCTION dbo.fn_TaxLimitCalcTable ( @startVal decimal(25,18), @LimitVal decimal(25,18), @YTDVal decimal(25,18), @NetBenefit decimal(25,18), @Fed decimal(25,18), @staterate decimal(25,18), @Soc decimal(25,18), @Med decimal(25,18), @CurRate decimal(25,18) )RETURNS @TaxLimit TABLE ( NewSDIRate decimal(25,18), adNetval decimal(25,18), adjustedval decimal(25,18), Fed decimal(25,18), Med decimal(25,18), staterate decimal(25,18), Soc decimal(25,18))AS/**************************************************** SP Name: fn_TaxLimitCalcTable Description: This function re-calculates the associated tax and rates when the maximum tax limit has been reached or exceeded. ****************************************************/ BEGIN DECLARE @w2amt1 decimal(25,18), @NewSDIRate decimal(25,18), @adjustedval decimal(25,18), @adNetval decimal(25,18),@Returned decimal(25,18), @Fed1 decimal(25,18),@Med1 decimal(25,18), @staterate1 decimal(25,18),@Soc1 decimal(25,18) SELECT @NewSDIRate = @CurRate, @adjustedval=@startVal, @adNetval=@NetBenefit*(1/(1-(@CurRate+@Fed+@staterate+@Soc+@Med))) --set if startvalue matches IF @YTDVal > 0 and @LimitVal > 0 BEGIN WHILE @adjustedval <> (@LimitVal-@YTDVal) BEGIN SELECT @NewSDIRate = (@LimitVal-@YTDVal)/@adNetval SELECT @adNetval = ROUND(@NetBenefit*(1/(1-(@NewSDIRate+@Fed+@staterate+@Soc+@Med))),2) SELECT @adjustedval = ROUND(@NewSDIRate * @adNetval,2) END SELECT @Fed1 = round(@Fed * @adNetval,2) SELECT @Med1 = ROUND(@Med * @adNetval,2) SELECT @staterate1 = ROUND(@staterate * @adNetval,2) SELECT @Soc1 = ROUND(@Soc * @adNetval,2) END -- Return the result of the function insert @TaxLimit select @NewSDIRate, @adNetval, @adjustedval, @Fed1, @Med1, @staterate1, @Soc1 RETURN ENDGO[/code]Thank you,JeffFri, 25 Jun 2010 11:54:09 GMTjeffwilson2http://www.sqlservercentral.com/Forums/Topic696297-1226-1.aspxWow! I can't believe some of the readers took this article so personally.In my case you are preaching to the choir, and never the less produced a highly entertaining piece. I look forward to following the series.Fri, 25 Jun 2010 11:52:51 GMTTom Garthhttp://www.sqlservercentral.com/Forums/Topic696297-1226-1.aspx[quote][b]Jeff Moden (6/25/2010)[/b][hr][quote][b]Thomas-282729 (6/25/2010)[/b][hr][quote][b]Michael Jenck (6/25/2010)[/b][hr]I have been trying to figure out a way to eliminate the following cursor. I figure this would be a good place to post this seeing the title of this article :-).The only conditions are that I don't know in advance what the child and parent records are - just that at least 1 of each exists.(Note: I'm not a DBA. I fall in the category of knowing just enough SQL to be dangerous.)<snip>[/quote]When you hit a problem in T-SQL that requires a cumbersome solution, you should sit back and consider whether it ought to be done in T-SQL in the first place. The SQL language is fundamentally not designed for dynamic column generation. Further, T-SQL was not designed for fancy string manipulation. So, the solution to eliminating a cursor in this case is to not do dynamic crosstabs in T-SQL. Build your query in a middle-tier component or reporting tool. Is it [i]possible[/i] to do it in T-SQL? Yes, just like it is possible to use Excel as a database or to write memos. That does not mean you should use it for this purpose just as you shouldn't use T-SQL for dynamic crosstabs.[/quote]While I agree that creating dynamic crosstabs on properly preaggregated data using something like SSRS is usually a much better way to go, there are cases where you need be able to do a dynamic crosstab in T-SQL (usually bad DB design or "special" requirements). Although T-SQL isn't usually the place to do such a thing, you'd better know how to do it so you don't have to tell your boss that you can't do it without getting another chunk of software involved. T-SQL does have more than just data storage capabilities in the form of DML (Data [i]Manipulation [/i]Language) and it's frequently much more effective than relegating such tasks to the "outside" world.[/quote]Jeff,Thanks for pointing out the fact that there was a part 2 to the article. After reading and searching I came across a post that used [code="sql"]COALESCE[/code]Which is what I needed to use. My working example is now below.So my non cursor version is[code="sql"]IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Temp]') AND type in (N'U'))DROP TABLE [dbo].[Temp]GOIF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Temp2]') AND type in (N'U'))DROP TABLE [dbo].[Temp2]GO/****** Object: Table [dbo].[Temp] Script Date: 10/28/2009 08:20:47 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[Temp]( [ObjectId] [int] NOT NULL, [ObjectName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]INSERT INTO [dbo].[Temp] ([ObjectId], [ObjectName])SELECT 1, 'Parent 1' UNION ALLSELECT 2, 'Parent 2' UNION ALLSELECT 3, 'Parent 3' UNION ALLSELECT 4, 'Parent 4' CREATE TABLE [dbo].[Temp2]( [ObjectId] [int] NOT NULL, [ObjectName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [ParentId] [int] NOT NULL, [ChildId] [int] NOT NULL) ON [PRIMARY]INSERT INTO [dbo].[Temp2] ([ObjectId], [ObjectName], [ParentId], [ChildId])SELECT 1, 'Option 1',1, 10 UNION ALLSELECT 1, 'Option 1',2, 20 UNION ALLSELECT 1, 'Option 1',3, 30 UNION ALLSELECT 1, 'Option 1',4, 40 UNION ALLSELECT 1, 'Option 2',1, 11 UNION ALLSELECT 1, 'Option 2',2, 21 UNION ALLSELECT 1, 'Option 2',3, 31 UNION ALLSELECT 1, 'Option 2',4, 41 DECLARE @NewColumnName varchar(8000)DECLARE @NewColumnValue varchar(8000)DECLARE @NewSQL varchar(8000)SELECT @NewColumnName = COALESCE(@NewColumnName + ', ', '') + '[' + CAST([ObjectId] AS varchar(5))+ '] AS [' + ObjectName + ']', @NewColumnValue = COALESCE(@NewColumnValue + ', ', '') + '[' + CAST([ObjectId] AS varchar(5))+ ']'FROM [dbo].[Temp]SET @NewSQL = 'SELECT ObjectId, ObjectName, ' + @NewColumnName + ' FROM (SELECT ObjectId, ObjectName, ParentId, ChildId FROM dbo.Temp2 WHERE (ObjectId = 1)) AS src PIVOT (Min(ChildId) FOR ParentId IN (' + @NewColumnValue + ')) AS pvt ORDER BY ObjectName'EXEC (@NewSQL)[/code]Fri, 25 Jun 2010 10:52:14 GMTMichael Jenckhttp://www.sqlservercentral.com/Forums/Topic696297-1226-1.aspx[quote][b]Nick Walton (6/25/2010)[/b][hr][quote][b]Thomas-282729 (6/25/2010)[/b][hr]There is an answer, but you probably won't like it: don't do it in a stored procedure. If you cannot accomplish a given piece of work in a set-based format, it was probably not meant to be done in a database. Stored procedures were not meant to encapsulate complex business logic of the type you suggest. Databases are first and foremost meant to serve up data. Further, if there is so much data that streaming it to an external service is impractical, then I would suggest that it is also impractical to do it row-by-row in a stored proc. If you can do it row-by-row in a proc, then you can do it row-by-row in an external service.[/quote]Thanks for the response. I agree, in general, but until you've worked with SmartClients, remote connectors, WFC, Web Services, and all that other supposedly "good" stuff Microsoft is spewing out on the dev community, you've not had to have the following argument with developers:-Dev: "We need to put this logic in the Database"DBA: "That's business logic, and should live in the service BL Layer"Dev: "We must pull out all billing and banking records for each ident. Each connection takes around one tenth of a second, and therefore can take around three seconds per record to process. To process the whole database will take around sixty-four days".DBA: "Is there no faster way to do this?"Dev: "Possibly, but we have a deadline, and it's almost the end of the <insert financial deadline here>".DBA: "So what's your solution?"Dev: "I will pass in an XML file with a full list of all the record IDs which need to be processed, and the database will expand it and process each of the rows, and return a total."DBA: "No, that's silly, and will require a Cursor, which is *bad*"Dev: "You'll have to explain to the customer why they won't be able to collect their <insert financial term>, they will lose about eight million pounds."..rinse repeat.There are many occasions where the slide into poor practice is unavoidable or just plain impossible by export/import. A cursor achieves the above two-month operation in around an hour (still too slow for the customers, but hey, it's better than 64-days).Anyway, this is a very specific issue, and I'm interested to see how I can get rid of cursors effectively for such things....[/quote]DBA: A. Why is it my problem that someone else did not do a better job of planning ahead? B. why are you pulling one record at a time instead of pulling a batch of records? C. Why not write a small app that batches and processes the data? That will be significantly faster to develop and will run faster than trying to hack something together in T-SQL and you could even make it multi-threaded so that will perform the batch operations in parallel. Bonus points for making it so that you can monitor the progress of the operations....Cursors aren't "evil" per se but they are often the wrong tool for the job. To its credit, T-SQL can solve many problems for which it was not intended but that creates its own issues. The problem here is that it is [i]possible[/i] to do it in T-SQL even though T-SQL is the wrong solution. I think I've had more problems from developers hacking things together because it was "possible" than just about anything else. It is akin to building a database system using email because it is "possible". Lots of dynamic SQL or complex logic encapsulated in stored procedures are examples of misuse of T-SQL IMO.Fri, 25 Jun 2010 10:09:30 GMTThomas-282729http://www.sqlservercentral.com/Forums/Topic696297-1226-1.aspxI agree with Brad Neufeld this article bites. Another condescending rant.Fri, 25 Jun 2010 09:41:14 GMTehaustin78737http://www.sqlservercentral.com/Forums/Topic696297-1226-1.aspxWe must be in to summer rerun season. :cool:Maybe the unknown procedural language was COBOL or some derivative?Fri, 25 Jun 2010 09:31:27 GMTOCTomhttp://www.sqlservercentral.com/Forums/Topic696297-1226-1.aspxYour description of the child Cursor describes my son to a tee.Bastard love-child. Hmmm. :blush:Fri, 25 Jun 2010 08:46:35 GMTgitmohttp://www.sqlservercentral.com/Forums/Topic696297-1226-1.aspx[quote][b]Thomas-282729 (6/25/2010)[/b][hr]There is an answer, but you probably won't like it: don't do it in a stored procedure. If you cannot accomplish a given piece of work in a set-based format, it was probably not meant to be done in a database. Stored procedures were not meant to encapsulate complex business logic of the type you suggest. Databases are first and foremost meant to serve up data. Further, if there is so much data that streaming it to an external service is impractical, then I would suggest that it is also impractical to do it row-by-row in a stored proc. If you can do it row-by-row in a proc, then you can do it row-by-row in an external service.[/quote]Thanks for the response. I agree, in general, but until you've worked with SmartClients, remote connectors, WFC, Web Services, and all that other supposedly "good" stuff Microsoft is spewing out on the dev community, you've not had to have the following argument with developers:-Dev: "We need to put this logic in the Database"DBA: "That's business logic, and should live in the service BL Layer"Dev: "We must pull out all billing and banking records for each ident. Each connection takes around one tenth of a second, and therefore can take around three seconds per record to process. To process the whole database will take around sixty-four days".DBA: "Is there no faster way to do this?"Dev: "Possibly, but we have a deadline, and it's almost the end of the <insert financial deadline here>".DBA: "So what's your solution?"Dev: "I will pass in an XML file with a full list of all the record IDs which need to be processed, and the database will expand it and process each of the rows, and return a total."DBA: "No, that's silly, and will require a Cursor, which is *bad*"Dev: "You'll have to explain to the customer why they won't be able to collect their <insert financial term>, they will lose about eight million pounds."..rinse repeat.There are many occasions where the slide into poor practice is unavoidable or just plain impossible by export/import. A cursor achieves the above two-month operation in around an hour (still too slow for the customers, but hey, it's better than 64-days).Anyway, this is a very specific issue, and I'm interested to see how I can get rid of cursors effectively for such things....Fri, 25 Jun 2010 08:14:06 GMTNick W*http://www.sqlservercentral.com/Forums/Topic696297-1226-1.aspx[quote][b]Thomas-282729 (6/25/2010)[/b][hr][quote][b]Michael Jenck (6/25/2010)[/b][hr]I have been trying to figure out a way to eliminate the following cursor. I figure this would be a good place to post this seeing the title of this article :-).The only conditions are that I don't know in advance what the child and parent records are - just that at least 1 of each exists.(Note: I'm not a DBA. I fall in the category of knowing just enough SQL to be dangerous.)<snip>[/quote]When you hit a problem in T-SQL that requires a cumbersome solution, you should sit back and consider whether it ought to be done in T-SQL in the first place. The SQL language is fundamentally not designed for dynamic column generation. Further, T-SQL was not designed for fancy string manipulation. So, the solution to eliminating a cursor in this case is to not do dynamic crosstabs in T-SQL. Build your query in a middle-tier component or reporting tool. Is it [i]possible[/i] to do it in T-SQL? Yes, just like it is possible to use Excel as a database or to write memos. That does not mean you should use it for this purpose just as you shouldn't use T-SQL for dynamic crosstabs.[/quote]While I agree that creating dynamic crosstabs on properly preaggregated data using something like SSRS is usually a much better way to go, there are cases where you need be able to do a dynamic crosstab in T-SQL (usually bad DB design or "special" requirements). Although T-SQL isn't usually the place to do such a thing, you'd better know how to do it so you don't have to tell your boss that you can't do it without getting another chunk of software involved. T-SQL does have more than just data storage capabilities in the form of DML (Data [i]Manipulation [/i]Language) and it's frequently much more effective than relegating such tasks to the "outside" world.Fri, 25 Jun 2010 08:01:16 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic696297-1226-1.aspx[quote][b]mlabedz (6/25/2010)[/b][hr]I think a few early posters got up on the wrong side of the bed this AM.[/quote]At 35 pages by 10am GMT, I think that qualifies as Understatement Of The Year(tm) :*)Perhaps a way to thank all the people who weren't too rude...[code="sql"] DECLARE @ThanksTo nvarchar(512) DECLARE @numPosts int DECLARE C AS CURSOR LOCAL FAST_FORWARD FOR SELECT [Author_Email] FROM [dbo].[ArticleDiscussionComments] WITH (NOLOCK) WHERE [dbo].[fn_isNotDisparaging]([BodyText]) AND [dbo].[fn_seemToThinkCursorsAreAllBad]([BodyText]) AND replyDate IS NULL; OPEN CBEGIN TRY FETCH NEXT FROM C INTO @ThanksTo WHILE @@FETCH_STATUS = 0 BEGIN SELECT @numPosts = COUNT(*) FROM Article WHERE Author_Email = @ThanksTo SET @thanks = ' I see you''ve posted ' + LTRIM(RTRIM(STR(@numPosts))) + ' times before, ' IF (@numPosts > 100) SET @thanks = @Thanks + 'I hope I can change your mind. Thank you.' ELSE SET @thanks = @Thanks +'I hope my posts are instructive, young padawan. Thank you.' EXEC msdb.dbo.sp_send_dbmail @recipients=@Author, @subject = 'Thanks', @body = @Thanks; FETCH NEXT FROM C INTO @ThanksTo END UPDATE ArticleDiscussionComments WITH (NOLOCK) SET ReplyDate = getdate() WHERE dbo.fn_isNotDisparaging([BodyText]) AND [dbo].[fn_seemToThinkCursorsAreAllBad]([BodyText]) AND replyDate IS NULL;END TRYBEGIN CATCH exec sp_logError('An error has occurred replying to the nice people'); exec sp_logError(ERROR_MESSAGE())END CATCHCLOSE CDEALLOCATE C[/code]Fri, 25 Jun 2010 08:00:36 GMTNick W*http://www.sqlservercentral.com/Forums/Topic696297-1226-1.aspxHi,I come from an RPG/DB2 background so I am sort of used to looping through records in order to do something, but I haven't actually used a cursor yet. Does using a Script Transformation in SSIS count? Does someone have an example of a problem that could only be solved with a cursor? Perhaps Mr. Neufeld can provide one?I have to agree that an example of an improper usage of a cursor would have been nice, even if the resolution was put into part 2. Especially since the provided example doesn't do anything but count rows. Would someone with any SQL knowledge at all write something like that? (umm, never mind - I just read part 2 and it seems that some folks might; one of the examples there uses a cursor for a simple concatenation and the author tells us that it was based on an actual code sample - that one would have been a better homework example, though)Oh, By the way - Select COUNT(1) * COUNT(2) From master.sys.columnsFri, 25 Jun 2010 07:55:03 GMTdcradayhttp://www.sqlservercentral.com/Forums/Topic696297-1226-1.aspx[quote][b]Thomas-282729 (6/25/2010)[/b][hr][quote][b]Nick Walton (6/25/2010)[/b][hr]I'm really looking forward to this series, as there's been a higher than average number of "Cursors are baaaaaad" posts on SSC recently. Your article seems to indicate that cursors can be eliminated entirely....I'm really looking for a solution to the following:-1. How do I execute a stored procedure for each row in a table (temporary or otherwise), which does some very complex calculations (using other tables) and then updates (or inserts if it doesn't already exist) a row in another table (a common operation for month-end tasks, for example), or if spooling out to external consumers, such as Broker peers?I don't believe SQL Server 2005 allows a) an sp to be executed per-row or b) a function to update a table...Sadly, logistics and transfer speeds make the option of external processing not possible. I hope that one of your articles covers this situation, as I'd love to get rid of all these cursors.[/quote]There is an answer, but you probably won't like it: don't do it in a stored procedure. If you cannot accomplish a given piece of work in a set-based format, it was probably not meant to be done in a database. Stored procedures were not meant to encapsulate complex business logic of the type you suggest. Databases are first and foremost meant to serve up data. Further, if there is so much data that streaming it to an external service is impractical, then I would suggest that it is also impractical to do it row-by-row in a stored proc. If you can do it row-by-row in a proc, then you can do it row-by-row in an external service.[/quote]That's also where a "control loop" comes into play and "control loops" are one of the very few places where a CURSOR or WHILE LOOP are generally ok to use.As for not using stored procedures to execute complex business logic goes, I guess I'll just have to agree to disagree with you on that. It's usually much more effective to process the data at the source than to stream millions of rows over the pipe.Fri, 25 Jun 2010 07:53:01 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic696297-1226-1.aspx[quote][b]Michael Jenck (6/25/2010)[/b][hr]I have been trying to figure out a way to eliminate the following cursor. I figure this would be a good place to post this seeing the title of this article :-).The only conditions are that I don't know in advance what the child and parent records are - just that at least 1 of each exists.(Note: I'm not a DBA. I fall in the category of knowing just enough SQL to be dangerous.)<snip>[/quote]When you hit a problem in T-SQL that requires a cumbersome solution, you should sit back and consider whether it ought to be done in T-SQL in the first place. The SQL language is fundamentally not designed for dynamic column generation. Further, T-SQL was not designed for fancy string manipulation. So, the solution to eliminating a cursor in this case is to not do dynamic crosstabs in T-SQL. Build your query in a middle-tier component or reporting tool. Is it [i]possible[/i] to do it in T-SQL? Yes, just like it is possible to use Excel as a database or to write memos. That does not mean you should use it for this purpose just as you shouldn't use T-SQL for dynamic crosstabs.Fri, 25 Jun 2010 07:46:16 GMTThomas-282729http://www.sqlservercentral.com/Forums/Topic696297-1226-1.aspx[quote][b]Nick Walton (6/25/2010)[/b][hr]I'm really looking forward to this series, as there's been a higher than average number of "Cursors are baaaaaad" posts on SSC recently. Your article seems to indicate that cursors can be eliminated entirely....I'm really looking for a solution to the following:-1. How do I execute a stored procedure for each row in a table (temporary or otherwise), which does some very complex calculations (using other tables) and then updates (or inserts if it doesn't already exist) a row in another table (a common operation for month-end tasks, for example), or if spooling out to external consumers, such as Broker peers?I don't believe SQL Server 2005 allows a) an sp to be executed per-row or b) a function to update a table...Sadly, logistics and transfer speeds make the option of external processing not possible. I hope that one of your articles covers this situation, as I'd love to get rid of all these cursors.[/quote]There is an answer, but you probably won't like it: don't do it in a stored procedure. If you cannot accomplish a given piece of work in a set-based format, it was probably not meant to be done in a database. Stored procedures were not meant to encapsulate complex business logic of the type you suggest. Databases are first and foremost meant to serve up data. Further, if there is so much data that streaming it to an external service is impractical, then I would suggest that it is also impractical to do it row-by-row in a stored proc. If you can do it row-by-row in a proc, then you can do it row-by-row in an external service.Fri, 25 Jun 2010 07:38:26 GMTThomas-282729http://www.sqlservercentral.com/Forums/Topic696297-1226-1.aspx[quote][b]Michael Jenck (6/25/2010)[/b][hr]I have been trying to figure out a way to eliminate the following cursor. I figure this would be a good place to post this seeing the title of this article :-).The only conditions are that I don't know in advance what the child and parent records are - just that at least 1 of each exists.(Note: I'm not a DBA. I fall in the category of knowing just enough SQL to be dangerous.)[code="sql"]IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Temp]') AND type in (N'U'))DROP TABLE [dbo].[Temp]GOIF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Temp2]') AND type in (N'U'))DROP TABLE [dbo].[Temp2]GO/****** Object: Table [dbo].[Temp] Script Date: 10/28/2009 08:20:47 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[Temp]( [ObjectId] [int] NOT NULL, [ObjectName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]INSERT INTO [dbo].[Temp] ([ObjectId], [ObjectName])SELECT 1, 'Parent 1' UNION ALLSELECT 2, 'Parent 2' UNION ALLSELECT 3, 'Parent 3' UNION ALLSELECT 4, 'Parent 4' CREATE TABLE [dbo].[Temp2]( [ObjectId] [int] NOT NULL, [ObjectName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [ParentId] [int] NOT NULL, [ChildId] [int] NOT NULL) ON [PRIMARY]INSERT INTO [dbo].[Temp2] ([ObjectId], [ObjectName], [ParentId], [ChildId])SELECT 1, 'Option 1',1, 10 UNION ALLSELECT 1, 'Option 1',2, 20 UNION ALLSELECT 1, 'Option 1',3, 30 UNION ALLSELECT 1, 'Option 1',4, 40 UNION ALLSELECT 1, 'Option 2',1, 11 UNION ALLSELECT 1, 'Option 2',2, 21 UNION ALLSELECT 1, 'Option 2',3, 31 UNION ALLSELECT 1, 'Option 2',4, 41 -- FROM: http://www.sqlservercentral.com/articles/T-SQL/63681/SELECT ObjectId, ObjectName, --(4) [1] AS [Parent 1], --(3) [2] AS [Parent 2], [3] AS [Parent 3], [4] AS [Parent 4] FROM (SELECT ObjectId, ObjectName, ParentId, ChildId FROM dbo.Temp2 WHERE (ObjectId = 1)) AS src --(1) PIVOT (Min(ChildId) FOR ParentId IN ([1],[2],[3],[4])) AS pvt --(2) ORDER BY ObjectNameDECLARE @ObjectId intDECLARE @ObjectName varchar(25)DECLARE @NewColumnName varchar(8000)DECLARE @NewColumnValue varchar(8000)DECLARE @NewSQL varchar(8000)DECLARE Object_cursor CURSOR FOR SELECT ObjectId, ObjectName FROM Temp FOR READ ONLY OPEN Object_cursorFETCH FROM Object_cursor INTO @ObjectId, @ObjectName WHILE @@FETCH_STATUS = 0 BEGIN IF(@NewColumnName IS NULL) BEGIN SET @NewColumnName = '[' + CAST(@ObjectId as varchar(5)) + '] AS [' + @ObjectName + ']' SET @NewColumnValue = '[' + CAST(@ObjectId as varchar(5)) + ']' END ELSE BEGIN SET @NewColumnName = @NewColumnName + ',[' + CAST(@ObjectId as varchar(5)) + '] AS [' + @ObjectName + ']' SET @NewColumnValue = @NewColumnValue + ',[' + CAST(@ObjectId as varchar(5)) + ']' END FETCH NEXT FROM Object_cursor INTO @ObjectId, @ObjectName ENDCLOSE Object_cursorDEALLOCATE Object_cursorSET @NewSQL = 'SELECT ObjectId, ObjectName, ' + @NewColumnName + ' FROM (SELECT ObjectId, ObjectName, ParentId, ChildId FROM dbo.Temp2 WHERE (ObjectId = 1)) AS src PIVOT (Min(ChildId) FOR ParentId IN (' + @NewColumnValue + ')) AS pvt ORDER BY ObjectName'EXEC (@NewSQL)[/code][/quote]You've already cited one article to get you started in your code. Here's part 2 which should help you get over the hump on such a dynamic crosstab...[url]http://www.sqlservercentral.com/articles/Crosstab/65048/[/url]Fri, 25 Jun 2010 07:38:14 GMTJeff Moden