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

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