CODEWITHSUNDEEP
sqlsql-serversql-server-2005user-defined-functionsdeterministic
Cade Roux
Cade Roux

Reputation: 89721

User-Defined Functions SQL Server 2005 flagged incorrectly as non-deterministic?

Related to this question, I decided to check the UDFs in my data warehouse (which should largely have been deterministic), and I found several which aren't which should be.

For instance:

CREATE FUNCTION [udf_YearFromDataDtID]
(
    @DATA_DT_ID int
)
RETURNS int
AS
BEGIN
    RETURN @DATA_DT_ID / 10000
END

Shows up in this query:

SELECT  ROUTINE_NAME
FROM    INFORMATION_SCHEMA.ROUTINES
WHERE   IS_DETERMINISTIC = 'NO'
        AND ROUTINE_TYPE = 'FUNCTION'
ORDER BY ROUTINE_NAME

Why is this?

Upvotes: 4

Views: 1649

Answers (1)

Cade Roux
Cade Roux

Reputation: 89721

Yikes - apparently, it REQUIRES SCHEMABINDING to be specified other wise it could cause performance problems

ALTER FUNCTION [udf_YearFromDataDtID]
(
    @DATA_DT_ID int
)
RETURNS int
WITH SCHEMABINDING
AS
BEGIN
    RETURN @DATA_DT_ID / 10000
END

Looks like I could be getting some performance boosts by fixing these. Nice.

Upvotes: 5

Related Questions