SQL SERVER :Delaying Sql Execution-Use of WAITFOR Cluse

--Example:-1

--WHILE LOOP

--USE WAITFOR FOR DELAYING THE EXECUTION FOR 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

GO

WAITFOR DELAY '00:00:02'

SELECT 'THIS IS DB BLOG'

GO

Descriptions:-

It block/slow down the execution Process of any sql statement(Procedure/Commands).The delay specified period of time that must pass, up to a maximum of 24 hours, before execution of a batch, stored procedure, or transaction proceeds. While executing the WAITFOR statement, the transaction is running and no other requests can run under the same transaction.

The actual time delay may vary from the time to time specified and depends on the activity level of the server. The time counter starts when the thread associated with the WAITFOR statement is scheduled. If the server is busy, the thread may not be immediately scheduled; therefore, the time delay may be longer than the specified time.

WAITFOR does not change the semantics of a query. If a query cannot return any rows, WAITFOR will wait forever or until TIMEOUT is reached, if specified. Cursors cannot be opened on WAITFOR statements and Views cannot be defined on WAITFOR statements.

Every WAITFOR statement has a thread associated with it. If many WAITFOR statements are specified on the same server, many threads can be tied up waiting for these statements to run. SQL Server monitors the number of threads associated with WAITFOR statements, and randomly selects some of these threads to exit if the server starts to experience thread starvation.

Conclusion:- You can create a situation for Deadlock Condition.