In SQL Server 2005, I'm trying to use a User Defined Function in a indexed view that will be used in a full-text index. I have been able to get the UDF to work with a stored procedure and the view in question. But, when I try to create an index on the view I get the following error...
Cannot create index on view "DevDatabase.dbo.View_PersonSearch" because function "dbo.GetCurrentImage" referenced by the view performs user or system data access.
I am stumped by this. Below is an example of what I am trying to do. Am I missing something or is this even possible?
User Defined Function
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GetCurrentImage](@Person_ID int)
RETURNS int
WITH SCHEMABINDING
AS
BEGIN
-- Declare the return variable here
DECLARE @Img_ID int
SET @Img_ID = (**sql that selects image** )
RETURN @Img_ID
END
GO
View with index creation
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[View_PersonSearch]
WITH SCHEMABINDING
AS
SELECT Person_ID,
(**Select fields to search on**) AS SearchArea,
dbo.GetCurrentImage(Person_ID) AS FK_Img_ID
FROM dbo.Person
GO
CREATE UNIQUE CLUSTERED INDEX Index_Person_ID ON [View_PersonSearch](Person_ID)
GO
According to this page:
Any functions referenced in an indexed view must be deterministic; deterministic functions return the same value each time they’re invoked with the same arguments.
GetCurrentImage is not deterministic with respect to its parameters - it uses a select, which means the results may change as the data changes - so any view using it cannot be indexed.