Putting SQL to Sleep

Have you ever wanted to test how your application would handle a long running stored procedure, but didn't want to create a multi-gigabyte database to do so?  Add a WAITFOR statement. 

WAITFOR is similar to .NET's Thread.Sleep() in which you can stop a thread from processing for a specified period of time.  You can either pass WAITFOR a delay time for the batch to pause for or a specific time for the batch to continue afterwards as follows:

WAITFOR DELAY '00:00:45' --Delays the batch for 45 seconds.

WAITFOR TIME '12:30'  --Restarts the batch at the next line at 12:30.

Note that there is no date specified in the WAITFOR TIME statement.  You can only delay it within a 24 hour period using the TIME clause.

One thing to watch out for are transactions.  If you put a WAITFOR within a transaction, any locked resources will remain so for the duration of the delay, potentially causing timeouts and deadlocks.

WAITFOR is also used for waiting for Service Broker messages, but that's a whole other blog entry.

I'm sure there are other uses for WAITFOR but for now, happy napping!