Cursors vs Sets in SQL

Introduction

They say not to use Cursors. They are absolutely right and wrong at the same time.

If Cursors are that bad, then why is it not removed from SQL?

Background

Cursors are probably slow in performance with respect to normal code (Sets), therefore, we avoid using it. But at the same time it is unavoidable and is proffered to be used in cases where there is a need to prepare dynamic SQL or complex logics row by row basis.

The article primarily focuses on determining a boundary between the two, Cursors and Sets.

Explanation

Cursor

If the developers have worked with Visual Basic (VB) precisely in recordsets, it works in a similar fashion to that of a cursor.

The cursor iterates through every single row for processing and each time it fetches a row, it does a network round trip. Since it does the round trips, the network bandwidth would go for a toss and repeatedly doing this can have a direct impact of the operation used with the Cursor.

The following is a simple descrition of how cursors are used in SQL procedures:

  1. Declare a cursor that defines a result set.

  2. Open the cursor to establish the result set.

  3. Fetch the data into local variables as needed from the cursor, one row at a time.

  4. Close the cursor when done.

Here is the sample code of a cursor:

  1. DECLARE cust_cursor CURSOR  
  2.     FOR SELECT * FROM Cutomers  
  3. OPEN cust_cursor  
  4. FETCH NEXT FROM cust_cursor;  
  5. CLOSE cust_cursor   

Sets

SQL works on sets, in other words with a set of records. The fundamental approach of SQL is to differentiate a pool of data logically. Therefore, Sets can replace the cursor to a maximum level. These are normal SQL queries. The following example shows the difference between them.

Example

Problem: Update all the records in the Customer table with a respective pincode using the table Pincode_Details.

Solution using the Cursor

Here is what the code says:

  1. Fetch the records (Telephone numbers) having 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 a null and update the pincode into the Customer table.

  1. DECLARE @telnumber char(8)  
  2. DECLARE cust_cursor CURSOR FOR                             //  
  3.    SELECT TelNumber FROM Customer WHERE PinCode IS NULL    //  
  4. OPEN cust_cursor                                           //  
  5. FETCH NEXT FROM cust_cursor  INTO @telnumber               // (1)  
  6. WHILE @@FETCH_STATUS = 0 BEGIN  
  7.    Declare @pincode char(6)  
  8.    DECLARE @centerid char(4)  
  9.    SELECT @centerid = LEFT(@telnumber, 4)                  // (2)  
  10.   
  11.    SELECT @pincode = PinCode                               //  
  12.    FROM PinCode_Details                                    //  
  13.    WHERE Centerid = @centerid                              // (3)  
  14.   
  15.    IF @pincode IS NOT NULL                                 //  
  16.    BEGIN                                                   //  
  17.        UPDATE Customer SET PinCode = @pinCode              //  
  18.        WHERE CURRENT OF cust_cursor                        //  
  19.    END                                                     // (4)  
  20.    FETCH NEXT FROM cust_cursor INTO @telnumber  
  21. END  
  22. CLOSE cust_cursor   
  23. DEALLOCATE cust_cursor   
  24.  

Solution using the Sets

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

  1. UPDATE Customer   
  2. SET PinCode = PinCode_Details.PinCode   
  3. FROM Customer  
  4. JOIN PinCode_Details ON  
  5.     LEFT(Customer.PhoneNumber, 4) = PinCode_Details.Centerid  
  6. WHERE  
  7.     Customer.PinCode IS NULL  

Advantage of Sets over Cursor in above example

  1. Sets are recommended since there will be a noticable improvement in the query results.

  2. Code is easily readable and understandable.

  3. There will be no network round trips.

  4. Query can be optimized with indexing.

Now the question is, when can we use cursors?

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

  1. Cursors can be used in a situation where the user is given with an interface to logically group the data. 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.

  1. DECLARE @dbname VARCHAR(50)  
  2. DECLARE @databasename VARCHAR(256)  
  3. DECLARE @SQL varchar(8000)  
  4.    
  5. SET @SQL = 'UPDATE  @dbname.dbo.tbldailyEventFired  
  6.         SET EndTime = CONVERT(datetime,''2014-11-18 23:59:00'',120)   
  7.         WHERE EndTime = (CONVERT(datetime,''2015-11-17 23:59:00'',120))'  
  8.    
  9. DECLARE db_cursor CURSOR FOR   
  10.     SELECT name   
  11.     FROM master.dbo.sysdatabases  
  12.     WHERE name IN ('ClientProductionOne','ClientProductionTwo','ClientProductionThree')  
  13. OPEN db_cursor  
  14. FETCH NEXT FROM db_cursor INTO @dbname  
  15. WHILE   (@@FETCH_STATUS = 0)  
  16. BEGIN  
  17.     SET @SQL    = REPLACE(@SQL, '@dbname', @dbname)  
  18.     PRINT   @SQL  
  19. --      EXEC    (@SQL)  
  20.         FETCH NEXT FROM db_cursor INTO  @dbname   
  21. END  
  22. CLOSE db_cursor  
  23. DEALLOCATE db_cursor  
  24. GO  

Points of Interest

In this article, we have tried to help the developers determine the approach (on choosing Cursor or Sets), probably a better one. And to clearly 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.