XPost: microsoft.public.sqlserver.programming   
   From: genew@ocis.net   
      
   On Thu, 06 Jan 2011 13:35:47 +0100, Erland Sommarskog   
    wrote:   
      
   >Gene Wirchenko (genew@ocis.net) writes:   
   >>      I thought that the determination was if the input parameters are   
   >> the same and the database state is the same, then yes.  To me,   
   >> changing the database state would mean that all bets are off.   
   >>   
   >>           create function dbo.contrived()   
   >>           returns int   
   >>           as return 1   
   >>   
   >>      The above function is supposedly not deterministic.  It does not   
   >> touch any database!  The example that got me going was an interest   
   >> calculation function that took rate, amount, from date, and to date.   
   >> It did not touch a database either.   
   >   
   >I guess it is a limitation or a simplification.   
   >   
   >WITH SCHEMABINDING gives a required, but not sufficient condition, that   
   >the function is deterministic. That is simply what Microsoft has decided.   
   >They could maybe have designed it differently, but then they would have   
   >had to run more complex analysis when the function is used, and when the   
   >function is altered or dropped.   
      
        I am wondering what sort of analysis they do.  I think that they   
   can not do a complete analysis: it has the feel of trying to solve the   
   halting problem.  I hope that they err on the side of calling   
   something nondeterministic when it is rather than the reverse, but   
   this example of doing so does seem strange.   
      
   >To the price that we users have to add WITH SCHEMABINDING to make our   
   >function deterministic. A fairly small price, I would say.   
      
        But a gotcha until one knows.  The text I am following did not   
   have this point.  Fortunately, I found an answer on the Web as to what   
   (but not why).  There are too many gotchas in this field, and the   
   sheer mass of them is a high price.   
      
   Sinerely,   
      
   Gene Wirchenko   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)