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?

Answers


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.


Need Your Help

Portable serialisation of IEEE754 floating-point values

c floating-point portability ieee-754 endianness

I've recently been working on a system that needs to store and load large quantities of data, including single-precision floating-point values. I decided to standardise on network byte order for