SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Joe Celko / Stairway to Database Design Level 8: Cursors / Latest Posts

RE: Stairway to Database Design Level 8: Cursors

alistairgrieve — Sun, 24 Mar 2013 09:46:19 GMT

Is the article's description of the DECLARE CURSOR statement strictly accurate? I'm referring to the sentence that says: "The <query expression> is executed and the results are saved, so they can be sorted." According to MSDN, "DECLARE CURSOR defines the attributes of a Transact-SQL server cursor.... The OPEN statement populates the result set,...". So the query expression isn't actually executed until the OPEN statement is issued.

RE: Stairway to Database Design Level 8: Cursors

john.campbell-1020429 — Tue, 08 Nov 2011 12:51:59 GMT

On the one hand I agree that most cursors, especially those used inside 1 database at a time should probably be rewritten. Most reporting oriented cursors can be rewritten to use joins with a dramatic increase in speed. That being said, I find it hard to program some of my server wide queries and updates not using a cursor. Sp_msforeachdb is great for a lot of database manipulation jobs, but when you want to find a list of all of the tables, primary keys, check constraints, and secondary indexes on a server where the tablename is like '%_2011_%' and store the results in a table in order to generate the list for the next year, a cursor comes in very handy. Cursors are powerful tools that are still in our toolbox, but probably the most misused, especially the newer programmers, especially if they have experience with other, older programming languages.

RE: Stairway to Database Design Level 8: Cursors

patrickmcginnis59 — Thu, 03 Nov 2011 12:09:47 GMT

[quote][b]Cadavre (11/3/2011)[/b][hr][quote][b]patrickmcginnis59 (11/3/2011)[/b][hr]So one use of cursors I would consider would be running totals based on a date ordering of a result set. It seems to me that using the set oriented solutions I've would get progressively slower as the result set got larger. Are there set oriented solutions that wouldn't increase in execution time at more than a linear rate in proportion to the number of records in the set getting the running total calculated?I can't conceptually figure out how to accumulate and list a total for each record without constantly recalculating it against all the previous records preceding it in the ordered result set.[/quote][url=http://www.sqlservercentral.com/articles/T-SQL/68467/]Jeff's quirky update solves the running total issue[/url], and "Denali" offers new options.[/quote]That was an interesting read, but it sort of looks like it works by accident, would it work on my table without an index? I'm hoping Denali has some designed solutions.

RE: Stairway to Database Design Level 8: Cursors

rstone — Thu, 03 Nov 2011 11:58:05 GMT

A cursor does have its place because some things (e.g., DDL, procedure execution) can't be done in a set-based query. Imagine using a SELECT to execute a procedure to send email? Execution of T-SQL select and update statements stored in a column as part of a set-based query in a single transaction? Needing a cursor in or procedural code this case is probably a good thing. As to avoiding a cursor, any form of a procedural work around (e.g., WHILE loops) still misses the point. SQL Server can't optimize procedural code well - if at all. If you can specify the problem as a set-based query declaration, SQL server might still end up with a horrid execution plan. Even so, I bet it does a better job of it. How many of you will use multiple threads to process something procedurally? (However, if SQL is going parallel, the query probably has not been optimized.)With a reservation system, the problem often is that the record to be processed depends upon completing the processing of the prior record. This might seem to require a cursor or procedural code, but there are options to do it set based. Even so, I notice some will do everything with the cursor once a cursor is "required" - even if most of the problem can be solved via set-based steps without much thought.PS I have to look up the cursor syntax every time I use it. I think that's as it should be.

RE: Stairway to Database Design Level 8: Cursors

Cadavre — Thu, 03 Nov 2011 11:12:37 GMT

[quote][b]patrickmcginnis59 (11/3/2011)[/b][hr]So one use of cursors I would consider would be running totals based on a date ordering of a result set. It seems to me that using the set oriented solutions I've would get progressively slower as the result set got larger. Are there set oriented solutions that wouldn't increase in execution time at more than a linear rate in proportion to the number of records in the set getting the running total calculated?I can't conceptually figure out how to accumulate and list a total for each record without constantly recalculating it against all the previous records preceding it in the ordered result set.[/quote][url=http://www.sqlservercentral.com/articles/T-SQL/68467/]Jeff's quirky update solves the running total issue[/url], and "Denali" offers new options.

RE: Stairway to Database Design Level 8: Cursors

patrickmcginnis59 — Thu, 03 Nov 2011 11:05:45 GMT

So one use of cursors I would consider would be running totals based on a date ordering of a result set. It seems to me that using the set oriented solutions I've would get progressively slower as the result set got larger. Are there set oriented solutions that wouldn't increase in execution time at more than a linear rate in proportion to the number of records in the set getting the running total calculated?I can't conceptually figure out how to accumulate and list a total for each record without constantly recalculating it against all the previous records preceding it in the ordered result set.

RE: Stairway to Database Design Level 8: Cursors

patrickmcginnis59 — Thu, 03 Nov 2011 06:42:16 GMT

RE: Stairway to Database Design Level 8: Cursors

aurato — Thu, 03 Nov 2011 05:58:53 GMT

[quote][b]david.howell (11/2/2011)[/b][hr]It's quite disrespectful calling Mr Celko silly. "This is called an impedance mismatch, a borrowed term from electronics that refers to parallel versus serial data transmission."This is a correct statement, but probably just needs a comma after "electronics". In electronics yes its about power maximisation and avoiding reflections, but in computing it is certainly about the mismatch between relational and procedural ( which are themselves analogues for parallel and serial). On that Dinosaur languages comment, a quick search on wikipedia reveals"Lisp is the second-oldest high-level programming language in widespread use today; only Fortran is older (by one year)." Maybe it should be LISP.Joe Celko is a venerable statesman in the SQL community, he contributed to the SQL-89 and SQL-92 standards and has written and contributed to more than 8 books, not to mention this informative Stairway series. Attacking him on trite points and calling him silly just detracts from the point of all this. Newbies and Seasoned pros alike can benefit from stairway installments. They suit my short attention span perfectly. The comment about procedural languages providing an undercover cursor was something I'd never considered, but I suddenly recall all the csv / xls and other flat reports i've ever done that benefitted dramatically from not processing the dataset in the procedural language, and shifting the bulk work back to SQL Server.Where is Jeff Moden's comment in all this? If you still don't believe cursors are slow just ask him![/quote]I'm all about Jeff Moden and anti-RBAR. I just work with and have always worked with and been surrounded by people who consider OO programming to be greater than functional programming by default and it bothers me. The reason I left Lisp out of the dinosaur category is because it was basically so brilliant that it's become timeless. I have no issue with his SQL knowledge and I have no issue with cutting down on cursors. I just didn't like the implied outdated-ness of functional programming.But again, straying from topic. My company has me writing alllllllll of the business logic for a program that's going to be a huge part of our business cycle in SQL. And it's driving me nuts because I've had to write cursors for it in more than one place.

RE: Stairway to Database Design Level 8: Cursors

CELKO — Wed, 02 Nov 2011 21:30:53 GMT

[quote][b]arp 24442 (11/2/2011)[/b][hr]Hi all,I am all for new knowledge. I have come to SQL from a programmer background and still find it hard NOT to think sequentially through a set. Recently, I had to find the 25th, 50th (median) and 75th percentiles (or 1st, 2nd, 3rd quartiles) for a box and whisker plot. After much digging I found a post from a guy using a cursor to find the median and tweaked it to give me the other two stats.[/code][/quote]A to ne point decdes ago, the Median was the "SQL Proble,m du Jour"; Chris date published one answer in his column, I replied with another answer, and it bounced around the community for a few months. The last edition of SQL FOR SMARTIES has six ways. The approach I like today is to use SELECT xROW_NUMBER() OVER (ORDER BY x ASC) AS up_cnt,ROW_NUMBER() OVER (ORDER BY x DESC) AS dn_cnt FROM Foobar;the look for up_cnt IN ( dn_cnt, dn_cnt -1, dn_cnt +1)But you have to wqth out for duplicate values. lay with it see if you like it.

RE: Stairway to Database Design Level 8: Cursors

davoscollective — Wed, 02 Nov 2011 17:45:42 GMT

It's quite disrespectful calling Mr Celko silly. "This is called an impedance mismatch, a borrowed term from electronics that refers to parallel versus serial data transmission."This is a correct statement, but probably just needs a comma after "electronics". In electronics yes its about power maximisation and avoiding reflections, but in computing it is certainly about the mismatch between relational and procedural ( which are themselves analogues for parallel and serial). On that Dinosaur languages comment, a quick search on wikipedia reveals"Lisp is the second-oldest high-level programming language in widespread use today; only Fortran is older (by one year)." Maybe it should be LISP.Joe Celko is a venerable statesman in the SQL community, he contributed to the SQL-89 and SQL-92 standards and has written and contributed to more than 8 books, not to mention this informative Stairway series. Attacking him on trite points and calling him silly just detracts from the point of all this. Newbies and Seasoned pros alike can benefit from stairway installments. They suit my short attention span perfectly. The comment about procedural languages providing an undercover cursor was something I'd never considered, but I suddenly recall all the csv / xls and other flat reports i've ever done that benefitted dramatically from not processing the dataset in the procedural language, and shifting the bulk work back to SQL Server.Where is Jeff Moden's comment in all this? If you still don't believe cursors are slow just ask him!

RE: Stairway to Database Design Level 8: Cursors

x102dscaper — Wed, 02 Nov 2011 13:30:20 GMT

One instance that springs to mind where cursors actually out-perform while key > last_key loops is once you get involved in Sync Framework and start using timestamps. Set-based doesn't handle timestamps all that well when you rely on your information being indexed and batched according to them. Rowversion also behaves in a similar way and certainly isn't suitable for use with Sync Framework.The problem defines the solution - so don't be afraid to use all the tools you have at hand. Saying you should only use cursors 5 times during your career is like telling a mechanic they can't use a particular spanner more than 5 times - chances are it'll be the only spanner that'll work on that 6th engine.

RE: Stairway to Database Design Level 8: Cursors

HighTechAngel — Wed, 02 Nov 2011 10:32:06 GMT

The advice about not writing more than five cursors in one's career does not make any sense. I think that a cursor is the perfect solution for certain scenarios and those scenarios may arise anytime in our careers.

RE: Stairway to Database Design Level 8: Cursors

karli — Wed, 02 Nov 2011 09:49:21 GMT

arp 24442,Finding the Median depends on the ordering of of the data you're processing. SQL server 2005's ROW_NUMBER() function allows your to easily calculate a row's position in an ordered set.Your code might look something like:[code="sql"]DECLARE @MedianValue as decimalDECLARE @rowCount as intSELECT @rowCount = COUNT(*) FROM dataTableSELECT @MedianValue = dataValueFROM( SELECT dataValue ,ROW_NUMBER() OVER (ORDER BY dataValue) as rowNumber FROM dataTable) as tWHERE t.rowNumber = @rowCount / 2 + 1[/code]In the code above you first get the total number of rows in your set. Then you query for the n/2+1th Item (rowNumber always begins counting at 1). Note that I had to use a subquery in order to easily apply a WHERE clause to the row numbering. We're also assuming that the row Count is odd. If it's even, Wikipedia says that the [url=http://en.wikipedia.org/wiki/Median]Median[/url] for even-numbered sets is defined as the mean of the middle two items. That means that you find the [highlight]@rowCount/2[/highlight] and [highlight]@rowCount/2+1[/highlight] items and average them out.To find the 25th and 75th percentile items, adjust your formula to find items 1/4 and 3/4 of the way down the list.If you're stuck using SQL server 2000, there's a nifty way of using [highlight]SET RowCount[/highlight] and variable assignments to get the exact rows that you need. [url=http://www.4guysfromrolla.com/webtech/042606-1.shtml]This article[/url]explains how to do it with Paging but the method can be used for finding the median as well.Hope this helps![b]Update:[/b]I just realized that there may be the case where the data is partitioned by some other value or values. This complicates the solution but it can still be done with SQL server 2005:[code="sql"]DECLARE @MedianValue as decimalDECLARE @rowCount as intSELECT @rowCount = COUNT(*) FROM dataTableSELECT dataValue as Median ,groupingValueFROM( -- count of values partitioned by grouping Value SELECT count(dataValue) / 2 as medianLocation ,groupingValue FROM dataTable GROUP BY groupingValue) as countTableINNER JOIN( -- row counts table partitioned by grouping Value SELECT dataValue ,ROW_NUMBER() OVER (ORDER BY dataValue PARTITION BY groupingValue) as rowNumber ,groupingValue FROM dataTable) as rowTable ON countTable.medianLocation = rowTable.rowNumber AND countTable.groupingValue = rowTable.groupingValue[/code]The logic is fairly straightforward: the first subquery calculates the location of the median per grouped value. The second query calculates the row number for each set of values based on the grouped value. All we then do is join the two tables together so we can get the datavalue (median) for each grouping Value. A good basic introduction to Row_Number() and partitioning can be found [url=http://www.sqlservercentral.com/articles/t-sql/70264/]here[/url].The query above will not be the fastest thing on the planet but I'm willing to bet it would still be faster than using a cursor. Unfortunately, I don't think there's an SQL server 2000 equivalent.

RE: Stairway to Database Design Level 8: Cursors

patrickmcginnis59 — Wed, 02 Nov 2011 08:13:20 GMT

[quote][b]aurato (11/2/2011)[/b][hr][quote][b]patrickmcginnis59 (11/2/2011)[/b][hr]"This is called an impedance mismatch, a borrowed term from electronics that refers to parallel versus serial data transmission."Mr. Celko shouldn't discuss topics he doesn't know about because he ends up looking pretty silly when he does.[/quote]This is kind of off-topic, but I raged a little inside when the only examples of procedural languages he came up with were COBOL, BASIC, and FORTRAN. As if functional programming consists of nothing but useless old dinosaurs whose names are written in scary giant letters.Lisp, anyone?[/quote]SQL and RDBMS technologies solve a specific class of problems well and most database professionals realise this without needing to believe for instance that procedural languages are based on a "sequential file model," and Mr. Celko just looks a bit silly writing stuff like that. But I doubt he's going to change because of any criticism appearing on a web forum, so I'm not going to sweat it.

RE: Stairway to Database Design Level 8: Cursors

aurato — Wed, 02 Nov 2011 07:36:45 GMT

[quote][b]patrickmcginnis59 (11/2/2011)[/b][hr]"This is called an impedance mismatch, a borrowed term from electronics that refers to parallel versus serial data transmission."Mr. Celko shouldn't discuss topics he doesn't know about because he ends up looking pretty silly when he does.[/quote]This is kind of off-topic, but I raged a little inside when the only examples of procedural languages he came up with were COBOL, BASIC, and FORTRAN. As if functional programming consists of nothing but useless old dinosaurs whose names are written in scary giant letters.Lisp, anyone?

RE: Stairway to Database Design Level 8: Cursors

99zardoz — Wed, 02 Nov 2011 06:52:44 GMT

I agree that using a cursor to avoid joining 2 tables is obviously wrong, and if a report takes 45 seconds because a cursor is used I too not be looking to use a cursor for that report.But these are rather extreme examples. Cursors have their place as do set based operations.

RE: Stairway to Database Design Level 8: Cursors

patrickmcginnis59 — Wed, 02 Nov 2011 06:51:50 GMT

"This is called an impedance mismatch, a borrowed term from electronics that refers to parallel versus serial data transmission."Mr. Celko shouldn't discuss topics he doesn't know about because he ends up looking pretty silly when he does.

RE: Stairway to Database Design Level 8: Cursors

arp 24442 — Wed, 02 Nov 2011 06:34:03 GMT

Hi all,I am all for new knowledge. I have come to SQL from a programmer background and still find it hard NOT to think sequentially through a set. Recently, I had to find the 25th, 50th (median) and 75th percentiles (or 1st, 2nd, 3rd quartiles) for a box and whisker plot. After much digging I found a post from a guy using a cursor to find the median and tweaked it to give me the other two stats.Very happy to hear of a more nifty way to do this. My code works nicely, and the performance is OK, but as I said, always happy to learn.[code="sql"]--Holding table for quartiles rowsCREATE TABLE #ExamStats (sub_code VARCHAR(4), Q0 decimal(5,2), Q1 decimal(5,2), Q2 decimal(5,2), Q3 decimal(5,2), Q4 decimal(5,2), mean decimal(5,2), NumRows int)CREATE TABLE #ExamResults ( ID int IDENTITY(1,1) , result decimal(5,2))DECLARE @subjcode varchar(4), @q2 decimal(5,2) , @mid decimal(5,2), @odd int, @mid1 decimal(5,2), @mid2 decimal(5,2) , @NumRows int DECLARE @q1max int, @q1 decimal(5,2), @q3 decimal(5,2), @q3min intDECLARE @q0 decimal(5,2), @q4 decimal(5,2), @mean decimal(5,2) DECLARE oCursor CURSOR FORSELECT distinct sub_codeFROM studresultWHERE (cmpy_code = '01') AND (res_year = '2011') AND (res_period = '3') and (obj_code like 'SE%%') and ISNUMERIC(stud_result)= 1 --AND (sub_code = '8041') ORDER BY sub_code asc --Open said cursor and start stepping thru itOPEN oCursorWHILE 0=0 BEGIN FETCH NEXT FROM oCursor INTO @subjcode IF @@Fetch_Status<>0 BREAK TRUNCATE TABLE #ExamResults --Insert the data for a given age INSERT INTO #ExamResults (result) SELECT stud_result FROM studresult WHERE (cmpy_code = '01') AND (res_year = '2011') AND (res_period = '3') and (obj_code like 'SE%%') and ISNUMERIC(stud_result)= 1 AND (sub_code = @subjcode) ORDER BY stud_result asc --Now actually compute the median SET @NumRows = @@ROWCOUNT SET @odd = @NumRows % 2 --1 if odd number of recs, 0 if even SET @mid = @NumRows / 2 --Number of the "middle" record IF @NumRows = 1 BEGIN --Special trick so this works if only one record SET @odd = 1 SET @mid = 1 END -- Which row(s) hold the median value? IF @odd = 1 BEGIN --Odd number of rows - median is the middle one SET @q2 = (SELECT result FROM #ExamResults WHERE ID = @mid) --To Reset Midpoints SET @q1max = @mid - 1 SET @q3min = @mid + 1 END ELSE BEGIN --Even number of rows - median is the average of the two middle ones SET @mid1 = (SELECT result FROM #ExamResults WHERE ID = @mid) SET @mid2 = (SELECT result FROM #ExamResults WHERE ID = @mid + 1) SET @q2 = (@mid1 + @mid2) / 2 --To Reset Midpoints SET @q1max = @mid SET @q3min = @mid + 1 END --Q1 SET @odd = @q1max % 2 SET @mid = @q1max / 2 if @q1max = 1 BEGIN SET @odd = 1 SET @mid = 1 END IF @odd = 1 BEGIN SET @q1 = (SELECT result FROM #ExamResults WHERE ID = @mid) END ELSE BEGIN SET @mid1 = (SELECT result FROM #ExamResults WHERE ID = @mid) SET @mid2 = (SELECT result FROM #ExamResults WHERE ID = @mid + 1) SET @q1 = (@mid1 + @mid2) / 2 END --Q3 SET @odd = (@NumRows-@q3min) % 2 SET @mid = ((@NumRows-@q3min) / 2) + @q3min if (@NumRows-@q3min) = 1 BEGIN SET @odd = 1 SET @mid = 1 END IF @odd = 1 BEGIN SET @q3 = (SELECT result FROM #ExamResults WHERE ID = @mid) END ELSE BEGIN SET @mid1 = (SELECT result FROM #ExamResults WHERE ID = @mid) SET @mid2 = (SELECT result FROM #ExamResults WHERE ID = @mid + 1) SET @q3 = (@mid1 + @mid2) / 2 END SET @q0 = (SELECT MIN(result) FROM #ExamResults) SET @q4 = (SELECT MAX(result) FROM #ExamResults) SET @mean = (SELECT AVG(result) FROM #ExamResults) INSERT into #ExamStats values(@subjcode, @q0, @q1, @q2, @q3, @q4, @mean, @NumRows)ENDCLOSE oCursor DEALLOCATE oCursorSELECT student.stud_code, student.surname, student.given_name, studresult.res_year, studresult.res_period, student.year_grp, student.house, student.form_cls, student.pctut_grp, subtab.sub_long, studresult.sub_code, studresult.stud_result, #ExamStats.Q0, #ExamStats.Q1, #ExamStats.Q2, #ExamStats.Q3, #ExamStats.Q4, #ExamStats.mean, #ExamStats.NumRowsFROM student INNER JOIN studresult ON student.stud_code = studresult.stud_code AND student.cmpy_code = studresult.cmpy_code INNER JOIN #ExamStats ON studresult.sub_code = #ExamStats.sub_code INNER JOIN subtab on subtab.cmpy_code = student.cmpy_code and subtab.sub_code = studresult.sub_codeWHERE studresult.res_year = '2011' and studresult.res_period = 3 and (obj_code like 'SE%%') and student.cmpy_code = '01' ORDER BY studresult.res_year, studresult.res_period, studresult.sub_code, student.stud_code DROP TABLE #ExamStatsDROP TABLE #ExamResults[/code]

RE: Stairway to Database Design Level 8: Cursors

GPO — Wed, 02 Nov 2011 05:48:17 GMT

I started in current job about 18 months ago bringing with me a nascent aversion for cursors and while loops. I was instructed in the first week to write something with a cursor; something that really just needed a join to a calendar table. The rationale was that a cursor would only take "a few" more seconds to run and was a lot easier to read. I secretly disobeyed this instruction and generally have done ever since.There has almost always been a faster set-based solution. Having said that, we do now have the luxury of working with versions of SQL Server that are newer than SQL 2000, and I think some of the improvements to later versions of SQL have made it far easier to avoid cursors and loops. It's therefore a bit rich for me to sneer down my patrician nose at the code that I've inherited. Much of it was written ten or a dozen years ago. It seems to me the SQL community's philosophy and collective knowledge in relation to cursors and their alternatives has changed a lot in that time. What I can't get my head around though, is the belief that cursors are easier to read. Maybe I write too much SQL and not enough procedural code (that is to say, roughly none) but I find set-based TSQL waaaaay easier to read than cursor-based TSQL. As for "only a few more seconds", sorry, but I've had to stand beside busy company directors while they've waited 45 seconds for a report that should have only taken 5 seconds to run. It's excruciating. Do that a few times and you'll find a way to avoid that cursor!

RE: Stairway to Database Design Level 8: Cursors

Joffrey — Wed, 02 Nov 2011 04:30:18 GMT

Once you get your head around using set based SQL you'll hardly use cursors. It's nice to have this option but I would use a while loop/variables if I really need to loop row by row (itch, itch , scratch)Nice article nevertheless.

RE: Stairway to Database Design Level 8: Cursors

99zardoz — Wed, 02 Nov 2011 04:03:29 GMT

Nothing wrong with the article itself but I think it needs to be acknowledged that there are some things that are most easily done with cursors. As long as the performance is OK, whats the problem? Its about delivering robust functionality to users in the minimum time possible.I try and do everything in the simplest and most robust way possible. By simplest, that means understandable by people who may be less familiar with T-SQL than me and without huge statements and unnecessary code written simply to conform to a somewhat arbitrary design guideline. If I think the best way is set-based I'll do it, and the same for row-based.Free thinking is important as is the confidence to step outside recieved opinion when that gets better results quicker.Dont mean to be too negative as this has been a valuable series of articles.

RE: Stairway to Database Design Level 8: Cursors

john barnett — Wed, 02 Nov 2011 03:40:37 GMT

How about - using while loops and variables to avoid cursor overhead but still handle row based processing?If you can tell me how to do database mail for producing emails to a set of customers (based on data in SQL server) without using row by row processing (either cursors or while loops) that would be very helpful.

Stairway to Database Design Level 8: Cursors

CELKO — Tue, 09 Aug 2011 04:16:35 GMT

Comments posted to this topic are about the item [B]Stairway to Database Design Level 8: Cursors[/B]