Delay Function in SQL Server

The Delay function used to execution of a batch in block, stored procedure, or transaction until a specified time or time interval is reached, or a specified statement modifies or returns at least one row.

While executing the WAITFOR statement, the transaction is running and no other requests can run under the same transaction.

Remarks:

Ø  If a query cannot return any rows, WAITFOR will wait forever or until TIMEOUT is reached, if specified.

Ø  WAITFOR statements not used in Views and Cursor.

Ø  When the query exceeds the query wait option, the WAITFOR statement argument can complete without running. You can create a Deadlock by running a query with WAITFOR.

Example:

SELECT GETDATE() CurrentDateTime
WAITFOR DELAY '00:00:05' ---- 5 Second Delay
SELECT GETDATE() CurrentDateTime

Note: You can also perform update, DB Backup and Restore and many important operation easily using this command.