Alternative SqlDependency for non-deterministic functions?

Question

I need to use a getdate() function on SqlDependency but that is part of its limitations. (non-deterministic function)

What alternative do I have for it?

Answer 1

A function will be deterministic, if it returns the same value(s) always - as long as you call it with the same parameters against the same data. It is rather obvious, that functions like GETDATE() or NEWID() will not fullfill this requirement.

The only possibility I know about, is to pass in the non-deterministic value as a parameter. Check this out:

--This will return the current date, but is non-determinisitic
CREATE FUNCTION dbo.TestFunc()
RETURNS DATETIME
WITH SCHEMABINDING
BEGIN
    RETURN GETDATE();
END
GO
--returns 0
SELECT OBJECTPROPERTY(OBJECT_ID('[dbo].TestFunc'), 'IsDeterministic');
GO

--This is deterministic, as it returns nothing else, than the parameter
CREATE FUNCTION dbo.TestFunc2(@CurrentDate DATETIME)
RETURNS DATETIME
WITH SCHEMABINDING
AS
BEGIN
    RETURN @CurrentDate;
END
GO
--returns 1
SELECT OBJECTPROPERTY(OBJECT_ID('[dbo].TestFunc2'), 'IsDeterministic');
GO

--Both return the current date and time
SELECT dbo.TestFunc() AS NonDeterministic
      ,dbo.TestFunc2(GETDATE()) AS Deterministic; 
GO

--Cleaning
DROP FUNCTION dbo.TestFunc;
DROP FUNCTION dbo.TestFunc2;