Sometimes when running processes in SQL Server, there may be a need to create delays before the next step runs. This could be for processes that are run externally and therefore you have no control over when that process finishes, the need to mimic a delay in user response if you are doing testing, or maybe you are collecting data at various intervals and want to delay the next collection time. In this article, we will explore different approaches to create delays in SQL Server.

Using the WAITFOR Statement

SQL Server offers the WAITFOR statement that allows you to wait for a specified amount of time or until a specified time to process the next step in the sequence of events. The command can be simply written as follows:

WAITFOR DELAY '00:00:30' -- this will delay the processing for 30 seconds
WAITFOR DELAY '01:15:30' -- this will delay the processing for 1 hour, 15 minutes and 30 seconds
WAITFOR TIME '08:00' -- this will delay the processing until 8:00am

With the WAITFOR statement, you can run code to get the number of connections on the server, delay the process, and then get the data again. However, note that the results are not shown until the process completes. If you want to get data back as soon as it completes, you can use the GO statement to separate the queries:

select getdate(), count(*) as 'NumberOfProcesses' from sys.sysprocesses;
GO
WAITFOR DELAY '00:00:15'
select getdate(), count(*) as 'NumberOfProcesses' from sys.sysprocesses;
GO

To mimic user input for testing, you can issue queries with delays in between:

INSERT INTO table1 (FirstName) VALUES ('Brian');
WAITFOR DELAY '00:00:05'
SELECT * FROM table1 WHERE FirstName = 'Brian';
WAITFOR DELAY '00:00:05'
INSERT INTO table1 (FirstName) VALUES ('Tim');
WAITFOR DELAY '00:00:15'
INSERT INTO table1 (FirstName) VALUES ('Steve');
WAITFOR DELAY '00:00:05'
SELECT * FROM table1 WHERE FirstName IN ('Brian','Tim');
WAITFOR DELAY '00:00:15'
INSERT INTO table1 (FirstName) VALUES ('Andy');

Using a WHILE Loop

Although using the WAITFOR statement is the simplest way to create delays, another approach could be to use a WHILE loop. Here are a few examples:

1. Using a WHILE loop and quitting after the counter hits a certain number:

DECLARE @counter int
SET @counter = 0
WHILE @counter < 10000000
   SET @counter = @counter + 1

2. Checking the current date/time against a date and time that you set:

DECLARE @startTime datetime
DECLARE @dummy int
SET @startTime = '2008-01-29 19:53:00'
WHILE getdate() < @startTime
   SET @dummy = 0

3. Waiting for a specified amount of seconds using the DATEADD function and comparing it to the current date and time:

DECLARE @startTime datetime
DECLARE @delay int
DECLARE @dummy int
SET @delay = 30
SET @startTime = DATEADD(s, 30, getdate())
WHILE getdate() < @startTime
   SET @dummy = 0

These are a few ways that you can create delays in your SQL Server processing. Another approach could be to use scheduled jobs if you have long delays between processing.