SQL Server’s Equivalent to Sleep(): The WAITFOR Statement

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.