WAITFOR is a powerful command in SQL Server that allows you to introduce delays in your code execution. It can be used to synchronize multiple query windows, test system behavior, or create artificial delays for better concurrency. In this article, we will explore how to use variables and configuration tables with WAITFOR to make it more flexible and efficient.

Using Variables with WAITFOR

By default, WAITFOR commands use hard-coded string literals to specify the delay or time. However, you can also use variables to define the delay dynamically. For example, instead of using WAITFOR DELAY '00:00:05', you can use DECLARE @seconds tinyint = 5; DECLARE @delay varchar(8) = '00:00:' + RIGHT(CONCAT('0', @seconds), 2); WAITFOR DELAY @delay;. This allows you to easily change the delay value without modifying the code in multiple places.

It’s important to note that if you want to define a delay longer than 59 seconds, you’ll need a more robust solution to convert seconds to HH:mm:SS format. Additionally, WAITFOR TIME accepts time without a date component, so be cautious when specifying a time that is too soon.

Using Configuration Tables with WAITFOR

If you have multiple query windows or want to experiment with different delay values, using variables alone may not be sufficient. In such cases, you can leverage configuration tables to store the start time and delay values. This allows you to make changes in a single place and have them impact all instances in the script.

To use a configuration table, you can create a table with columns for StartTime and DelaySeconds. For example:

CREATE TABLE dbo.TestConfig
(
   StartTime     varchar(20),
   DelaySeconds  decimal(6,2),
   CONSTRAINT    CK_Time  CHECK (TRY_CONVERT(datetime, StartTime) IS NOT NULL),
   CONSTRAINT    CK_Delay CHECK (DelaySeconds BETWEEN 0 AND 3599) 
);

INSERT dbo.TestConfig(StartTime, DelaySeconds)
VALUES('16:20', 1.25);

In each query window, you can then retrieve the values from the configuration table and use them in the WAITFOR commands. This ensures consistency across all instances. For example:

DECLARE @timeString  varchar(20), 
        @delayString varchar(11);

SELECT @timeString  = StartTime,
       @delayString = CONCAT_WS(':',
       RIGHT(CONCAT('0', CONVERT(int, DelaySeconds) / 6000), 2), 
       RIGHT(CONCAT('0', CONVERT(int, DelaySeconds) / 60),   2),
       RIGHT(CONCAT('0', DelaySeconds % 60), 5))
FROM dbo.TestConfig;

WAITFOR TIME @timeString;

-- Rest of the code

With this approach, you can easily modify the start time and delay values in the configuration table, and the changes will be reflected in all query windows without the need to modify the code in multiple places.

Conclusion

Using variables and configuration tables with WAITFOR can greatly enhance the flexibility and efficiency of your SQL Server code. By dynamically defining delays and centralizing configuration values, you can easily make changes in a single place and have them impact all instances in your script. This technique is particularly useful when synchronizing multiple query windows or experimenting with different delay values.

While WAITFOR is a niche feature, exploring its lesser-known capabilities can uncover more broadly useful techniques. Additionally, it’s important to consider the real-world impact of other performance factors, such as log write throttling, when assessing the effectiveness of features like optimized locking.

Remember to always test and evaluate the impact of any changes in a controlled environment before implementing them in production.

Article Last Updated: 2023-12-11