XPost: microsoft.public.sqlserver.programming   
   From: genew@ocis.net   
      
   On Thu, 06 Jan 2011 00:30:54 +0100, Hugo Kornelis   
    wrote:   
      
   >On Wed, 05 Jan 2011 14:55:30 -0800, Gene Wirchenko    
   >wrote:   
   >   
   >(snip)   
   >>     What gives?  Why is WITH SCHEMABINDING required?   
      
   >Consider this simple example:   
   >   
   >CREATE VIEW dbo.v1 WITH SCHEMABINDING   
   >AS SELECT CAST(1 AS int) AS a;   
   >go   
   >CREATE FUNCTION dbo.f2()   
   >RETURNS TABLE --WITH SCHEMABINDING   
   >AS RETURN (SELECT a FROM dbo.v1) ;   
   >go   
   >SELECT * FROM dbo.f2();   
   >SELECT * FROM dbo.f2();   
   >go   
   >SELECT OBJECTPROPERTYEX(OBJECT_ID('dbo.f2'), 'IsDeterministic')   
   >SELECT OBJECTPROPERTYEX(OBJECT_ID('dbo.f2'), 'IsPrecise')   
   >go   
   >ALTER VIEW dbo.v1   
   >AS SELECT CAST(CURRENT_TIMESTAMP AS float) AS a;   
   >go   
   >SELECT * FROM dbo.f2();   
   >SELECT * FROM dbo.f2();   
   >go   
   >DROP FUNCTION dbo.f2;   
   >DROP VIEW dbo.v1;   
   >go   
   >   
   >Without schemabinding, there is no link between the function and the   
   >underlying view. When the view changes, the properties of the function   
   >would not change, but they would no longer be correct.   
      
        Why not?   
      
        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.   
      
   Sincerely,   
      
   Gene Wirchenko   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)