In SQL Server, you can use the WAITFOR
statement to delay the execution of a batch, stored procedure, or transaction.
It works similar to MySQL‘s sleep()
function.
Actually, it’s probably more like a combination of Postgres‘s three “sleep” functions: pg_sleep()
, pg_sleep_for()
, and pg_sleep_until()
.
I say this, because SQL Server’s WAITFOR
statement gives you the option of specifying either a time delay, or an actual fixed time before execution continues.
Example – Specify a Delay
To specify a time delay, use the DELAY
argument, followed by the actual time to wait/sleep.
The delay can be a maximum of 24 hours.
Here’s an example to demonstrate.
SELECT CURRENT_TIMESTAMP AS [First Timestamp];
WAITFOR DELAY '00:00:10';
SELECT CURRENT_TIMESTAMP AS [Second Timestamp];
Result:
Time: 11.137s (11 seconds) +-------------------------+ | First Timestamp | |-------------------------| | 2020-06-29 00:02:30.963 | +-------------------------+ (1 row affected) Commands completed successfully. +-------------------------+ | Second Timestamp | |-------------------------| | 2020-06-29 00:02:41.610 | +-------------------------+ (1 row affected)
When using the DELAY
option, the value is formatted as hh:mm[[:ss].mss].
The value can be specified either in a datetime data format, or as a local variable. However, dates can’t be specified, so the date part of the datetime value isn’t allowed.
Example – Specify a Time
Alternatively, you can use the TIME
argument to specify a time when the batch, stored procedure, or transaction runs.
In this case, the value provided is the time at which the WAITFOR
statement finishes.
Here’s an example to demonstrate.
SELECT CURRENT_TIMESTAMP AS [First Timestamp];
WAITFOR TIME '04:33:30';
SELECT CURRENT_TIMESTAMP AS [Second Timestamp];
Result:
Time: 39.487s (39 seconds) +-------------------------+ | First Timestamp | |-------------------------| | 2020-06-29 04:32:51.183 | +-------------------------+ (1 row affected) Commands completed successfully. +-------------------------+ | Second Timestamp | |-------------------------| | 2020-06-29 04:33:30.160 | +-------------------------+ (1 row affected)
Here are some things to keep in mind regarding the time value that you provide:
- The time can be specified in a datetime data format, or it can be specified as a local variable.
- Dates can’t be specified, so the date part of the datetime value isn’t allowed.
- The time is formatted as hh:mm[[:ss].mss] and can optionally include the date of 1900-01-01.
The Actual Delay May Vary
The actual time delay may vary from the time you specify, as this will depend on things such as server load.
The time counter starts when the WAITFOR
statement thread is scheduled. If the server is busy, the thread may not be immediately scheduled, so the time delay may be longer than the specified time.
Server Broker Messages
The WAITFOR
statement accepts some arguments/values that are applicable only to Service Broker messages.
When using Service Broker messages you can provide a RECEIVE
statement or a GET CONVERSATION GROUP
statement, as well as a TIMEOUT
argument that specifies the period of time, in milliseconds, to wait for a message to arrive on the queue.
See Microsoft’s official documentation for more information about these options.