SQL Talk

Using a While Loop Instead of Cursors in SQL Server

If you have ever worked with cursors, you may find this title a bit confusing because after all, cursors uses while constructs to iterate between rows. But besides that, I want to show you that in some circumstances when we use a cursor to iterate over a set of rows we can change it to a while loop. In such cases, the only challenge will be to choose a proper exit condition.

Pros and Cons of Using Cursors to Iterate Through Table Rows in SQL Server

Not everything is wrong with cursors, they also have some advantages over other looping techniques.

• Cursors are updatable: When you create a cursor, you use a query to define it using the DECLARE CURSOR instruction. By using the UPDATE option in the cursor creation statement, you can update the columns within the cursor.
• You can move forward and backward in a cursor: By using the SCROLL option in the DECLARE CURSOR statement you can navigate across the cursor records in both directions with the fetch options FIRST, LAST, PRIOR, NEXT, RELATIVE and ABSOLUTE. Keep in mind that the SCROLL option is incompatible with the FORWARD_ONLY and FAST_FORWARD options.
• Cursors can be passed to stored procedures: If you use the GLOBAL option to create a cursor, it can be used in any stored procedure or batch executed in the same connection. This allows you to use cursors on nested stored procedures.
• Cursors have a lot of different options: With cursors you have the chance to use different options that affects how they will behave in regards to locking.
• Cursors don’t need a condition: By using cursors, you are handling a set of rows as a record. This allows you to move across the cursor without the need of having a Boolean condition. For example, you can create a cursor with the name of the databases residing on a SQL Server instance without the need of a surrogate key to work as a test condition like on a WHILE loop.

There are also some negative aspects that you should be aware when using cursors instead of other looping options.

• If you use global cursors in your code you are taking the risk of facing errors due to a cursor being closed by some stored procedure nested in your code.
• Usually cursors have less performance than an equivalent loop using a WHILE loop or CTE.

Pros and Cons of Using a While Loop to Iterate Through Table Rows in SQL Server

There are also benefits to use a WHILE loop compared to a cursor.

• While loops are faster than cursors.
• While loops use less locks than cursors.
• Less usage of Tempdb: While loops don’t create a copy of data in tempdb as a cursor does. Remember that cursors, depending on the options you use to create them can cause the temp tables to be created.

The next list details the negative aspects of WHILE loops.

• Moving forward or backward is complex: To move forward or backward in a loop you need to dynamically change the iteration condition inside the loop. This requires extra care; otherwise you can end up in an infinite loop.
• The risk of an infinite loop: Compared to a cursor, you don’t have a fixed set of data to loop (i.e. the data returned by the SELECT statement in the cursor declaration), instead when using a WHILE loop you have to define a boundary with an expression that is evaluated to true or false.

What is a SQL Server Cursor

A SQL Server cursor is a set of T-SQL logic to loop over a predetermined number of rows one at a time.  The purpose for the cursor may be to update one row at a time or perform an administrative process such as SQL Server database backups in a sequential manner.  SQL Server cursors are used for Development, DBA and ETL processes.

A Cursor impacts the performance of the SQL Server since it uses the SQL Server instances' memory, reduce concurrency, decrease network bandwidth and lock resources. Hence it is mandatory to understand the cursor types and its functions so that you can use suitable cursor according to your needs.

You should avoid the use of the cursor. Basically you should use cursor alternatives like as WHILE loop, sub queries, Temporary tables and Table variables. We should use cursor in that case when there is no option except cursor.

These are steps for using a cursor:

-- 1 - Declare Variables
-- * UPDATE WITH YOUR SPECIFIC CODE HERE *
DECLARE @name VARCHAR(50) -- database name 
DECLARE @path VARCHAR(256) -- path for backup files 
DECLARE @fileName VARCHAR(256) -- filename for backup 
DECLARE @fileDate VARCHAR(20) -- used for file name 

-- Initialize Variables
-- * UPDATE WITH YOUR SPECIFIC CODE HERE *
SET @path = 'C:\Backup\' 

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

-- 2 - Declare Cursor
DECLARE db_cursor CURSOR FOR 
-- Populate the cursor with your logic
-- * UPDATE WITH YOUR SPECIFIC CODE HERE *
SELECT name 
FROM MASTER.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb') 

-- Open the Cursor
OPEN db_cursor

-- 3 - Fetch the next record from the cursor
FETCH NEXT FROM db_cursor INTO @name  

-- Set the status for the cursor
WHILE @@FETCH_STATUS = 0  
 
BEGIN  
	-- 4 - Begin the custom business logic
	-- * UPDATE WITH YOUR SPECIFIC CODE HERE *
   	SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
  	BACKUP DATABASE @name TO DISK = @fileName 

	-- 5 - Fetch the next record from the cursor
 	FETCH NEXT FROM db_cursor INTO @name 
END 

-- 6 - Close the cursor
CLOSE db_cursor  

-- 7 - Deallocate the cursor
DEALLOCATE db_cursor 

Types of Cursors
There are different types of cursors in sql server as listed below. 
1. Static Cursors

A static cursor populates the result set at the time of cursor creation and the query result is cached for the lifetime of the cursor. A static cursor can move forward and backward direction. A static cursor is slower and use more memory in comparison to other cursor. Hence you should use it only if scrolling is required and other types of cursors are not suitable.
No UPDATE, INSERT, or DELETE operations are reflected in a static cursor (unless the cursor is closed and reopened). By default static cursors are scrollable. SQL Server static cursors are always read-only.

2. Dynamic Cursors

A dynamic cursor allows you to see the data updation, deletion and insertion in the data source while the cursor is open. Hence a dynamic cursor is sensitive to any changes to the data source and supports update, delete operations. By default dynamic cursors are scrollable.
3. Forward Only Cursors
A forward only cursor is the fastest cursor among the all cursors but it doesn't support backward scrolling. You can update, delete data using Forward Only cursor. It is sensitive to any changes to the original data source.
There are three more types of Forward Only Cursors.Forward_Only KEYSET, FORWARD_ONLY STATIC and FAST_FORWARD.
A FORWARD_ONLY STATIC Cursor is populated at the time of creation and cached the data to the cursor lifetime. It is not sensitive to any changes to the data source.
A FAST_FORWARD Cursor is the fastest cursor and it is not sensitive to any changes to the data source.

4. Keyset Driven Cursors
A keyset driven cursor is controlled by a set of unique identifiers as the keys in the keyset. The keyset depends on all the rows that qualified the SELECT statement at the time of the cursor was opened. A keyset driven cursor is sensitive to any changes to the data source and supports update, delete operations. By default keyset driven cursors are scrollable.

Cursor Scope
 
Microsoft SQL Server supports the GLOBAL and LOCAL keywords on the DECLARE CURSOR statement to define the scope of the cursor name.
GLOBAL - specifies that the cursor name is global to the connection.
LOCAL - specifies that the cursor name is local to the Stored Procedure, trigger, or query that holds the cursor.
Data Fetch Option in Cursors
 
Microsoft SQL Server supports the following two fetch options for data:
FORWARD_ONLY - Specifies that the cursor can only be scrolled from the first to the last row.
SCROLL - It provides 6 options to fetch the data (FIRST, LAST, PRIOR, NEXT, RELATIVE, and ABSOLUTE).

Why Use a Cursor in SQL Server
Although using an INSERT, UPDATE or DELETE statement to modify all of the applicable data in one transaction is generally the best way to work with data in SQL Server, a cursor may be needed for:
Iterating over data one row at a time
Completing a process in a serial manner such as SQL Server database backups
Updating data across numerous tables for a specific account
Correcting data with a predefined set of data as the input to the cursor
ref: https://www.c-sharpcorner.com/UploadFile/f0b2ed/cursors-in-sql/

https://www.dotnettricks.com/learn/sqlserver/sql-server-different-types-of-cursors

 1, NOLOCK (without lock)

When this option is selected, SQL Server does not add any locks when reading or modifying data. In this case, it is possible for the user to read the data in the incomplete transaction (uncommited Transaction) or rollback (roll back), known as "dirty data."

2. HOLDLOCK (Hold Lock)

When this option is selected, SQL Server will persist this shared lock to the end of the entire transaction and will not be released on the way.

3, UPDLOCK (Modify the Lock)

When this option is selected, SQL Server uses a modify lock instead of a shared lock when reading data, and holds the lock to the entire transaction or to the end of the command. Using this option ensures that multiple processes can read data at the same time, but only that process can modify the data.

4, TABLOCK (table lock)

When this option is selected, SQL Server will place a shared lock on the entire table until the command ends. This option ensures that other processes can read only and cannot modify the data.

5, Paglock (page lock)

This option is the default option when SQL Server uses a shared page lock when it is selected.

6, Tablockx (row it table lock)

When this option is selected, SQL Server will place an exclusive lock on the entire table until the command or transaction ends. This prevents other processes from reading or modifying the data in the table.

HOLDLOCK holds a shared lock until the entire transaction is complete and should be released immediately when the locked object is not needed, equal to the serializable transaction isolation level

NOLOCK statement execution does not emit a shared lock, allowing dirty reads, equal to the READ UNCOMMITTED transaction isolation LEVEL

Paglock with multiple page locks where a table lock is used

READPAST let SQL Server skip any locking lines, perform transactions, apply to READ UNCOMMITTED transaction isolation LEVEL only skip RID lock, skip page, zone and table lock

Rowlock forcing the use of row locks

Tablockx enforces exclusive table-level locks, which prevent any other transactions from using this table during a transaction

Uplock forcing updates to be used when reading tables without sharing locks

Note: The difference between a table that locks a database

SELECT * from table with (HOLDLOCK) Other transactions can read the table but cannot update and delete

SELECT * from table with (Tablockx) Other transactions cannot read tables, update and delete

What does the SQL Server NOLOCK hint do?

• It is an explicit command, which directly specify for particular table or a view.
• It is similar to Nolock hint. It does not use locks against table’s data, once the command is issued.
• The NOLOCK hint allows SQL to read data from tables by ignoring any locks and therefore not get blocked by other processes.
• With the help of the NOLOCK table hint, we can read locked objects (row, page or table) which are locked by open transactions. 
• The NOLOCK hint overrides the default behavior of the SQL Server query optimizer so that the select statement can read the locked objects.
• This can improve query performance by removing the blocks, but introduces the possibility of dirty reads.
• The benefit of using With Nolock is that, no deadlock is encountered against the table’s queries running against the table; also, there is no need to hold the locks against the data, which will save the memory space.
• While using WITH (nolock) there is no need to do anything with subqueries. However, you can use it with single or subqueries. The Readuncommited and WITH (nolock) are similar as transaction isolation level. But, using WITH (nolock) could be unsafe, because it will return inconsistent result

In a database, a deadlock occurs when two or more processes have a resource locked, and each process requests a lock on the resource that another process has already locked. Neither of the transactions here can move forward, as each one is waiting for the other to release the lock. The following diagram explains this.

Difference between repeatable read and serializable
Repeatable read prevents only non-repeatable read. Repeatable read isolation level ensures that the data that one transaction has read, will be prevented from being updated or deleted by any other transaction, but it doe not prevent new rows from being inserted by other transactions resulting in phantom read concurrency problem.

Serializable prevents both non-repeatable read and phantom read problems. Serializable isolation level ensures that the data that one transaction has read, will be prevented from being updated or deleted by any other transaction. It also prevents new rows from being inserted by other transactions, so this isolation level prevents both non-repeatable read and phantom read problems.

How to Overcome the Concurrency Problems in SQL Server?

To overcome the above Concurrency Problems, SQL Server provides different types of Transaction Isolation Levels, to balance the concurrency problems and performance depending on our application’s need. The Transaction Isolation Levels provided by SQL Server are as follows

1. Read Uncommitted
2. Read Committed
3. Repeatable Read
4. Snapshot
5. Serializable

The isolation level that you choose for your transaction, defines the degree to which one transaction must be isolated from resource or data modifications made by other transactions. Depending on the isolation level you have chosen you get varying degrees of performance and concurrency problems. The table here has the list of isoltaion levels along with concurrency side effects.

Isolation Level	Dirty Reads	Lost Update	Nonrepeatable Reads	Phantom Reads
Read Uncommitted	Yes	Yes	Yes	Yes
Read Committed	No	Yes	Yes	Yes
Repeatable Read	No	No	No	Yes
Snapshot	No	No	No	No
Serializable	No	No	No	No

If you choose the lowest isolation level (i.e Read Uncommitted), it increases the number of concurrent transactions that can be executed at the same time, but the down side is you have all sorts of concurrency issues. On the other hand if you choose the highest isolation level (i.e Serializable), you will have no concurrency side effects, but the downside is that, this will reduce the number of concurrent transactions that can be executed at the same time if those transactions work with same data.

These 5 isolation levels work on two major concurrency models:

1. Pessimistic model - In the pessimistic model of managing concurrent data access, the readers can block writers, and the writers can block readers.
2. Optimistic model - In the optimistic model of managing concurrent data access, the readers cannot block writers, and the writers cannot block readers, but the writer can block another writer.

Note that readers are users are performing the SELECT operations. Writers are users are performing INSERT, ALTER, UPDATE, S.E.T. operations.

Out of the five isolation levels, Read Uncommitted, Read Committed, Repeatable Read, and Serializable come under the pessimistic concurrency model. Snapshot comes under the optimistic concurrency model. These levels are ordered in terms of the separation of work by two different processes, from minimal separation to maximal.

Let's look at each of these isolation levels and how they affect concurrency of operations.

Read Uncommitted

This is the first level of isolation, and it comes under the pessimistic model of concurrency. In Read Uncommitted, one transaction is allowed to read the data that is about to be changed by the commit of another process. Read Uncommitted allows the dirty read problem.

When this level is set, the transaction can read uncommitted data resulting in the Dirty Read problem. With this isolation level, we allow a transaction to read the data which is being updated by other transaction and not yet committed. Suppose User A is trying to read a row which is being updated by User B. Here, we are allowing User A to read the un-updated/uncommitted data i.e old data.

Example

1. SET TRANSACTION ISOLATION LEVEL   
2. READ UNCOMMITTED  
3. BEGIN TRANSACTION MyTransaction  
4. BEGIN TRY  
5. UPDATE Account SET Debit=100 WHERE Name='John Cena'  
6. UPDATE ContactInformation SET Mobile='1234567890' WHERE Name='The Rock'  
7. COMMIT TRANSACTION MyTransaction  
8. PRINT 'TRANSACTION SUCCESS'  
9. END TRY  
10. BEGIN CATCH  
11. ROLLBACK TRANSACTION MyTransaction  
12. PRINT 'TRANSACTION FAILED'  
13. END CATCH  

Read Committed

This is the second level of isolation and also falls under the pessimistic model of concurrency. In the Read Committed isolation level, we are only allowed to read data that is committed, which means this level eliminates the dirty read problem. In this level, if you are reading data then the concurrent transactions that can delete or write data, some work is blocked until other work is complete.

1. This prevents Dirty Read. When this level is set, the transaction can not read the data that is being modified by the current transaction. This will force user to wait for the current transaction to finish up its job. Suppose User A is trying to read a row which is being updated by User B. Here, we are asking User A to wait for the User B to finish its update task, and giving the updated/correct data to User A. But the problem with this level is - it can't resolve Phantom Read or Inconsistency Analysis i.e it asks User A to wait for Read but not for update or insert.
   
   Example
   
   1. SET TRANSACTION ISOLATION LEVEL   
   2. READ COMMITTED  
   3. BEGIN TRANSACTION MyTransaction  
   4. BEGIN TRY  
   5. UPDATE Account SET Debit=100 WHERE Name='John Cena'  
   6. UPDATE ContactInformation SET Mobile='1234567890' WHERE Name='The Rock'  
   7. COMMIT TRANSACTION MyTransaction  
   8. PRINT 'TRANSACTION SUCCESS'  
   9. END TRY  
   10. BEGIN CATCH  
   11. ROLLBACK TRANSACTION MyTransaction  
   12. PRINT 'TRANSACTION FAILED'  
   13. END CATCH  

Repeatable Read

The Repeatable Read isolation level is similar to the Read Committed level and eliminates the Non-Repeatable Read problem. In this level, the transaction has to wait till another transaction's update or read query is complete. But if there is an insert transaction, it does not wait for anyone. This can lead to the Phantom Read problem.

This level does every work that Read Committed does. but it has one additional benefit. User A will wait for the transaction being executed by User B to execute it's Update query as well, like Read Query. But Insert query doesn't wait, this also creates Phantom Read problem.

Example

1. SET TRANSACTION ISOLATION LEVEL   
2. REPEATABLE READ 

Serializable

This is the highest level of isolation in the pessimistic model. By implementing this level of isolation, we can prevent the Phantom Read problem. In this level of isolation, we can ask any transaction to wait until the current transaction completes.

This is the maximum level of Isolation level provided by SQL Server transaction. We can prevent Phantom Read problem by implementing this level of isolation. It asks User A to wait for the current transaction for any kind of operation he wants to perform.

Example

1. SET TRANSACTION ISOLATION LEVEL   
2. SERIALIZABLE  

Snapshot

Snapshot follows the optimistic model of concurrency, and this level of isolation takes a snapshot of the current data and uses it as a copy for the different transactions. Here each transaction has its copy of data, so if a user tries to perform a transaction like an update or insert, it asks him to re-verify all the operation before the process gets started executing.

This level takes a snapshot of current data. Every transaction works on its own copy of data. When User A tries to update or insert or read anything, we ask him to re-verify the table row once again from the starting time of its execution, so that he can work on fresh data. with this level. We are not giving full faith to User A that he is going to work on fresh data but giving high-level changes of data integrity.

Example

1. SET TRANSACTION ISOLATION LEVEL   
2. SNAPSHOT  

Note: Isolation level also has a problem called "Dead Lock"- "Both the transactions lock the object and waits for each other to finish up the job". DeadLock is very dangerous because it decreases the concurrency and availability of database and the database object.