I learned to love WHILE (true) and you should too

This is one of those things that I would have never expected to adopt, and yet here I am! Not just loving “infinite” loops, but also confident enough that I can convince you to feel the same!

It may sound very weird to most people at first, since it’s such an unorthodox way of using WHILE loops, and probably goes against what you consider to be coding best practices and standards.

What is a WHILE (true) Loop?

What I’m talking about is a WHILE loop that uses an arbitrary boolean expression that is always true (such as the commonly used “1=1“, but it could be anything else that’s always true).

The perception of WHILE loops here is flipped on its head, because instead of declaring when the loop should continue running, we declare when the loop should stop. We do this using an IF conditional inside the WHILE loop, which when true, would execute the BREAK command, and thus stop the loop.

For example:

WHILE 1=1
BEGIN
	IF /*stopping condition*/
		BREAK;
		
	/* loop body */
END

It’s awesome and I’ll prove it

It even took a while to convince me. But after experimenting with it myself, I’ve learned to love the heck out of it, as its benefits outweigh any perceived downsides that you may think it has.

Let’s break it down by topics and compare each to its vanilla counterpart:

• Minimalism
• Robustness
• Readability

Minimalism

Who here hasn’t heard of the DRY principle, also known as “Don’t Repeat Yourself“?

When a regular WHILE loop first starts, it first needs its corresponding boolean expression to be true. On the vast majority of cases that I see, this means that some kind of “initialization step” would have to be utilized before the loop, otherwise it would never start.

For example, when using CURSORS as the classic use-case (I know I know, ew cursors, don’t @ me), we would normally be checking the @@FETCH_STATUS in the loop condition. But we won’t be able to enter the loop unless we also FETCH from the cursor first.

Then, once inside the loop, we would have to FETCH again in order to update the @@FETCH_STATUS and move on to the next record:

FETCH NEXT FROM MyCursor INTO @MyVar

WHILE @@FETCH_STATUS = 0
BEGIN
	/* loop body */

	FETCH NEXT FROM MyCursor INTO @MyVar
END

Running the same FETCH command twice?? Such barbarism!
What if I have to maintain this code and change the variables list in the FETCH command?
I would have to do it twice! Oh, the horror! 😱
No longer! Behold the elegance of WHILE (true):

WHILE 1=1
BEGIN
	FETCH NEXT FROM MyCursor INTO @MyVar
	
	IF @@FETCH_STATUS <> 0
		BREAK;
		
	/* loop body */
END

Look! I only had to use the FETCH command once! Simply instead of looping while @@FETCH_STATUS=0, I’m stopping when @@FETCH_STATUS<>0.

Let’s take a different example. What about the all-too-familiar use case of looping based on the @@ROWCOUNT resulting from some SQL command? i.e. “do while there are rows to process“?

Let’s take a “delete in chunks” use case as an example.

If we are to use the vanilla form of a WHILE loop, this means that either we have to start by running an additional DELETE command before the loop in order to initialize the @@ROWCOUNT variable, or we’d use an additional local variable to save the @@ROWCOUNT value and initialize it with a “dummy” value before the loop.

Variant 1 example (DELETE twice):

DELETE TOP (1000) FROM MyTable WHERE ToDelete = 1

WHILE @@ROWCOUNT > 0
BEGIN
	DELETE TOP (1000) FROM MyTable WHERE ToDelete = 1
END

Variant 2 example (local variable):

DECLARE @rcount INT
SET @rcount = 1

WHILE @rcount > 0
BEGIN
	DELETE TOP (1000) FROM MyTable WHERE ToDelete = 1

	SET @rcount = @@ROWCOUNT
END

Good lord! So many redundant words! So disgusting! 🤮
Let’s fix this with an elegant WHILE (true):

WHILE 1=1
BEGIN
	DELETE TOP (1000) FROM MyTable WHERE ToDelete = 1
	
	IF @@ROWCOUNT = 0
		BREAK;
END

Oh? What, we’re done already? Look at how elegantly minimalist this is! No extra variables, no dummy values, no duplicate commands. Simply beautiful. 😄

Robustness

Another downside to the vanilla WHILE loop is the fact that there is only one single boolean expression to be evaluated.

What if you have multiple stop conditions? What if checking those stop conditions requires some kind of complex logic? That would make the boolean expression in your WHILE loop very complicated, and may require you to use additional local variables, multiple sub-queries, or duplicate code.

No such limitations with a WHILE (true) loop. 😎

Let’s take for example a script which needs to compress a list of indexes, but stop itself based on multiple conditions such as time of day, CPU utilization, and of course whether there’s anything left to process:

SET NOCOUNT ON;

IF OBJECT_ID('tempdb..#INDEXTABLE') IS NOT NULL DROP TABLE #INDEXTABLE;
CREATE TABLE #INDEXTABLE (
	IndexName SYSNAME NULL, 
	TableName NVARCHAR(4000),
	CompressionType SYSNAME
)
 
INSERT INTO #INDEXTABLE VALUES ('PK_MyTable1', '[dbo].[MyTable1]', 'PAGE');
INSERT INTO #INDEXTABLE VALUES ('IX_MyTable1_SomeColumn', '[dbo].[MyTable1]', 'PAGE');
INSERT INTO #INDEXTABLE VALUES ('PK_MyTable2', '[dbo].[MyTable2]', 'ROW');
INSERT INTO #INDEXTABLE VALUES ('IX_MyTable2_SomeOtherColumn', '[dbo].[MyTable2]', 'ROW');
INSERT INTO #INDEXTABLE VALUES ('PK_MyTable3', '[dbo].[MyTable3]', 'PAGE');

DECLARE @IndexName SYSNAME, @TableName NVARCHAR(4000), @CompressionType SYSNAME, @CMD NVARCHAR(MAX)
DECLARE ToCompressCursor CURSOR
LOCAL FORWARD_ONLY FAST_FORWARD
FOR
SELECT IndexName, TableName, CompressionType
	, Cmd = N'USE ' + QUOTENAME(DB_NAME()) + N'; ALTER INDEX ' + QUOTENAME(IndexName) + N' ON ' + TableName
        + N' REBUILD PARTITION = ALL'
	+ N' WITH (DATA_COMPRESSION = ' + CompressionType + N', ONLINE = ON, SORT_IN_TEMPDB = ON);'
FROM #INDEXTABLE

OPEN ToCompressCursor

WHILE 1 = 1
BEGIN
	FETCH NEXT FROM ToCompressCursor INTO @IndexName, @TableName, @CompressionType, @CMD

	IF @@FETCH_STATUS <> 0 -- stop when no more indexes to check
	BEGIN
		PRINT N'Done.';
		BREAK;
	END
		
	IF DATEPART(hour, GETDATE()) NOT BETWEEN 3 AND 7 -- stop when current time is not between 03:00 and 07:00
	BEGIN
		PRINT N'Stopping because reached outside allowed execution timeframe.';
		BREAK;
	END

	DECLARE @AvgCPU FLOAT;

	SELECT @AvgCPU = AVG( 100 - record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') )
	from (
	SELECT TOP (10) [timestamp], convert(xml, record) as record
	FROM sys.dm_os_ring_buffers
	WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
	AND record like '%<SystemHealth>%'
	ORDER BY [timestamp] DESC
	) as RingBufferInfo

	IF @AvgCPU > 80 -- stop when average CPU of 10 latest samples is above 80 %
	BEGIN
		PRINT N'Stopping because CPU utilization is too high.';
		BREAK;
	END

	-- Check if index has no compression
	IF EXISTS (
	SELECT NULL
	FROM sys.partitions AS p WITH (NOLOCK)
	INNER JOIN sys.indexes AS ix WITH (NOLOCK)
		ON ix.object_id = p.object_id
		AND ix.index_id = p.index_id
	WHERE ix.object_id = OBJECT_ID(@TableName)
	AND (ix.name = @IndexName OR (@IndexName IS NULL AND ix.index_id = 0))
	AND p.data_compression_desc <> @CompressionType
	)
	BEGIN
		PRINT @CMD;
		EXEC sp_executesql @CMD;
	END
END

CLOSE ToCompressCursor
DEALLOCATE ToCompressCursor

DROP TABLE #INDEXTABLE

This script is a simplified version of a remediation script generated by my ultimate compression savings estimation script.

Look at how we have 3 different possible stopping conditions here. In this case: based on the cursor, the time of day, and the server’s CPU utilization.

Just imagine how you would have had to implement this if you were to use the vanilla version of WHILE. In order to achieve what we want, we would have had to perform each of these checks twice, and/or had to write a very complicated WHILE boolean expression.

Using WHILE (true), on the other hand, we have a very lean and clean code, where each command is written only once, and performed only as many times as actually needed. 👍

Readability

Assuming you reached this part after reading the above two parts, it should be easy for you to agree with me that, despite initial intuition, a script utilizing WHILE (true) properly would actually be more readable than the regular variant: Utilizing less variables, less duplicate commands, and overall leaner code. That also makes it easier to maintain as well.

One tip I’d urge you to adopt in this regard, though, is to try and place all of your stopping conditions at the top of the WHILE loop. Since, intuitively, most people would be looking at the top of the WHILE loop for its boolean expression to see when it should stop and when it should continue.

Conclusion

Obviously, WHILE (true) loops are nothing “new”. It’s not a recently added feature of SQL Server.

But after recently adopting it, I personally feel a significant improvement in terms of productivity. I even feel that it makes it easier for me to write T-SQL code now that I let myself focus on the stopping conditions of WHILE loops, instead of on the continue-running condition.

I’m sharing this with you in the hopes that you’d benefit from this approach as much as I did, and slowly we’ll start seeing a decline in the vanilla approach as people learn of its alternative.

Here’s to a cleaner T-SQL! 🥂🍻