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.
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();
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.
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.
