en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP

 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize

Recent Entries

Minimize
אוק28

Written by: ronen ariely
28/10/2013 16:44 RssIcon

Background

I see many questions about the difference between Cursor and While Loop in SQL queries. I think this is a fundamental mistake to compare them at all. It's like comparing a car and a boat. We use the car moving on land, and we use a boat to travel at sea. I would not recommend anyone to try the opposite. So what's so confusing?

Let's try to have a brief discussion about "Cursors and Loops" in general and the replacing between them (yes we can car to travel at sea and a boat to move on land).

Some points to think about:

Cursor do not use for looping, but to "point to" or "hold" an element from data set. We should think about cursor as a "pointer". It usually use for iterating through a set of element (records). Most of time when we use cursor we do need to loop through set of element, and for that we can use while loop.To scroll through the dataset dynamically you need to use loop. If you have million records you want to scroll through without a loop, then you need to write million time "FETCH NEXT". This will be very long script.

We usually use a while loop when we use cursor in order to loop the operation of "FETCH NEXT"A cursor is an actual object inside the database engine. That is why we need to use DEALLOCATE in order to Removes a cursor reference.Cursors are flexible and the type used are very important in terms of performance.

* FORWARD_ONLY, STATIC, KEYSET, DYNAMIC, FAST_FORWARD,READ_ONLY,SCROLL_LOCKS,OPTIMISTICCursor have a relation with other object like tables that declares during the declaration of the cursor (by using "for").

Cursor cost CPU, memory, blocking and concurrency. In OLTP system don't base your choice only on "logical reads" (can be much lower using cursor sometime).A WHILE loop is a programming concept, no objects must be created.

In summary

WHILE loops (without cursor) don't provide some of the bells and whistles that come with cursors, such as the ability to easily go "backward" in the result set.Operating on a recordset might fit for use a cursor. Repeating an operation while some condition fit a while loop.

If I can avoid using either I will :-)

Hope this was useful
Have fun.