WAITFOR DELAY - Too Much TIME DATETIME On My Hands?

There are certain design patterns in T-SQL that give me pause. They may not be "code smells" per se, but when I encounter them I find myself thinking "there's got to be a more sensible way to accomplish this". WAITFOR DELAY is one example. I've used it a few times here and there, mostly in one-off scripts. If I wrote some code that used it regularly in production, I'd be paranoid about putting my SPID to sleep forever. Maybe a little bit of paranoia is a good thing. But I digress.

A recent task found its way to me, and I've decided to use WAITFOR DELAY as part of my solution. (It hasn't been tested or implemented yet--perhaps more on this in another post.) My usage this time has been more complex than in the past. What I already knew is that you can use a string literal for the time_to_pass argument. For example, this will delay for 3½ seconds:

WAITFOR DELAY '00:00:03.500'

Online documentation states the time_to_pass argument is formatted as hh:mm[[:ss].mss] and that it can be specified as a local variable. But we're not told what data types are valid. "String-type" data types all work, as long as the proper format is maintained (and the variable has enough capacity).

--"String" data types can be used, if formatted as hh:mm[[:ss].mss]
DECLARE @Char CHAR(999) = '00:00:01.5';  
WAITFOR DELAY @Char;

DECLARE @Nchar NCHAR(999) = '00:00:02.5';  
WAITFOR DELAY @Nchar;

DECLARE @Nvarchar NVARCHAR(999) = '00:00:03.5';  
WAITFOR DELAY @Nvarchar;

DECLARE @Varchar VARCHAR(999) = '00:00:04.5';  
WAITFOR DELAY @Varchar;

DECLARE @Sysname SYSNAME = '00:00:05.5';  
WAITFOR DELAY @Sysname;

INT and SMALLINT data types work with WAITFOR DELAY, causing a delay in seconds for the value of the variable. Many other languages have some variation of WAITFOR DELAY (usually as a sleep() function), with the amount of time to sleep specified in milliseconds. TSQL would seem to be out of lock step with other languages in this regard.

--INT and SMALLINT params cause a delay 
--for the specified number of seconds.
DECLARE @Int INT = 1;  
WAITFOR DELAY @Int;

DECLARE @Smallint SMALLINT = 1;  
WAITFOR DELAY @Smallint;

A slew of other data types don't work at all.

--WAITFOR DELAY and WAITFOR TIME cannot be of these types.
DECLARE @Bigint BIGINT = CAST(CURRENT_TIMESTAMP AS BIGINT);  
WAITFOR DELAY @Bigint;
DECLARE @Binary BINARY = CAST(CURRENT_TIMESTAMP AS BINARY);  
WAITFOR DELAY @Binary;
DECLARE @Bit BIT = CAST(CURRENT_TIMESTAMP AS BIT);  
WAITFOR DELAY @Bit;
DECLARE @Date DATE = CAST(CURRENT_TIMESTAMP AS DATE);  
WAITFOR DELAY @Date;
DECLARE @Datetime2 DATETIME2 = CAST(CURRENT_TIMESTAMP AS DATETIME2);  
WAITFOR DELAY @Datetime2;
DECLARE @Datetimeoffset DATETIMEOFFSET = CAST(CURRENT_TIMESTAMP AS DATETIMEOFFSET);  
WAITFOR DELAY @Datetimeoffset;
DECLARE @Decimal DECIMAL = CAST(CURRENT_TIMESTAMP AS DECIMAL);  
WAITFOR DELAY @Decimal;
DECLARE @Float FLOAT = CAST(CURRENT_TIMESTAMP AS FLOAT);  
WAITFOR DELAY @Float;
DECLARE @Money MONEY = CAST(CURRENT_TIMESTAMP AS MONEY);  
WAITFOR DELAY @Money;
DECLARE @Numeric NUMERIC = CAST(CURRENT_TIMESTAMP AS NUMERIC);  
WAITFOR DELAY @Numeric;
DECLARE @Real REAL = CAST(CURRENT_TIMESTAMP AS REAL);  
WAITFOR DELAY @Real;
DECLARE @Smalldatetime SMALLDATETIME = CAST(CURRENT_TIMESTAMP AS SMALLDATETIME);  
WAITFOR DELAY @Smalldatetime;
DECLARE @Smallmoney SMALLMONEY = CAST(CURRENT_TIMESTAMP AS SMALLMONEY);  
WAITFOR DELAY @Smallmoney;
DECLARE @Sql_variant SQL_VARIANT = CAST(CURRENT_TIMESTAMP AS SQL_VARIANT);  
WAITFOR DELAY @Sql_variant;
DECLARE @Time TIME = CAST(CURRENT_TIMESTAMP AS TIME);  
WAITFOR DELAY @Time;
DECLARE @Timestamp TIMESTAMP = CAST(CURRENT_TIMESTAMP AS TIMESTAMP);  
WAITFOR DELAY @Timestamp;
DECLARE @Tinyint TINYINT = CAST(1 AS TINYINT);  
WAITFOR DELAY @Tinyint;
DECLARE @Uniqueidentifier UNIQUEIDENTIFIER = NEWID();  
WAITFOR DELAY @Uniqueidentifier;
DECLARE @Varbinary VARBINARY = CAST(CURRENT_TIMESTAMP AS VARBINARY);  
WAITFOR DELAY @Varbinary;
DECLARE @Xml XML = '<sys.objects Test="1" />';  
WAITFOR DELAY @Xml;

All of the above result in this error message:

Msg 9815, Level 16, State 0, Line 2
Waitfor delay and waitfor time cannot be of type <data type>.

For some of those data types, the error makes sense. But not for the number-type data types. INT and SMALLINT are viable, so why not BIGINT, TINYINT, et al? The date and/or time data types don't make a lot of sense either, because...

The DATETIME data type is compatible with WAITFOR DELAY. The date part is ignored, and the time portion becomes the value for the time_to_pass argument:

DECLARE @DateTime DATETIME = '1900-01-01 00:00:03.500'

PRINT CONVERT(VARCHAR, CURRENT_TIMESTAMP, 121)
--Delay for 3½ seconds. (The date is ignored.)
WAITFOR DELAY @DateTime
PRINT CONVERT(VARCHAR, CURRENT_TIMESTAMP, 121)

Output:
2018-10-28 20:30:21.797
2018-10-28 20:30:25.297

Knowing that the date part of a DATETIME data type is ignored, and that "strings" must be in the format hh:mm[[:ss].mss], it makes little sense for TIME data types to be incompatible. And if DATETIME is OK, why not DATETIME2 and SMALLDATETIME?

I think I'll always be hesitant to use WAITFOR DELAY. But if I have to, using an INT or SMALLINT data type to specify the number of seconds for time_to_pass will probably be my first choice. It's closest in spirit to the "sleep" functions of other languages. And it's arguably the least ambiguous.

Tick, tock. Tick, tock. The clock's ticking. Go write some code!