Cannot create index on view with User Defined Function in SQL Server

Eddie picture Eddie · Mar 10, 2009 · Viewed 9.8k times · Source

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

Answer

Eric Petroelje picture Eric Petroelje · Mar 10, 2009

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.