:::: MENU ::::

Make required delay in T-SQL with WAITFOR DELAY

  • Jun 06 / 2009
  • 0
dbDigger, T-SQL Tips and Tricks

Make required delay in T-SQL with WAITFOR DELAY

For many tasks some delay between tasks is required. For such required delay use WAITFOR DELAY option. It may be used on all versions of SQL Server. Syntax for this option is

 
WAITFOR   
 {  
 DELAY 'time_to_pass'   
 | TIME 'time_to_execute'   
 | [ ( receive_statement ) | ( get_conversation_group_statement ) ]   
 [ , TIMEOUT timeout ]  
 }  

For example to execute two T-SQL statements with a delay of 5 seconds we have following example.

 
SELECT 'Written before delay' as BeforeDelay  
 GO  
 WAITFOR DELAY '00:00:05'   
 GO  
 SELECT 'Written after delay' as AfterDelay  
 GO  

Executing above script will display two results with a difference of 5 seconds. For another example we may use it for a single GO in following way.

SELECT 'Displayed after delay' as AfterDelay  
 WAITFOR DELAY '00:00:05'   
 GO  

In this way whole statement will be executed with 5 seconds delay.
WAITFOR DELAY is a powerful option and needs to be used carefully. It may generate dead lock if not used properly. Especially while using it in step with other T-SQL statement as in case of our second example. Some limited and safe use of this option may be

  • Use WAITFOR DELAY as a job step between other job steps to provide a delay between DML operations and backups. It will reduce load on resources.
  • Similarly use WAITFOR DELAY between GO statements of any T-SQL script for delay.

Keeping in view the basic functionality you may use this option as required, but for complicated dependent tasks it would be better to use SQL Srever agent scheduling tools or service broker for different versions of SQL Server.

Consult us to explore the Databases. Contact us