SQL Server: Delaying SQL Execution Using WAITFOR

WAITFOR in SQL Server
 

Example 1 – WHILE Loop


-- USE WAITFOR TO DELAY EXECUTION FOR A SPECIFIED TIME

GO

DECLARE @T INT

SET @T = 1

WAITFOR DELAY '00:00:10'

WHILE @T <= 10
BEGIN
    PRINT 'MANOJ_KUMAR'

    SET @T = @T + 1
END

GO

Example 2 – WAITFOR DELAY


GO

WAITFOR DELAY '00:00:02'

SELECT 'THIS IS DB BLOG'

GO

Description

`WAITFOR` is used in SQL Server to delay the execution of a batch, stored procedure, or transaction for a specified period of time.

The delay can be specified using `WAITFOR DELAY`, with the maximum delay being up to 24 hours.

The actual delay may be slightly longer than the specified time, depending on the activity and available resources on the SQL Server.

Every `WAITFOR` statement requires a thread while it is waiting. If too many `WAITFOR` statements are running at the same time, they can consume server resources and may contribute to thread starvation.

`WAITFOR` does not change the semantics of a query. It simply makes the execution wait for the specified period before continuing.

Conclusion

`WAITFOR` can be useful for testing scenarios where you need to introduce a delay in SQL Server. It can also be used to simulate certain blocking or timing situations during troubleshooting and testing.