Cursors, they say, do not use them. They are absolutely right... and wrong at the same time.
If Cursors are that bad, then why are they not removed from SQL??
Cursors are probably slow in performance with respect to normal code (Sets), therefore, we avoid using them. But at the same time, they are unavoidable, and are preferred to be used in cases where there is a need to prepare dynamic SQL or complex logics row by row basis.
This tip is primarily to focus on determining a boundary between the two (Cursors and Sets).
If developers have worked in VB (Visual Basic) precisely in recordsets, it works in a similar fashion to that of a cursor.
The cursor iterates through every single row for processing, each time when it fetches a row, it performs a network round trip. Since it is round tripped, the network bandwidth would go for a toss and repeatedly doing this can have a direct impact of the operation used in cursor.
Following is a simple code on how cursors are used in SQL procedures:
- Declare a cursor that defines a result set.
- Open the cursor to establish the result set.
- Fetch the data into local variables as needed from the cursor "one row at a time".
- Close the cursor when done.
Here is the sample code of a cursor:
DECLARE cust_cursor CURSOR
FOR SELECT * FROM Cutomers
FETCH NEXT FROM cust_cursor;
SQL works on sets, i.e., with set of records. The fundamental approach of SQL is to differentiate a pool of data logically. Therefore, Sets can replace the cursor to maximum level.These are normal SQL queries.
The below example shows the difference between them.
Problem: Update all the records in
Customer table with respective pincode using table
Solution using the Cursor
Here is what the code says:
- Fetch the records (Telephone numbers) having pincode
null, from table
- Iterate every fetched record, and break the preceding 4 digits of telephone number.
- Find the respective pincode from
Pincode_details using the number fetched in step 2.
- For every record, check if the variable
@pincode is not a
null and update the pincode into
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 WHILE @@FETCH_STATUS = 0 BEGIN
Declare @pincode char(6)
DECLARE @centerid char(4)
SELECT @centerid = LEFT(@telnumber, 4)
SELECT @pincode = PinCode FROM PinCode_Details WHERE Centerid = @centerid
IF @pincode IS NOT NULL BEGIN UPDATE Customer SET PinCode = @pinCode WHERE CURRENT OF cust_cursor END FETCH NEXT FROM cust_cursor INTO @telnumber
Solution using the Sets
update query with
join will achieve the same result.
SET PinCode = PinCode_Details.PinCode
JOIN PinCode_Details ON
LEFT(Customer.PhoneNumber, 4) = PinCode_Details.Centerid
Customer.PinCode IS NULL
Advantage of Sets over Cursor in the Above Example
- 'Sets' is recommended as there will be a noticeable improvement in the query results.
- Code is easily readable and understandable.
- There will be no network round trips.
- Query can be optimized with indexing.
Now the Question is, When Can We Use the Cursor?
Sets are only bound to be used where the developer builds the query with a prior knowledge of what user is going to see or use.
- Cursors can be used in a situation where the user is given an interface to logically group the data. Then, the developer would have no idea on what kind of grouping will be done by the user.
- Or as per the below example, if an event has to be fired to update a table present in all the databases ('
ClientProductionThree'), then approach of cursor 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
WHERE name IN ('ClientProductionOne','ClientProductionTwo','ClientProductionThree')
FETCH NEXT FROM db_cursor INTO @dbname
WHILE (@@FETCH_STATUS = 0)
SET @SQL = REPLACE(@SQL, '@dbname', @dbname)
FETCH NEXT FROM db_cursor INTO @dbname
Points of Interest
In this tip, we have tried to help 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.
- 18th November, 2014: First version