July 21, 2003 at 8:51 am
Hi
Why is the following UDF nondeterministic?
set ANSI_NULLS on
set QUOTED_IDENTIFIER on
go
CREATE FUNCTION CREATE_DISP_DATE (@SOURCE_COLUMN decimal(20,6))
RETURNS datetime AS
BEGIN
RETURN (convert(datetime,left(stuff(stuff(stuff(@source_column,9,0,' '),12,0,':'),15,0,':'),17),113))
END
According to BOL, all the functions I am using are deterministic. When I try to create an index based on a computed column based on this UDF, I can't because "Cannot create index because the key column 'DISP_CRET_TMSTMP1' is non-deterministic or imprecise."
Thanks
Andy
July 21, 2003 at 9:40 pm
Another criterion for a deterministic function is that it's schema bound. Just add WITH SCHEMABINDING. Eg.
drop FUNCTION CREATE_DISP_DATE
go
set ANSI_NULLS on
set QUOTED_IDENTIFIER on
go
create FUNCTION CREATE_DISP_DATE (@SOURCE_COLUMN decimal(20,6))
RETURNS datetime WITH SCHEMABINDING AS
BEGIN
RETURN (convert(datetime,left(stuff(stuff(stuff(@source_column,9,0,' '),12,0,':'),15,0,':'),17),113))
END
go
select objectproperty(object_id('CREATE_DISP_DATE'), 'IsDeterministic')
Cheers,
- Mark
July 22, 2003 at 4:07 am
mccork
Thanks very much for this, its solved my problem.
Andy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply