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 (http://blog.SQLAuthority.com)
pinaldave
Have you ever used WAITFOR in a production environment?
I’ve never really seen much of a use for it and I’d be interested to hear ideas of practical uses for it.
For any situations where I want to delay the start of a query (e.g. DBCC commands run outside of core business hours), I’ve always used SQL Server Agent instead.
I have the same questions as John.
I understand the value of the first example to wait X seconds until doing something else, but I don’t get why you would wait until a specific time to execute.
Why not use SQL Agent with a scheduled job?
I assume that second feature simply exists, but that best practices would be to use an agent job?
Is the benefit perhaps for a one time or semi-random AdHoc style situation?
Also how does it handle waiting for a specific time to execute?
Does it check in the background and thus consume resources the entire time it is waiting for the specified date/time or does it have a more efficient way of “sleeping” until it is needed?
We have the use for a waitfor due to our developers writing a process that returns a status code. We will give it 45 seconds, check the results and continue depending on the results.
hi,
I use the WAITFOR command allot in DTS’s while an External Application (WinRar) compacts Access MDB files that have just been created from SQL Views. Once the Delay has timed out I then publish the Zipped files to our website. The WAITFOR command saved me creating lots of different DTS or Batch files can kicking then off at different times.
Cheers Michael