SQL SERVER – Database Testing and Waitfor Delay – Notes from the Field #099

[Note from Pinal]: This is a 99th episode of Notes from the Field series. In this episode we are going to learn something very simple but effective about database testing. Database testing is one of the most critical elements for any developer or DBA. If you do not test your code, there are good chances to make mistakes. However, sometime testing requires some advanced tricks.

In this episode of the Notes from the Field series database expert Kevin Hazzard explains the how to use command Waitfor Delay for our advantage when doing database testing. Read the experience of Kevin in his own words.


Database testing has become something of an obsession for us at Linchpin People. To test ETL processes, I often write mocking procedures that can simulate data flowing in from clients. When I first began doing this years ago, I’d set up SQL Agent jobs to invoke the mocking procedures on a regular frequency. However, that’s fairly cumbersome to set up so I started writing scripts like this instead:

WAITFOR DELAY '00:00:03';
EXEC sp_InsertMockTransaction;
GO 100

This code uses the WAITFOR command which many developers already understand. If you’ve never seen or heard of WAITFOR before, it’s fairly straightforward. In the code shown above, the DELAY option is used to induce a delay of three seconds. Afterwards, the mocking procedure runs.

The last line of the script is not as familiar to developers, I’ve found. We’ve all seen and used the GO phrase but as it turns out, it’s not part of the T-SQL language. GO is a batch separator that tells SSMS or the SQLCMD processor to forward the current batch of commands to an instance of SQL Server. What many developers don’t realize is that the GO batch separator can accept a positive integer parameter that will loop over the batch that many times.

In SSMS, the code shown above will invoke the mocking procedure 100 times, pausing for three seconds in between. This is a simple but effective way to simulate the input I’ll need for my tests. Of course, this pattern comes in handy for all sorts of uses, not just testing. Hopefully you’ll find simple trick as useful as I have.

If you want to get started with SQL Server with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Menu
Exit mobile version