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?

Just as an aside, why not just set return_string to empty string immediately after you declare it? You can remove some of your else's that do nothing but that, making it a bit easier to follow. — Ellesedil, Jan 26 '18 at 19:49
You might be better suited turning this into an inline table valued function instead of a scalar function. — Sean Lange, Jan 26 '18 at 19:51
Same here, the function is deterministic... (returns `1`). Are you sure to check the correct database / version / schema? Are there transactions involved? — Shnugo, Jan 26 '18 at 20:21

score 1 · Accepted Answer · answered Jan 26 '18 at 19:54

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

score 0 · Answer 2 · answered Jan 30 '18 at 01:58

Vahid's gave a great reply. I agree with everything he said and love the link to the Paul White comment which is excellent.

A couple things to note:

1. The logic you're using is overly complex for what you are trying to do.

I think you could get away with:

SUBSTRING(@URI, 1, ISNULL(NULLIF(CHARINDEX('://', @URI)-1,0),''))

2. If you must have a function for this task use an inline Table Valued Function (iTVF).

This advice changed my career and what I've learned over the years is that there are two kinds of T-SQL functions: iTVFs and very slow functions (e.g. Scalar and multi-statement table valued functions). Here's more great reading from Paul White on this topic: Understanding and Using APPLY (Part 1).

Let's turn your function into a high-performing, parallelism-enabled DETERMINISITC function.

CREATE FUNCTION dbo.parse_uri_for_scheme_itvf(@URI nvarchar(4000))
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT newURI = SUBSTRING(@URI, 1, ISNULL(NULLIF(CHARINDEX('://', @URI)-1,0),''));

and a performance test to demonstrate why scalar functions are not for people in a hurry.

Sample Data

SELECT string = cast(pr+'://'+samples.txt as nvarchar(4000))
INTO #strings
FROM (VALUES ('http'),('https'),('ftp')) pr(pr)
CROSS JOIN 
(
  SELECT replicate(newid(), abs(checksum(newid())%2)+1)
  FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) a(x),
       (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) b(x),
       (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) c(x),
       (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) d(x),
       (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) e(x)
) samples(txt);

Perf Test

SET NOCOUNT ON;
PRINT 'Scalar'+char(10)+replicate('-',60);
GO
DECLARE @st datetime = getdate(), @txt nvarchar(4000);
SELECT @txt = dbo.Parse_URI_For_Scheme(t.string)
FROM #strings t;
PRINT datediff(ms,@st,getdate())
GO 3

PRINT 'iTVF'+char(10)+replicate('-',60);
GO
DECLARE @st datetime = getdate(), @txt nvarchar(4000);
SELECT @txt = itvf.newURI
FROM #strings t
CROSS APPLY dbo.parse_uri_for_scheme_itvf(t.string) itvf;
PRINT datediff(ms,@st,getdate())
GO 3

Results

Scalar
------------------------------------------------------------
Beginning execution loop
1423
1380
1360
Batch execution completed 3 times.

iTVF
------------------------------------------------------------
Beginning execution loop
423
427
437
Batch execution completed 3 times.

I knew the iTVF would be 3-4 times faster before I prepared the test.

Why does SQL Server say this function is nondeterministic?

2 Answers2

Linked