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.

SQL SERVER - Writing Infinite Loop infiniteloop-800x305

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

Quest

SQL Scripts, SQL Server
Previous Post
SQL SERVER – Query Shortcuts
Next Post
SQL SERVER – Change Database and Table Collation

Related Posts

1 Comment. Leave new

  • Jacob C Cordingley
    December 2, 2020 10:00 am

    I use recursive cte. And it will only let you go 100 levels by default but you can overwrite it by an query hint of option( maxrecursion n ) if you put 0 then it could go for ever if your Recursive member part doesn’t have a where to not return anything.

    I have crashed my sql when I made a query to find fibonacci prime numbers to 2⁶⁴

    Reply

Leave a Reply