Cursor Versus While Loop

http://www.mssqltips.com/sqlservertip/1599/sql-server-cursor-example/

http://www.mssqltips.com/sqlservertip/1216/executing-a-tsql-batch-multiple-times-using-go/

1.Cursor Example : 

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 

SET @path = 'C:\Backup\'  

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

DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM MASTER.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName  

       FETCH NEXT FROM db_cursor INTO @name   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

2.SQL Server Cursor Analysis

The analysis below is intended to serve as insight into various scenarios where cursor based logic may or may not be beneficial:

• Online Transaction Processing (OLTP) - In most OLTP environments, SET based logic makes the most sense for short transactions.  Our team has run into a third party application that uses cursors for all of its processing, which has caused issues, but this has been a rare occurrence.  Typically, SET based logic is more than feasible and cursors are rarely needed.
• Reporting - Based on the design of the reports and the underlying design, cursors are typically not needed.  However, our team has run into reporting requirements where referential integrity does not exist on the underlying database and it is necessary to use a cursor to correctly calculate the reporting values.  We have had the same experience when needing to aggregate data for downstream processes, a cursor based approach was quick to develop and performed in an acceptable manner to meet the need.
• Serialized processing - If you have a need to complete a process in serialized manner, cursors are a viable option.
• Administrative tasks - Many administrative tasks need to be executed in a serial manner, which fits nicely into cursor based logic, but other system based objects exist to fulfill the need.  In some of those circumstances, cursors are used to complete the process.
• Large data sets - With large data sets you could run into any one or more of the following:
  • Cursor based logic may not scale to meet the processing needs.
  • With large set based operations on servers with a minimal amount of memory, the data may be paged or monopolize the SQL Server which is time consuming can cause contention and memory issues.  As such, a cursor based approach may meet the need.
  • Some tools inherently cache the data to a file under the covers, so processing the data in memory may or may not actually be the case.
  • If the data can be processed in a staging SQL Server database the impacts to the production environment are only when the final data is processed.  All of the resources on the staging server can be used for the ETL processes then the final data can be imported.
  • SSIS supports batching sets of data which may resolve the overall need to break-up a large data set into more manageable sizes and perform better than a row by row approach with a cursor.
  • Depending on how the cursor or SSIS logic is coded, it may be possible to restart at the point of failure based on a checkpoint or marking each row with the cursor.  However, with a set based approach that may not be the case until an entire set of data is completed.  As such, troubleshooting the row with the problem may be more difficult.

3.SQL Server Cursor Alternatives

Below outlines alternatives to cursor based logic which could meet the same needs:

• Set based logic
• SQL Server Integration Services (SSIS) or Data Transformation Services (SSIS)
• WHILE loop
• COALSCE
  • The Many Uses of Coalesce in SQL Server
• sp_MSforeachdb
  • Run The Same SQL Command Against All SQL Server Databases
• sp_MSforeachtable
  • Here is a tip with example code - http://www.mssqltips.com/sqlservertip/1274/change-not-for-replication-value-for-sql-server-identity-columns/
• CASE expression
  • Using the CASE expression instead of dynamic SQL in SQL Server
• Repeat a batch with the GO command
  • Executing a T-SQL batch multiple times using GO

4. Using 'Go'

Let's say you want to create a test table and load it with 1000 records.  You could issue the following command and it will run the same command 1000 times:

CREATE TABLE dbo.TEST (ID INT IDENTITY (1,1), ROWID uniqueidentifier)  GO  INSERT INTO dbo.TEST (ROWID) VALUES (NEWID())   GO 1000

Alternative way : 

To do something similar to this in SQL Server 2000 you would need to write code such as the following. It is not that big a deal, but writing GO 1000 seems a bit easier to me.

CREATE TABLE dbo.TEST (ID INT IDENTITY (1,1), ROWID uniqueidentifier)  CREATE TABLE dbo.TEST2 (ID INT IDENTITY (1,1), ROWID uniqueidentifier)  GO  DECLARE @counter INT   SET @counter = 0   WHILE @counter < 1000     BEGIN         INSERT INTO dbo.TEST (ROWID) VALUES (NEWID())         INSERT INTO dbo.TEST2 (ROWID) VALUES (NEWID())    SET @counter = @counter + 1    END

 Shortcuts:

24 accepted

community owned wiki Answer - feel free to edit or add comments: Keyboard Shortcuts F5, CTRL + E or ALT + X - execute currently selected TSQL code CTRL + R – show/hide Results Pane CTRL + N – Open New Query Window CTRL + L – Display query execution plan Editing Shortcuts CTRL + K + C and CTRL + K + U - comment/uncomment selected block of code (suggested by Unsliced) CTRL + SHIFT + U and CTRL + SHIFT + L - changes selected text to UPPER/lower case SHIFT + ALT + Selecting text - select/cut/copy/paste a rectangular block of text Addons Red Gate's SQL Prompt - IntelliSense (suggested by Galwegian) SQLinForm - formatting of TSQL (suggested by Galwegian) Poor Man's T-SQL Formatter - open-source formatting add-in Other Tips Using comma prefix style (suggested by Cade Roux) Using keyboard accelerators (suggested by kcrumley) Useful Links SQL Server Management Studio Keyboard Shortcuts (full list)