Deterministic Function is Non-Deterministic

sql-server-2008-r2, t-sql

Solution

The solution was to add

WITH SCHEMABINDING

as suggested in this question Sql Server deterministic user-defined function

FUNCTION [dbo].[QDay] 
(
    @Date DATETIME
)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
    RETURN YEAR(@Date)*10000+MONTH(@Date)*100+DAY(@Date)
END

Problem

SQL Server 10.50.1600 I am trying to use a scalar valued function in a computed column. I then wish to create an index off of this. ``` ALTER TABLE [dbo].[Modified] ADD [StartQDate] AS ([dbo].[QDay]([StartDT])) PERSISTED, [EndQDate] AS ([dbo].[QDay]([EndDT])) PERSISTED; ``` I am receiving the following error when I try to create the computed column. Computed column 'StartQDate' in table 'Modified' cannot be persisted because the column is non-deterministic. Except for the fact that my scalar function QDay is defined as ``` FUNCTION [dbo].[QDay] ( @Date DATETIME ) RETURNS INT AS BEGIN RETURN YEAR(@Date)*10000+MONTH(@Date)*100+DAY(@Date) END ``` Which according to Microsoft should be deterministic. Even if I change the function to ``` FUNCTION [dbo].[QDay] ( @Date DATETIME ) RETURNS INT AS BEGIN RETURN 1 END ``` I still get the Non-Deterministic error message. I have this working on another server. I'm at a loss on what to do.

Original source

Related problems