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:
SELECT GETDATE(); WAITFOR DELAY '00:00:10'; —- Wait 10 seconds SELECT GETDATE();
You can also specify the time in milliseconds using:
SELECT GETDATE(); WAITFOR DELAY '00:00:00.500'; —- Wait half a second SELECT GETDATE();
Here’s how to wait for a specific time:
SELECT GETDATE(); WAITFOR TIME '15:00'; -- Wait for 3pm SELECT GETDATE();
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.
Popular search terms:
