jwcolby
jwcolby at colbyconsulting.com
Thu Sep 23 18:19:36 CDT 2010
LOL, Francisco you crack me up sometimes. From the SQL Server Team... (and YOU) http://www.sqlteam.com/article/cursors-an-overview Where in there does it say anything about unstable or unresponsive servers. In the real world (yours too I assume) we often need to just get it done and move on. Throwing out a solution *with no explanation at all* that is obviously classy, but a bit dense, to someone asking if a cursor is OK, is... less than helpful. I understand from past experiences with this list that there is a very definite "read BOL" mentality. If it don't hurt, it ain't good for ya. ;) So Francisco, I will pretty much make whatever damned recommendations I want. Including "use a cursor when it fits" which is what I said originally. It's a tool for God's sake. Provided by Microsoft for God's sake. Discussed in a blog by none other than SQLTeam.com for God's sake. If you have a problem with them, go bitch at somebody over there. John W. Colby www.ColbyConsulting.com On 9/23/2010 4:58 PM, Francisco Tapia wrote: > John, > in your environment and because you are a single user, your experiences > are more unique than most people who work with SQL Server, you can make > decisions to use cursors on a whim that will impact your server performance, > however, most other SQL programmers and DBA's generally need to address > server performance and availability. Because of that I recommend that you > (John) continue to use the tool of your choice, however, when offering > advice on cursors, would refrain from stating when a cursor is valid. I > would hate to have someone's server crash or become unresponsive when it > deals with a community of users. > > it's also quite another thing to state that information wasn't provided, > Asger, clearly stated that his example was a subquery example with common > table expressions (CTE) and Rank function, all terms that can be quickly be > researched at on Books online (BOL). > > > > -Francisco > http://bit.ly/sqlthis | Tsql and More... > > > > > On Thu, Sep 23, 2010 at 11:53 AM, jwcolby<jwcolby at colbyconsulting.com>wrote: > >> OK Asger (and Francisco), but this is a perfect example of... WTF over? >> >> I for one haven't a clue what this code does, nor do I have a clue how to >> figure out what this code >> does. >> >> If you are going to recommend code like this you need to tell us how it >> works, otherwise I for one >> will go "ok, now where did I put that cursor..." >> >> >> >> John W. Colby >> www.ColbyConsulting.com >> >> On 9/23/2010 12:13 PM, Asger Blond wrote: >>> Paul, >>> You can use a subquery to make this update. Following example uses a >> Common Table Expression and the Rank function Row_Number(): >>> >>> WITH cte (RowNumber, StartCode, EndCode) AS >>> (SELECT ROW_NUMBER() OVER (ORDER BY PersonID), StartCode, EndCode FROM >> YourQuery) >>> UPDATE cte SET StartCode = >>> (SELECT EndCode FROM cte AS cte2 >>> WHERE cte.RowNumber> 1 AND cte.RowNumber = cte2.RowNumber+1) >>> >>> Asger >>> >>> -----Oprindelig meddelelse----- >>> Fra: dba-sqlserver-bounces at databaseadvisors.com [mailto: >> dba-sqlserver-bounces at databaseadvisors.com] På vegne af Paul Hartland >>> Sendt: 23. september 2010 16:53 >>> Til: SQLServerList >>> Emne: [dba-SQLServer] SQL Server 2005 - Cursors Or Not Cursors >>> >>> To all, >>> >>> We have employees that can do more than one job in a day on our database, >>> when an employee is assigned to a job, the application takes the persons >>> home postcode and the jobs location postcode and stores them in a table, >> we >>> are now creating an estimated mileage. I can do this based on the >> postcodes >>> supplied but it will be inaccurate as the start postcodes will always be >> the >>> employees home, when the start postcode for the second job should really >> be >>> the postcode of the previous job location. >>> >>> At the moment I have a select query which returns results for an employee >>> where they are doing more than one job in a day, result example below: >>> >>> PersonID Date StartCode EndCode >>> 111111 29/09/2010 AA1 1AA BB1 1BB >>> 111111 29/09/2010 AA2 2AA BB2 2BB >>> 111111 29/09/2010 AA3 3AA BB3 3BB >>> 111111 29/09/2010 AA4 4AA BB4 4BB >>> >>> I am thinking of putting these results into a cursor and looping through >> the >>> records updating the startcode with the previous records endcode, so that >>> AA2 2AA will become BB1 1BB, AA3 3AA will become BB2 2BB etc >>> >>> Is this the best way to do this, or could someone point me in a better >>> direction please. >>> >>> Many thanks in advance for all your help. >>> >> _______________________________________________ >> dba-SQLServer mailing list >> dba-SQLServer at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >> http://www.databaseadvisors.com >> >> > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >