CODEWITHSUNDEEP
sql-serversql-server-2014
Jonathan Allen
Jonathan Allen

Reputation: 70327

Why is this function non-deterministic?

Why does SQL Server think this function is non-deterministic?

CREATE FUNCTION [Util].[BuildStreetFullName]
(
    @StreetNumber VARCHAR(20),
    @StreetDir VARCHAR(2),
    @StreetName VARCHAR(50) ,
    @StreetType VARCHAR(4) ,
    @StreetPostDir VARCHAR(2) 
)
RETURNS VarChar(100)
AS
BEGIN
    SET @StreetNumber   = NULLIF( Util.Trim(@StreetNumber   ), '');
    SET @StreetDir      = NULLIF( Util.Trim(@StreetDir      ), '');
    SET @StreetName     = NULLIF( Util.Trim(@StreetName     ), '');
    SET @StreetType     = NULLIF( Util.Trim(@StreetType     ), '');
    SET @StreetPostDir  = NULLIF( Util.Trim(@StreetPostDir  ), '');

    DECLARE @Result VarChar(100) =  @StreetNumber;

    IF @StreetDir IS NOT NULL BEGIN
        IF @Result IS NOT NULL
            SET @Result = @Result + ' ' + @StreetDir;
        ELSE
            SET @Result = @StreetDir;
    END

    IF @StreetName IS NOT NULL BEGIN
        IF @Result IS NOT NULL
            SET @Result = @Result + ' ' + @StreetName;
        ELSE
            SET @Result = @StreetName;
    END


    IF @StreetType IS NOT NULL BEGIN
        IF @Result IS NOT NULL
            SET @Result = @Result + ' ' + @StreetType;
        ELSE
            SET @Result = @StreetType;
    END

    IF @StreetPostDir IS NOT NULL BEGIN
        IF @Result IS NOT NULL
            SET @Result = @Result + ' ' + @StreetPostDir;
        ELSE
            SET @Result = @StreetPostDir;
    END

    RETURN NULLIF(@Result, '');
END

CREATE FUNCTION [Util].[Trim]
(
    @value nVarChar(max)
)
RETURNS nVarChar(max)
AS
BEGIN
    RETURN LTrim(RTrim(@value))
END

Upvotes: 1

Views: 577

Answers (1)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

1) You could create both function WITH SCHEMABINDING:

ALTER FUNCTION dbo.[Trim]
(
    @value nVarChar(max)
)
RETURNS nVarChar(max)
WITH SCHEMABINDING
AS
...

and

ALTER FUNCTION dbo.[BuildStreetFullName]
(
    @StreetNumber VARCHAR(20),
    @StreetDir VARCHAR(2),
    @StreetName VARCHAR(50) ,
    @StreetType VARCHAR(4) ,
    @StreetPostDir VARCHAR(2) 
)
RETURNS VarChar(100)
WITH SCHEMABINDING
AS
...

This will force SQL Server to check if these function are deterministic or not.

SELECT  OBJECTPROPERTY( OBJECT_ID(N'dbo.[BuildStreetFullName]') , 'IsDeterministic' ) AS IsDeterministic

Output:

IsDeterministic
---------------
1

2) Please don't use != NULL operator to check for NOT NULL. Use IS NOT NULL. At this moment ANSI_NULLS OFF is deprecated and a a future version of SQL Server will allow only ANSI_NULLS ON.

3) Scalar functions can be a nightmare from performance point of view. I would rewrite those functions as inline table valued functions.

Upvotes: 3

Related Questions