How does sql server determine if a function is deterministic or not?

Consider the following function, I think it's deterministic (as it's ultimately an integer operation), but sql server returns 0 when I call the objectproperty method on it.

Understanding how it works will help me understand why I'm wrong in my assessment of this function.

CREATE FUNCTION dbo.EndOfPreviousMonth ( @Date date )
    RETURN DATEADD(day, 0 - DAY(@Date), @Date);

SELECT dbo.EndOfPreviousMonth('2010-01-01'); --Usage example

--Returns 0, I expected it to return 1
SELECT OBJECTPROPERTY( OBJECT_ID( 'dbo.EndOfPreviousMonth' ), 'IsDeterministic' );
  • Are you running in compatibility mode for anything? (Ignore previous, DATEADD is deterministic.) Feb 9, 2012 at 22:31
  • No, I don't think so. I guess that's to say I didn't set a compatibility mode. Feb 10, 2012 at 13:33

1 Answer 1


First it looks whether used functions are deterministic.
Then it looks whether your function is schema bound.
(Or in reverse order.)

So add with schemabinding to your function.

  • That changed the answer to what I was expecting, but why? Feb 10, 2012 at 13:34
  • @BrianDishaw I'm not sure, to be honest. Apparently because any data-accessing function is not deterministic unless schema bound, and SQL Server expands this behaviour to functions that don't in fact access data. Maybe it's not able to see they don't.
    – GSerg
    Feb 10, 2012 at 14:24
  • I don't understand it either. It would make more sense if I had something in the function that I could drop. Oh well, thanks for taking the time to answer my question! Feb 10, 2012 at 14:43
  • 2
    Explained here
    – Paul White
    Dec 15, 2014 at 6:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.