Today we will look at one very small but interesting feature of SQL Server. Please note that this is not much known feature of SQL Server. In SQL Server sometime there are requirement when T-SQL script has to wait for some time before executing next statement. It is quite common that developers depends on application to take over this delay issue. However, SQL Server itself has very strong time management function of WAITFOR. Let us see two usage of WAITFOR clause.
Official explanation of WAITFOR clause from Book Online is “Blocks the execution of a batch, stored procedure, or transaction until a specified time or time interval is reached, or a specified statement modifies or returns at least one row.”
Option 1 : Waitfor Delay in executing T-SQL
T-SQL runs after particular delay is completed.
SELECT GETDATE() CurrentTime
WAITFOR DELAY '00:00:05' ---- 5 Second Delay
SELECT GETDATE() CurrentTime
Option 2 : Waitfor Time in executing T-SQL
T-SQL runs after particular time has arrived.
DECLARE @MyDateTime DATETIME
/* Add 5 seconds to current time so
system waits for 5 seconds*/
SET @MyDateTime = DATEADD(s,5,GETDATE())
SELECT GETDATE() CurrentTime
WAITFOR TIME @MyDateTime
SELECT GETDATE() CurrentTime
Reference : Pinal Dave (https://blog.sqlauthority.com)
29 Comments. Leave new
I use this delay to relax the sql server and make it faster between two heavy queries.
I’ve used WAITFOR DELAY in situations requiring polling. For example, somewhere in my batch, I need to wait for an external process to complete (e.g. file shows up on an FTP site). I use WAITFOR in a loop to limit the number of polling queries to, say, once per minute, or once every five minutes.
Waitfor is very usefull if you are settting up a single generic process Queue and you don’t want to ping the database every 5 minutes like most people do to see if a process is running or not. It is far less intrusive to put all your scheduled dates in some meta tables and simply have one job use the WAITFOR command to wait for the next process to execute. After each process is finished, it simply starts that one job again which waits for the next process etc.
I am wanting to use WAITFOR TIME I guess in a similar fashion to you, Andrew. Although mine is on a client server where I don’t have access to SQL Agent, but can request a job be created, which can be a slow process if we need to create/modify jobs. Thought I could have a job run every morning that runs a stored proc, and that proc has the logic of which job/s to run, using WAITFOR TIME. (perhaps your implementation is an even more elegant way of achieving the same outcome…!)
Would this be resource heavy? As it could conceivably wait up to 24 hours max. Thanks.
Thank you!! I was creating several database from a giant T-SQL script and was getting errors red text from SQL that the DB was not created yet. The statement to grab the current time and wait was perfect!!
thanks for helping the greater good!
Thanks Murdoc
esp in a busy network environment where a connection may not be able to connect immediately, a simple waitfor delay to let the network stabilize then try it again…
Thanks Pinal,
Would you have any idea to show only latest data on top in result window on execution of each script in sql query. If I set the waitfor for 5 time with 5 seconds interval, it shows 5 result set – I want only one result set in each execution.
My script is sp_whoisactive
I have 10 SQL queries which has to be executed sequentially as there are lot of update and insert and the next query is dependent on that.
I just want to share this script with customer to run all the 10 query in a new query window and execute it.
is adding delay will suffice