How can I design a query to pause / sleep / wait for a specified amount of time?
If you are looking for a function to block the execution batch for a specified amount of time, you can use WAITFOR in SQL Server 2000. This function is similar to SLEEP function in Oracle. Usage examples:

WAITFOR DELAY '00:00:30';     -- wait for 30 seconds
WAITFOR TIME '13:30';         -- wait until server time is 13:30