How does objectproperty() figure out if a function is deterministic in sql server?

Question

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 )
RETURNS date
AS BEGIN
 RETURN DATEADD(day, 0 - DAY(@Date), @Date);
END;
SELECT dbo.EndOfPreviousMonth('2010-01-01'); --Usage example
--Returns 0, I expected it to return 1
SELECT OBJECTPROPERTY( OBJECT_ID( 'dbo.EndOfPreviousMonth' ), 'IsDeterministic' );

• Simon Righarts: Are you running in compatibility mode for anything? (Ignore previous, DATEADD is deterministic.)
• Brian Dishaw: No, I don't think so. I guess that's to say I didn't set a compatibility mode.

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.

• Brian Dishaw: That changed the answer to what I was expecting, but why?
• GSerg: @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.
• Brian Dishaw: 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!
• Paul White: Explained here