WaitFor Delay & Time

WaitFor Delay is the equivilent of sleep in SQL SERVER.  There are two main uses for this function. First is you can wait for a specified duration relative to the current time, or you can wait for an actual time of day.

In order to wait for a duration, you specify the DELAY command:
[cc lang=”sql”]
SELECT GETDATE();
WAITFOR DELAY ’00:00:10′; —- Wait 10 seconds
SELECT GETDATE();
[/cc]

You can also specify the time in milliseconds using:
[cc lang=”sql”]
SELECT GETDATE();
WAITFOR DELAY ’00:00:00.500′; —- Wait half a second
SELECT GETDATE();
[/cc]

Here’s how to wait for a specific time:
[cc lang=”sql”]
SELECT GETDATE();
WAITFOR TIME ’15:00′; — Wait for 3pm
SELECT GETDATE();
[/cc]

I’ve found the most common usage of this to be in a while loop. Sometimes within a while loop you do not want to continuously execute the loops contents, you may want to delay it a little in order to not consume too many resources. SQL Server’s Repl LogReader (used for replication & CDC) uses this waitfor internally to pause it’s own while loop. The only other alternative for pausing a repeating process is to put it in a repeating job, however the lowest pause you can use in a job is 10 seconds.