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!