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.
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?
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.