User-Defined Functions SQL Server 2005 flagged inc

问题:

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?

回答1:

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.