T-SQL: Avoid Cursor. Use WHILE instead

Set-based Database Development

As database developer, we need to switch our mindsets to “Set-based thinking”. In simple words we need to manipulate the data as a “set”.

An example of set-based database development is:

• JOIN tables
• Aggregate the result
• Calculate the portion (%) of a row to the total of a group

in a single query.

We have to always prefer the set-based approach.

Row by agonizing row (RBAZ) Database Develpment

We can do the same with so called RBAZ, i.e. calculate the same percentage as mentioned above row by row.

Only if there is no chance to do the job with set-based operations, we can switch to RBAZ.

In this case we can write:

• Cursor
• Cursor alternative (WHILE loop)
  

Cursor

The cursor:

1. Populates a ‘resultset table’ (a list of variables)
2. Loops every row of this table and does something (operation)

The values in the ‘resultset table’ are the variables in the operation.

The anatomy of a cursor:

Note that the number and the order of the columns in the ‘resultset table’ has to match the assignment of the operational variables (highlighted in green).

Example of a cursor:

USE [master];

SET NOCOUNT ON;

-- Variables
DECLARE
  @database_name         SYSNAME        -- Operational
  , @compatibility_level INT            -- Operational
  , @now                 NCHAR(15)      -- Constant
  , @dynamic_sql         NVARCHAR(MAX); -- Dynamic SQL code

-- Set constant variables
SELECT @now = FORMAT(GETDATE(), N'yyyyMMdd_HHmmss');

--** Cursor - Start **--
DECLARE cursor_name CURSOR FOR 
  SELECT [name], [compatibility_level]
  FROM [master].sys.databases
  WHERE [name] NOT IN (N'master', N'tempdb', N'model', N'msdb', N'SSRSDB', N'SSRSDBTempDB');

OPEN cursor_name;
-- Set operation variables
FETCH NEXT FROM cursor_name INTO @database_name, @compatibility_level;

WHILE (@@FETCH_STATUS = 0)
BEGIN  
  SELECT @dynamic_sql = CONCAT(
N'USE [master];
BACKUP DATABASE ', QUOTENAME(@database_name),'
TO DISK = ''C:\Temp\'
  , @database_name, N'_'
  , @now, N'_'
  , CASE @compatibility_level
    WHEN 80 THEN 'SQL_Server_2000'
    WHEN 90 THEN 'SQL_Server_2005'
    WHEN 100 THEN 'SQL_Server_2008'
    WHEN 110 THEN 'SQL_Server_2012'
    WHEN 120 THEN 'SQL_Server_2014'
    WHEN 130 THEN 'SQL_Server_2016'
    WHEN 140 THEN 'SQL_Server_2017'
    WHEN 150 THEN 'SQL_Server_2019'
  END
  , N'.bak'';

');
  
  EXEC sp_executesql @dynamic_sql;

  -- Set operation variables
  FETCH NEXT FROM cursor_name INTO @database_name, @compatibility_level
END 

CLOSE cursor_name;
DEALLOCATE cursor_name;
--** Cursor - End **--

Cursor alternative (WHILE loop)

The cursors generate performance issues and it is a good idea to replace them with WHILE loops.

In the next simple examples i give 2 options:

• Loop the “recordset” table directly. The key column is not sequential and may have gaps:

USE [master];

SET NOCOUNT ON;

-- Variables
DECLARE
  @database_id           INT            -- Loop
  , @database_name       SYSNAME        -- Operational
  , @compatibility_level SYSNAME        -- Operational
  , @now                 NCHAR(15)      -- Constant
  , @dynamic_sql         NVARCHAR(MAX); -- Dynamic sql code

-- Set constant variables
SELECT @now = FORMAT(GETDATE(), N'yyyyMMdd_HHmmss');

-- Set loop variable
SELECT @database_id = MIN(database_id) - 1
FROM sys.databases
WHERE [name] NOT IN (N'master', N'tempdb', N'model', N'msdb', N'SSRSDB', N'SSRSDBTempDB');

--** WHILE - Start **--
WHILE EXISTS
  (
    SELECT 1
    FROM sys.databases
    WHERE
      database_id > @database_id -- database_id is not sequential
      AND [name] NOT IN (N'master', N'tempdb', N'model', N'msdb', N'SSRSDB', N'SSRSDBTempDB')
  )
BEGIN
  -- Set loop variable
  SELECT TOP (1) @database_id = database_id
  FROM sys.databases
  WHERE
    database_id > @database_id
    AND [name] NOT IN (N'master', N'tempdb', N'model', N'msdb', N'SSRSDB', N'SSRSDBTempDB')
  ORDER BY database_id;
  
  -- Set operation variables
  SELECT
    @database_name = [name]
    , @compatibility_level = [compatibility_level]
  FROM sys.databases
  WHERE
    database_id = @database_id
    AND [name] NOT IN (N'master', N'tempdb', N'model', N'msdb', N'SSRSDB', N'SSRSDBTempDB');
  
  -- Operations - Start
  SELECT @dynamic_sql = CONCAT(
N'USE [master];
BACKUP DATABASE ', QUOTENAME(@database_name),'
TO DISK = ''C:\Temp\'
  , @database_name, N'_'
  , @now, N'_'
  , CASE @compatibility_level
    WHEN 80 THEN 'SQL_Server_2000'
    WHEN 90 THEN 'SQL_Server_2005'
    WHEN 100 THEN 'SQL_Server_2008'
    WHEN 110 THEN 'SQL_Server_2012'
    WHEN 120 THEN 'SQL_Server_2014'
    WHEN 130 THEN 'SQL_Server_2016'
    WHEN 140 THEN 'SQL_Server_2017'
    WHEN 150 THEN 'SQL_Server_2019'
  END
  , N'.bak'';

');
  
  EXEC sp_executesql @dynamic_sql;
  -- Operations - End
END
--** WHILE - End **--

• Build a custom ‘recordset’ table. The key column is sequential (identity):

USE AdventureWorks2019;

SET NOCOUNT ON;

-- Operational Variables
DECLARE @business_entity_id INT

-- Result table
DECLARE @result TABLE
  (
    customer_id INT
    , customer_name NVARCHAR(128)
    , total_due DECIMAL(18, 6)
  );

-- 'Recordset' table
DECLARE @recordset TABLE
  (
    id INT IDENTITY(1, 1)    -- Loop
    , business_entity_id INT -- Operation
  );

-- Populate 'recordset' table
INSERT @recordset (business_entity_id)
SELECT PP.BusinessEntityID
FROM
  Person.Person                     AS PP
  JOIN Person.BusinessEntityAddress AS PBE ON pp.BusinessEntityID = PBE.BusinessEntityID
  JOIN Person.[Address]             AS PA  ON PBE.AddressID = PA.AddressID
WHERE PA.StateProvinceID = 9; -- California

-- Set loop variable (id is sequential)
DECLARE
  @loop_id INT = 1
  , @max_id INT;

SELECT @max_id = MAX(id)
FROM @recordset;

--** WHILE - Start **--
WHILE (@loop_id <= @max_id)
BEGIN
  SELECT @business_entity_id = business_entity_id
  FROM @recordset
  WHERE id = @loop_id;

  INSERT @result (customer_id, customer_name, total_due)
  SELECT
    SC.CustomerID
    , CONCAT(
      PP.FirstName
      , CASE WHEN (PP.FirstName IS NOT NULL AND PP.LastName IS NOT NULL) THEN N' ' END
      , PP.LastName
    )
    , SUM(SOH.TotalDue)
  FROM
    Person.Person               AS PP
    JOIN Sales.Customer         AS SC  ON PP.BusinessEntityID = SC.PersonID
    JOIN Sales.SalesOrderHeader AS SOH ON SC.CustomerID = SOH.CustomerID
  WHERE PP.BusinessEntityID = @business_entity_id
  GROUP BY
    PP.BusinessEntityID
    , SC.CustomerID
    , PP.FirstName
    , PP.LastName

  SET @loop_id += 1;
END
--** WHILE - Start **--

-- Get the result
SELECT * 
FROM @result
ORDER BY total_due DESC;

Keep it simple :-)