Question: How to Introduce Time Delay Between Two TSQL Commands?
Answer: Every time when I hear this question, I am very confident this question is from a developer. This is because I have observed quite a lot of time in developer world this question being discussed. There are processes and functions which need to have a time delay between them and the programming language developer knows how to use various delays related time functions to achieve it.
However, it is really not something I see in the database world. Trust me personally I have yet not come across a situation where I would need delay between two commands in the database. There are different ways to achieve the same output with a different strategy of code in SQL Server. Anyway, in the interview processes sometimes as a candidate we are more incline to answer interviewers than argue with them.
Let us see how we can add delay between two T-SQL statement in following example.
In following example, I am adding a 10 second delay between two SELECT statement.
SELECT GETDATE() CurrentTime WAITFOR DELAY '00:00:10' -- 10 Second Delay SELECT GETDATE() CurrentTime
When you execute above script, it gives us following output.
You can clearly see in the result set that there are 10 second delay between two SELECT statement.
Now here is a question back to you? Have you ever used WAITFOR DELAY function in your business in SQL Server? If yes, would you please leave a comment so we can learn from you.
Here are two blog posts and a video post on the same subject I have written earlier.
- Time Delay While Running T-SQL Query – WAITFOR Introduction
- Delay Function – WAITFOR clause – Delay Execution of Commands
- Video Delay Command in SQL Server – SQL in Sixty Seconds #055
Reference: Pinal Dave (https://blog.sqlauthority.com)
6 Comments. Leave new
I use several time this command, but I don’t remember why :)
Yes i have used in the SQL job step where i have bunch of step where i need to call another SQL job which migit take 30-40 sec to run so by putting wait for delay we can make sure that job is run successfully quickly, also have used in SSIS package when parallel task running on and to customize log the information into same table to avoide lock we use to put wait for delay for 1 sec to 5 sec.
Hi, Pinal,
Odd as it seems, but somtimes you deliberately want to insert a small delay. Thank you for this command.
As a programmer i would do something like this:
DECLARE @before DATETIME,
@after DATETIME;
SET @before = GETDATE();
SET @after = @before;
SELECT @before;
WHILE DATEDIFF(second, @before, @after) < 10
BEGIN
SET @after = GETDATE();
END;
SELECT @after;
GO
Needless to say that the WAITFOR-command is much easier to use…
Yes I have used the delay function, in order to allow an asynchronous process to complete..
The asynchronous process being an SQL agent job, which SSRS runs in response my code invoking the rs_addEvent proc..
My problem was that I need to modify the input parameters of the SSRS job and execute it, sometimes several times in a row, depending upon frequency of user requests from front end app..
I need to be sure that any existing running job, has completed, before I modify the SSRS job input parameters and execute the job.. So I check which jobs are running via MSDB tables, and use WAITFOR DELAY, if the SSRS job is running..
I use WAITFOR rarely, but remember it was useful when i built a custom performance monitoring data collector that reads CPU, I/O and Memory stats then waits x number of seconds then reads them again and calculates the difference and stores that in a trending table.
Hi,
I used it to check the minute value returned from the below as in SSIS the script did not return the 4 digit date if the time was under 10 minutes..
DROP TABLE IF EXISTS #info
CREATE TABLE #info (dt VARCHAR(50), nw DATETIME)
WHILE GETDATE() < '20191016 13:15'
BEGIN
INSERT INTO #info (dt, nw)
SELECT
CAST(DATEPART(YEAR,GETDATE()) AS VARCHAR)
+CAST(RIGHT('00'+DATEPART(MONTH,GETDATE()),2) AS VARCHAR)
+CAST(RIGHT('00'+DATEPART(DAY,GETDATE()),2) AS VARCHAR)+'_'
+CAST(RIGHT('00'+DATEPART(HOUR,GETDATE()),2) AS VARCHAR)
+CAST(RIGHT('00'+DATEPART(MINUTE,GETDATE()),2) AS VARCHAR)
AS dt
,GETDATE() now
WAITFOR DELAY '00:00:10'
END
SELECT * FROM #info