Comparing cursor vs. WHILE loop

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.

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

FORWARD_ONLY
STATIC
KEYSET Defines 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.
DYNAMIC
FAST_FORWARD
READ_ONLY
SCROLL_LOCKS
OPTIMISTIC

Life Cycle of Cursor

Declare Cursor A cursor is declared by defining the SQL statement that returns a result set.
Open A Cursor is opened and populated by executing the SQL statement defined by the cursor.
Fetch When cursor is opened, rows can be fetched from the cursor one by one or in a block to do data manipulation.
Close After data manipulation, we should close the cursor explicitly.
Deallocate Finally, we need to delete the cursor definition and released all the system resources associated with the cursor.

SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
DECLARE @salary int
DECLARE cur_emp CURSOR
STATIC FOR 
    SELECT EmpID,EmpName,Salary from Employee
    OPEN cur_emp
    IF @@CURSOR_ROWS > 0
         BEGIN 
         FETCH NEXT FROM cur_emp INTO @Id,@name,@salary
         WHILE @@Fetch_status = 0
             BEGIN
                 PRINT 'ID : '+ convert(varchar(20),@Id)+', Name : '+@name+ ', Salary : '+convert(varchar(20),@salary)
                 FETCH NEXT FROM cur_emp INTO @Id,@name,@salary
             END
    END
CLOSE cur_emp
DEALLOCATE cur_emp
SET NOCOUNT OFF

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.

Comparing cursor vs. WHILE loop