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

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
>
>



More information about the dba-SQLServer mailing list