WAITFOR FUNCTION IN SQL SERVER 2005

It 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.

Example of WAITFOR

WAITFOR ’00:00:10′
SELECT * FROM EMPLOYEE

GO

Notes:

1.WAITFOR will not work with Cursors and Views.

2.The actual time delay may vary from the time specified in
time_to_pass, time_to_execute, or timeout and depends on the activity
level of the server.

3.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.

4.When the query exceeds the querywait option, the WAITFOR statement argument can complete without running.
You can check all active and waiting processes from sp_who.

5.Each WAITFOR statement creates a thread.

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.

Pawan Kumar
Pawankkmr@hotmail.com