Why does SQL Server say this function is nondeterministic?

Question

Execute this function in T-SQL:

CREATE FUNCTION [dbo].[Parse_URI_For_Scheme](
         @URI nvarchar(4000))
RETURNS nvarchar(250)
WITH SCHEMABINDING
AS
BEGIN

   DECLARE @temp_string varchar(4000)
   DECLARE @return_string nvarchar(250)
   DECLARE @pos int

   SET @pos = CHARINDEX('://', @URI);
   --select @pos
   IF @pos > 0
      BEGIN
         SET @temp_string = SUBSTRING(@URI, 0, @pos);

         -- SET @pos = CHARINDEX('/', @temp_string)

         IF @pos > 0
            BEGIN
               SET @temp_string = LEFT(@temp_string, @pos - 1);

               SET @pos = CHARINDEX('@', @temp_string);

               IF @pos > 0
                  SET @return_string = SUBSTRING(@temp_string, @pos + 1, 250);
               ELSE
                  SET @return_string = @temp_string;
            END
         ELSE
            SET @return_string = '';
      END
   ELSE
      SET @return_string = '';

   RETURN @return_string;

END;

Then execute this command which returns 0:

SELECT OBJECTPROPERTY(OBJECT_ID('[dbo].Parse_URI_For_Scheme'), 'IsDeterministic')

Can someone please tell me why this is not a deterministic function?

Answer 1

One of the key points for SQL Server to mark a function as Deterministic is the SchemaBinding feature. For your function to be deterministic you need to define the function using With SchemaBinding.

In Your example if you remove the With SchemaBinding, the ObjectProperty function will return 0 for the IsDeterministic attribute, so by adding the With SchemaBinding the problem will be resolved for you

@Paul has detailed explanation around this issue, here