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

,
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 Cancel reply

Menu
Exit mobile version