SQL SERVER – Writing Infinite Loop

Just another day I was working with a client during Comprehensive Database Performance Health Check and we realized that they are running into a strange performance issue which was happening at a random time. To capture the random troublemaker we had to run a diagnostic script periodically. We had to write a loop that runs continuously at every one second and captures the details. Let us learn how we can write an infinite loop.

There are many ways to write an infinite loop in the SQL Server. The most simple one is where I just write WHILE condition with an identical value around the equal to (=) operator.

Here is the simplest example

WHILE 1=1
SELECT 1

Trust me the script above can be very dangerous as there is no break condition and it will keep on going on forever. Currently, it is going to print 1 infinite time. If you have any expensive query instead of the SELECT 1, your server can run out of resources very quickly and stop responding eventually.

It is always a good idea to have a break condition. For example, the following loop will run 100 times and will stop running.

DECLARE @Counter INT 
SET @Counter = 100
WHILE (@Counter > 0)
BEGIN
	SELECT @Counter;
	SET @Counter = @Counter -1;
END

Here is another example of the loop which runs for 60 seconds after that it stops running.

DECLARE @timectr DATETIME 
SET @timectr = GETDATE()
WHILE (DATEDIFF(s,@timectr, GETDATE()) < 60)
BEGIN
	SELECT GETDATE()
	WAITFOR DELAY '00:00:01'
END

I hope this blog post helps you to write the necessary loop for your business. If you have any question, you can always reach out to on LinkedIn.

Reference: Pinal Dave (https://blog.sqlauthority.com)