Non Deterministic UDF

• Green Armchair

  Hall of Fame

  Points: 3561

  More actions

  July 21, 2003 at 8:51 am

  #81205

  Hi

  Why is the following UDF nondeterministic?

  set ANSI_NULLS on

  set QUOTED_IDENTIFIER on

  go

  CREATE FUNCTION CREATE_DISP_DATE (@SOURCE_COLUMN decimal(20,6))

  RETURNS datetime AS

  BEGIN

  RETURN (convert(datetime,left(stuff(stuff(stuff(@source_column,9,0,' '),12,0,':'),15,0,':'),17),113))

  END

  According to BOL, all the functions I am using are deterministic. When I try to create an index based on a computed column based on this UDF, I can't because "Cannot create index because the key column 'DISP_CRET_TMSTMP1' is non-deterministic or imprecise."

  Thanks

  Andy

• mccork

  SSC-Insane

  Points: 22288

  More actions

  July 21, 2003 at 9:40 pm

  #466315

  Another criterion for a deterministic function is that it's schema bound. Just add WITH SCHEMABINDING. Eg.

   
  
  
  
  drop FUNCTION CREATE_DISP_DATE
  
  go
  
  set ANSI_NULLS on
  
  set QUOTED_IDENTIFIER on
  
  go
  
  create FUNCTION CREATE_DISP_DATE (@SOURCE_COLUMN decimal(20,6))
  
  RETURNS datetime WITH SCHEMABINDING AS 
  
  BEGIN 
  
  RETURN (convert(datetime,left(stuff(stuff(stuff(@source_column,9,0,' '),12,0,':'),15,0,':'),17),113))
  
  END
  
  go
  
  select objectproperty(object_id('CREATE_DISP_DATE'), 'IsDeterministic')
  
  

  
  Cheers,
  - Mark

• Green Armchair

  Hall of Fame

  Points: 3561

  More actions

  July 22, 2003 at 4:07 am

  #466349

  mccork

  Thanks very much for this, its solved my problem.

  Andy