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
You can create a functional index on the resulting values of a user-defined function on one or more columns.
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.
MySQL will consider using indexes that have been defined on the underlying tables. However you cannot create an index on the view.
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).
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With