Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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
like image 622
Eddie Avatar asked Mar 10 '09 18:03

Eddie


People also ask

Can we create index on user-defined function?

You can create a functional index on the resulting values of a user-defined function on one or more columns.

Can we create index on views in SQL Server?

Indexes can only be created on views which have the same owner as the referenced table or tables. This is also called an intact ownership-chain between the view and the table(s). Typically, when table and view reside within the same schema, the same schema-owner applies to all objects within the schema.

Does index work on view?

MySQL will consider using indexes that have been defined on the underlying tables. However you cannot create an index on the view.

Can we create index on CTE in SQL Server?

No. A CTE is a temporary, "inline" view - you cannot add an index to such a construct. If you need an index, create a regular view with the SELECT of your CTE, and make it an indexed view (by adding a clustered index to the view).


1 Answers

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.

like image 120
Eric Petroelje Avatar answered Nov 07 '22 00:11

Eric Petroelje