Comparing cursor vs. WHILE loop performance

Cursors

Cursors are a looping construct built inside the database engine and come with a wide variety of features. Cursors allow you to fetch a set of data, loop through each record, and modify the values as necessary; then, you can easily assign these values to variables and perform processing on these values. Depending on the type of cursor you request, you can even fetch records that you’ve previously fetched.
Because a cursor is an actual object inside the database engine, there is a little overhead involved in creating the cursor and destroying it. Also, a majority of cursor operations occur in tempdb, so a heavily used tempdb will be even more overloaded with the use of cursors.
The types of cursors used are very important in terms of performance. Below is a list of the available cursor types as listed on

•     FORWARD_ONLY

  Specifies that the cursor can only be scrolled from the first to the last row. FETCH NEXT is the only supported fetch option. If FORWARD_ONLY is specified without the STATIC, KEYSET, or DYNAMIC keywords, the cursor operates as a DYNAMIC cursor. When neither FORWARD_ONLY nor SCROLL is specified, FORWARD_ONLY is the default, unless the keywords STATIC, KEYSET, or DYNAMIC are specified. STATIC, KEYSET, and DYNAMIC cursors default to SCROLL. Unlike database APIs such as ODBC and ADO, FORWARD_ONLY is supported with STATIC, KEYSET, and DYNAMIC Transact-SQL cursors.

• STATICDefines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications.

• KEYSETSpecifies that the membership and order of rows in the cursor are fixed when the cursor is opened. The set of keys that uniquely identify the rows is built into a table in tempdb known as the keyset. Changes to nonkey values in the base tables, either made by the cursor owner or committed by other users, are visible as the owner scrolls around the cursor. Inserts made by other users are not visible (inserts cannot be made through a Transact-SQL server cursor). If a row is deleted, an attempt to fetch the row returns an @@FETCH_STATUS of -2. Updates of key values from outside the cursor resemble a delete of the old row followed by an insert of the new row. The row with the new values is not visible, and attempts to fetch the row with the old values return an @@FETCH_STATUS of -2. The new values are visible if the update is done through the cursor by specifying the WHERE CURRENT OF clause.

• DYNAMICDefines a cursor that reflects all data changes made to the rows in its result set as you scroll around the cursor. The data values, order, and membership of the rows can change on each fetch. The ABSOLUTE fetch option is not supported with dynamic cursors.

• FAST_FORWARDSpecifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled. FAST_FORWARD cannot be specified if SCROLL or FOR_UPDATE is also specified.
• READ_ONLYPrevents updates made through this cursor. The cursor cannot be referenced in a WHERE CURRENT OF clause in an UPDATE or DELETE statement. This option overrides the default capability of a cursor to be updated.
• SCROLL_LOCKSSpecifies that positioned updates or deletes made through the cursor are guaranteed to succeed. SQL Server locks the rows as they are read into the cursor to ensure their availability for later modifications. SCROLL_LOCKS cannot be specified if FAST_FORWARD or STATIC is also specified.

• OPTIMISTICSpecifies that positioned updates or deletes made through the cursor do not succeed if the row has been updated since it was read into the cursor. SQL Server does not lock rows as they are read into the cursor. It instead uses comparisons of timestamp column values, or a checksum value if the table has no timestamp column, to determine whether the row was modified after it was read into the cursor. If the row was modified, the attempted positioned update or delete fails. OPTIMISTIC cannot be specified if FAST_FORWARD is also specified.

WHILE loops

A WHILE loop is a programming construct that you’re likely familiar with from other programming languages. You define a condition at the beginning of the loop, and iteration will occur so long as this condition remains to be true.
WHILE loops are as easy to use as a cursor, but sometimes these constructs are a little more difficult to read and/or to understand, as a query must occur on the base table(s) that must fetch a single next row. This type of operation is very tricky to do in situations where your underlying base table does not have a primary key column (which it should have).
WHILE loops 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. Although, I’ve never been in a situation in which I did anything other than a straight loop through a result set with a cursor.
One advantage of the WHILE loop is that no objects must be created in memory to facilitate the looping through a set of records as is necessary with a cursor.