jwcolby
jwcolby at colbyconsulting.com
Fri Sep 24 05:52:59 CDT 2010
By none other than Andy Warren of SQL Server Central http://www.sqlservercentral.com/articles/Advanced+Querying/2785/ No mention of Servers around the world blue screening from instabilities. Arrghhh... my server just shut down... *(&^%@%$^ I knew I shouldn't have used that cursor! ;) John W. Colby www.ColbyConsulting.com On 9/23/2010 8:16 PM, Asger Blond wrote: > > John, >> *with no explanation at all* > And from your previous posting: >> I don't have the time to spend hours figuring out something that I could >> learn in 5 minutes if you were kind enough to explain > > Excuse me, but I made a typo in my last reply to you. > The last row in the testing recordset should of course not be: > 222222 29/09/2010 XX2 2XX YY2 1YY > It should be: > 222222 29/09/2010 XX2 2XX YY2 2YY > > Did you get my point? We all have work to do and don't want to spend hours making long explanations and then proof-reading our explanations which sure will have pleanty of slpelling flauts. > > Asger > > -----Oprindelig meddelelse----- > Fra: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af jwcolby > Sendt: 24. september 2010 01:20 > Til: Discussion concerning MS SQL Server > Emne: Re: [dba-SQLServer] SQL Server 2005 - Cursors Or Not Cursors > > 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 >> >> > _______________________________________________ > 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 >