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)
1 Comment. Leave new
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⁶⁴