Putting a full-text search index on a view with a function

Question

Hello, I'm using SQL Server 2008 and I have a view that looks like this:

select ID, dbo.functionname(ID) from tablename

I'm trying to put a full-text index on this, but it doesn't seem to have a unique index that I can go off of. The tablename.ID is the unique identifier.

I tried creating an index on it, but it says it cannot schema bind view because the function is not schemabound.

What do I need to do to get the full text index created?

Answer 1

I presume you mean't "not" schemabound in the comments above Recreate your view "WITH SCHEMABINDING" Then create a unique clusterred index as explained here http://www.mssqltips.com/tip.asp?tip=1610 Then try adding your Full Text Index

Answer 2

To be able to create an index view, the view must be deterministic, that is it must be the guarantied to be the same on every query of it.

Is your userfunction deterministic?

User-Defined Function Determinism

Whether a user-defined function is deterministic or nondeterministic depends on how the function is coded. User-defined functions are deterministic if:

* The function is schema-bound.

* All built-in or user-defined functions called by the user-defined

function are deterministic.

* The body of the function references no database objects outside

the scope of the function. For example, a deterministic function cannot reference tables other than table variables that are local to the function.

* The function does not call any extended stored procedures.

User-defined functions that do not meet these criteria are marked as nondeterministic. Built-in nondeterministic functions are not allowed in the body of user-defined functions.

Is your function SchemaBound?

alter function [dbo].[UserFunction] 
(@example int = 1 ) 
returns int
with schemabinding
as
begin
  return 1
end

Is your view SchemaBound?

ALTER VIEW dbo.UserView
WITH SCHEMABINDING
AS
SELECT ID, [dbo].userFunction

To create an indexed view you must first create a unique clustered index(See FAQ a bottom).