SQL Server Loops – Alternative to OPEN, FETCH & CLOSE loop using ROW_NUMBER in TSQL

Have I every said that I hate don’t like cursor based logic? This time, I am going to show how to replace cursor logic in your TSQL with loops.

Caution: RBAR is bad!

While row-by-row processing is generally bad both in Oracle and SQL Server, there are instances where it is best to simply loop through and get the job done when the number of rows to process is extremely “small” and the logic inside the loop “large”. They call it RBAR (Row By Agonizing Row) for a reason.

Open/Fetch/Close is bad too!

Note: It is not bad (in fact, it is good and vital) if your cursor uses “FOR UPDATE” which is used for locking the rows as you work with the cursor. So, if you have FOR UPDATE, please DO NOT replace with this looping recommended method below.

In SQL Server, usage of cursors is generally considered a bad practice due to several reasons.

  • For starters, it is a lot of steps to use the simplest of cursors (7 mandatory steps)
    • DECLARE, OPEN, FETCH, WHILE loop, FETCH NEXT, CLOSE and DEALLOCATE
ISO Syntax

DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR
FOR select_statement
[ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]
[;]

Transact-SQL Extended Syntax

DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ]
FOR select_statement
[ FOR UPDATE [ OF column_name [ ,...n ] ] ]
[;]
  • One never remembers all the steps when using a cursor. A reference is “always” needed.
  • Cursors opened with the wrong options can result in table unintended locking. Knowledge of cursor types is essential (static, keyset, dynamic, fast-forward only etc)

Using ROW_NUMBER

Before we look at an example, let us clarify how ROW_NUMBER() works

ROW_NUMBER() OVER (ORDER BY [customer_code]) AS rnk

Something like the above in the SELECT column list of a SQL says – Order the results by customer_code and assign the ROW_NUMBER() value to each row returned. In the above example, the customer_code is included because we want the ordering to be the same every time this query is run and the logic described later depends on it.

Looping example using ROW_NUMBER

I already did a post on how to do this type of looping here.

Here is a simple alternative to using cursors using a simple looping mechanism. Here we loop through a set of data returned by a SELECT statement to perform some logic within a loop.

SELECT Customer_id, Customer_code
INTO #Temp_Table_Customers
FROM Customer_master;

--Loop through and get the list
SET @Counter = 0;

WHILE 1=1
BEGIN
    SET @Counter = @Counter + 1;

    SELECT @Customer_Code = MIN(Customer_code)
    FROM
    	(
		SELECT
		      Customer_code,
		      ROW_NUMBER() OVER (
			    ORDER BY [Customer_code]
	       ) AS rnk
		FROM #Temp_Table_Customers
    	) a
	WHERE
	rnk = @Counter;

    IF @Customer_Code  IS NULL
	  BREAK;

    --Do some processing here…
    --Insert data into holding table for this Customer_code
    INSERT INTO My_Table
    SELECT * FROM big_query
    WHERE Customer_code = @Customer_code;

END;

The key things to note in the above are the following

  • ROW_NUMBER() OVER (ORDER BY [customer_code]) – This essentially controls which row is processed. The ORDER BY clause ensures that it has a column(s) that uniquely identify the rows
  • WHERE rnk = @Counter – Ensures that we are picking up the right row to process depending on the looping variable value
  • BREAK; – Ensures that we exit the loop when we are done

This mechanism is a lot more simple to follow and the source table that is being looped through is not locked for the duration of the loop for any reason since the contents were moved to a temp table to begin with.

Cursors do have their place. When you need fine control over the locking mechanism based on the transaction isolation level chosen, they are the best option.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s