Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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' );
share|improve this question
    
Are you running in compatibility mode for anything? (Ignore previous, DATEADD is deterministic.) –  Simon Righarts Feb 9 '12 at 22:31
    
No, I don't think so. I guess that's to say I didn't set a compatibility mode. –  Brian Dishaw Feb 10 '12 at 13:33

1 Answer 1

up vote 5 down vote accepted

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.

share|improve this answer
    
That changed the answer to what I was expecting, but why? –  Brian Dishaw Feb 10 '12 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 '12 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! –  Brian Dishaw Feb 10 '12 at 14:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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