November 17, 2009 at 9:35 am
I'm having trouble creating a deterministic function.
For a bit of background, I had to add to a table a computed column whose formula was based on a function.
But the table had an indexed view on it. When I tried to recreate the indexed view, I got complaints that
Cannot create index on view "xxx.dbo.xxx" because function "dbo.fnxxx" referenced by the view performs user or system data access. I tried commenting out bits of my function to see which bit SQL objected to. But eventually I was left with nothing, and SQL was still complaining. I then looked at OBJECTPROPERTY IsDeterministic to see if my function was - and it wasn't! Trouble is I can't see to make even the simplest function Deterministic now.
Can anyone see what I'm doing wrong?
Thanks,
David.
CREATE FUNCTION dbo.fnTest ()
RETURNS int
AS
BEGIN
RETURN 1
END
GO
select OBJECTPROPERTY (object_id('fnTest'),'IsDeterministic')
November 17, 2009 at 10:45 am
Try this:
CREATE FUNCTION dbo.fnTest ()
RETURNS int
WITH SCHEMABINDING
AS
BEGIN
RETURN 1
END
GO
November 18, 2009 at 1:31 am
Hi Lynn,
Thanks for that - it worked! (But I don't remember this being a requirement for a deterministic function!?)
I was then faced with the problem that my function was imprecise! (not the one in the example).
select OBJECTPROPERTY (object_id('fn...'),'IsPrecise')
The UDF used the POWER function which returns a float, and which my indexed view didn't appreciate. When I worked around this I was able to create my index.
Thanks again for your help.
David.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply