Posted by Hugo Kornelis on 02/01/06 23:41

On 31 Jan 2006 17:43:22 -0800, Doug wrote:

>>We are set based. And a set in a relational database is a collection of
>rows with no implied order.
>
>>A cursor is not a set - it is an operation to turn a set into a series
>of rows, with implied order.
>
>A cursor does NOT require an order. Further, a cursor does NOT require
>an implied order. I believe we agree on the above two statements?

Hi Doug,

Fully on the first; partially on the second.

I can agree that a cursor doesn't *require* an implied order, but I'll
hasten to add that this is because the cursor itself will imply an order
on the set that is defined for the cursor.

In other words: using a cursor will result in an ordering of the result
set - either explicitly specified in an ORDER BY clause, or implicitly
determined by the DBMS during execution of the cursor.

>
>If so, then perhaps another description of a cursor might be "an
>operation or contstruct that allows the traversing of a set." You use
>a cursor when you need to conduct complex logic on each member of a
>set. Normally, you start at one end, examine and perform logic on each
>member. Then, you proceed to the next member.
>
>The very first member can be thought of as the "first." You might then
>proceed to the "next." You usually end up with the "last."
>
>No where in my usage did I define order. Typically, I look at each
>member of the set once.

This all makes sense. And in such a case, I agree that there is no need
to add an ORDER BY clause - in fact, adding one might harm performance
without any gain.

But this is only true if you have no logic that operates on both the
"current" row and some "previous" row, or that shortcuts execution of
the cursor for the "remaining" rows once a specific state is found. As
soon as you add some of that logic, you will also have to add an ORDER
BY clause to your cursor to make sure that the logic is reproducable
and independent of external factors that might induce a new execution
plan.

>
>I never have used cursors a lot, but sometimes the business logic, or
>program logic, really makes a cursor useful.

The situations are very rare, but indeed: they do exist.

--
Hugo Kornelis, SQL Server MVP

[Back to original message]