Cursors vs Sets in SQL

1. Fetch the records (Telephone numbers) with pincode null from the table Customer.
2. Iterate to every fetched record and break the preceding 4 digits of the telephone number.
3. Find the respective pincode from Pincode_details using the number fetched in Step 2.
4. For every record, check if the variable @pincode is not null and update the pincode into the Customer table.

   DECLARE @telnumber char(8)  
   DECLARE cust_cursor CURSOR FOR                             //  
      SELECT TelNumber FROM Customer WHERE PinCode IS NULL    //  
   OPEN cust_cursor                                           //  
   FETCH NEXT FROM cust_cursor  INTO @telnumber               // (1)  
   WHILE @@FETCH_STATUS = 0 BEGIN  
      Declare @pincode char(6)  
      DECLARE @centerid char(4)  
      SELECT @centerid = LEFT(@telnumber, 4)                  // (2)  
     
      SELECT @pincode = PinCode                               //  
      FROM PinCode_Details                                    //  
      WHERE Centerid = @centerid                              // (3)  
     
      IF @pincode IS NOT NULL                                 //  
      BEGIN                                                   //  
          UPDATE Customer SET PinCode = @pinCode              //  
          WHERE CURRENT OF cust_cursor                        //  
      END                                                     // (4)  
      FETCH NEXT FROM cust_cursor INTO @telnumber  
   END  
   CLOSE cust_cursor   
   DEALLOCATE cust_cursor   

Solution using the Sets

A single update query with a join will achieve the same result.

UPDATE Customer   
SET PinCode = PinCode_Details.PinCode   
FROM Customer  
JOIN PinCode_Details ON  
    LEFT(Customer.PhoneNumber, 4) = PinCode_Details.Centerid  
WHERE  
    Customer.PinCode IS NULL  

Advantage of Sets over Cursor in the above example

1. Sets are recommended since there will be a noticeable improvement in the query results.
2. The code is easily readable and understandable.
3. There will be no network round trips.
4. The query can be optimized with indexing.

When can we use cursors?

Sets are only for use where the developer builds the query with prior knowledge of what the user will see or use.

1. Cursors can be used when the user is given an interface to group the data logically. Then, the developer would have no idea of what kind of grouping will be done by the user.
2. Or, as in the following example, if an event must be fired to update a table present in all the databases ('ClientProductionOne,' 'ClientProductionTwo,' 'ClientProductionThree'), then a cursor approach will help you.

   DECLARE @dbname VARCHAR(50)  
   DECLARE @databasename VARCHAR(256)  
   DECLARE @SQL varchar(8000)  
      
   SET @SQL = 'UPDATE  @dbname.dbo.tbldailyEventFired  
           SET EndTime = CONVERT(datetime,''2014-11-18 23:59:00'',120)   
           WHERE EndTime = (CONVERT(datetime,''2015-11-17 23:59:00'',120))'  
      
   DECLARE db_cursor CURSOR FOR   
       SELECT name   
       FROM master.dbo.sysdatabases  
       WHERE name IN ('ClientProductionOne','ClientProductionTwo','ClientProductionThree')  
   OPEN db_cursor  
   FETCH NEXT FROM db_cursor INTO @dbname  
   WHILE   (@@FETCH_STATUS = 0)  
   BEGIN  
       SET @SQL    = REPLACE(@SQL, '@dbname', @dbname)  
       PRINT   @SQL  
   --      EXEC    (@SQL)  
           FETCH NEXT FROM db_cursor INTO  @dbname   
   END  
   CLOSE db_cursor  
   DEALLOCATE db_cursor  
   GO  

Summary

In this article, we have tried to help the developers determine the approach (on choosing Cursor or Sets) which is probably better. And to point out the right choice of using the same.

Thanks to all my distinguished seniors/colleagues of my career for passing on their views and approaches when using Cursors and Sets.