Make a T-SQL Query Sleep for a certain amount of time

At times, in test scenarios, you may want to emulate that a transaction or a T-SQL query takes ‘x’ amount of time. Well that is possible (although not with precision) in SQL Server (2005/2008) using the WAITFOR command.

Here’s an example of delaying query execution till a specified time interval has elapsed – let us say 10 seconds

USE Northwind
WAITFOR DELAY '00:00:10';
SELECT LASTNAME, FIRSTNAME FROM Employees;

OUTPUT

As you can see, we used the WAITFOR statement with a DELAY option to delay code execution for 10 seconds.

There are a couple of things you should note about the WAITFOR statement

- WAITFOR does not accept date values, only time values are allowed

- In some scenarios, the delay specified by you may not exactly the same as the delay caused by WAITFOR