Deterministic Function is Non-Deterministic

Question

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.

Why use a scalar function at all? `EndQDate As Year(EndDT)*10000+Month(EndDT)*100+DAY(EndDT)`? — gvee, Nov 27 '13 at 16:04
Maybe `DATETIME` is not deterministic? The page you linked indicates that other functions are deterministic unless used with `DATETIME`, and `DATETIME` is not listed in the guaranteed deterministic functions. — taz, Nov 27 '13 at 16:06
Possibly relevant: http://stackoverflow.com/questions/3037330/why-is-date-time-offset-non-deterministic-in-sql-server-2008 — taz, Nov 27 '13 at 16:09

score 5 · Accepted Answer · edited May 23 '17 at 12:12

5

The solution was to add

WITH SCHEMABINDING

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

edited May 23 '17 at 12:12

Community

answered Nov 27 '13 at 16:22

awptimus

1 Answers1