WAITFOR Statement To Delay Query Execution In SQL Server

Posted June 2, 2019 by Vishwanath Dalvi in Database, SQL Server

WAITFOR Statement To Delay Query Execution In SQL Server

Sometimes, we need to pause the execution of a query to simulate different scenarios. Waiting for a desired amount of time or executing it at a specified clock time. Helpful to pause a query, batch or a stored procedure. WAITFOR statement can be used for delaying a query or execute a query at a specified time.

WAITFOR includes two options DELAY and TIME.

Using WAITFOR statement for practical purposes, including simulating a dirty read, deadlock. Additionally, to delay a query or batch as per requirement. Important to note. Specified command after WAITFOR statement will be considered for delaying not before.

Syntax

WAITFOR (DELAY 'pause_time_to_skip' OR TIME 'specific_time_of_the_day')


Arguments


WAITFOR DELAY – This option can be used to pause a query for a certain duration of time. Time to pass before a query is executed. For example, we can delay the execution of a query by seconds/ minutes or hours.

WAITFOR TIME – Other option to pause a query execution until a specified time of a day is reached. Time to execute, a specific standard clock time in a day. For example, we can execute a query at 10 PM today.

WAITFOR DELAY & TIME In SQL Server

Examples

Let’s do a walkthrough for WAITFOR statement in SQL Server with few examples. Demonstrating two available options DELAY and TIME with WAITFOR statement.

1.

WAITFOR DELAY – Delaying Query by Seconds/ Minutes/ Hours

DELAY option we can specify the time in seconds, minutes and hours. Following examples, we are delaying a query by 2 seconds, 2 minutes and then 2 hours. As a rule, we need to strictly follow the format to avoid any problems.

As a result, the subsequent screen print shows that the following GETDATE query was executed exactly after 2 seconds.

SELECT 1, GETDATE();
WAITFOR DELAY '00:00:02'; -- 2 Seconds
SELECT 2, GETDATE();

SELECT 1, GETDATE();
WAITFOR DELAY '00:02:00'; -- 2 Minutes
SELECT 2, GETDATE();

SELECT 1, GETDATE();
WAITFOR DELAY '02:00:00'; -- 2 Hours
SELECT 2, GETDATE();


WAITFOR SQL Server

2.

WAITFOR DELAY – Simulate Dirty Read Problem

WAITFOR statement can be used to simulate a dirty read problem. In the following screen print, we have used WAITFOR in the first session to delay transaction by 5 seconds. In the second window, the query reads the dirty data. In contrast, the WAITFOR DELAY helps to simulate this scenario.

WAITFOR Statement In SQL Server

3.

WAITFOR TIME – Executing Query at a Specific Time

With the help of TIME option, we can specify the exact future time of the day. This will help us to execute the query at the designated time. Generally in long-running query operations while waiting for some action to be completed then to start the subsequent action at the desired time.

In the following example, specifying TIME as 22:00:00 (10 PM), to execute the stored procedure exactly at 10 PM.

SELECT GETDATE();

WAITFOR TIME '22:00:00';
EXEC sp_who;


Alternative For WAITFOR TIME

Due to WAITFOR statement transactions could be open for long time. If you do not want to risk this and find a better solution then schedule a query using SQL Agent job at specific time.

Summary

Above all, WAITFOR statement provides option to delay a query in SQL Server. Additionally, TIME option to execute query at specified time in a day. If you like this article you should read through Tech-Recipes Database archive posts.

 

About Vishwanath Dalvi

Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.
View more articles by Vishwanath Dalvi

The Conversation

Follow the reactions below and share your own thoughts.

Leave a Reply