[dba-SQLServer] SQL Server 2005 - Cursors Or Not Cursors

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
>



More information about the dba-SQLServer mailing list