Enable Full-Text Search on View with Inner Join

Enable Full-Text Search on View with Inner Join

You can only create a full text index on an indexed view, that is why you are getting the error. To create full-text search on a table or view, it must have a unique, single-column, non-nullable index.

In other words, you should create your view something like this:

CREATE VIEW ViewSearch WITH SCHEMABINDING AS
SELECT Persons.P_Id AS ID, Persons.LastName, Persons.FirstName, Orders.OrderNo
    FROM Persons
    INNER JOIN Orders ON Persons.P_Id=Orders.P_Id
GO
CREATE UNIQUE CLUSTERED INDEX IX_ViewSearch ON ViewSearch (ID)

SQL full-text search builds a full-text index through a process called population, which fills the index with words and the locations in which they occur in your tables and rows. That is why you need a field that will uniquely identify you each row and that is why you need to make the view indexed.

More information here.

Full text index on view with combined unique index

You need to create primary key or unique index for your VIEW.

If you wants create to full-text search on a table or view, it must have a unique, single-column, non-nullable index.

This is the reason you are getting the Error.

I will strongly encourage you to watch this video on Indexed views
Very Good Video on Indexed views

Please Check Below link.
This Link will surely help you.
Detailed Full Text Information

Full-text Indexing for a view with multiple databases

Yes, absolutely. Each index will be queried individually and the results will be combined by the engine.

For example, if you've got:

• DatabaseA, TableA, FieldA with a full text index
• DatabaseB, TableB, FieldB with a full text index

And you have a view that includes both fields from both tables in both databases, it'll work fine when you query that view. From SQL Server's perspective, it doesn't matter whether they're in the same database or not.

If that doesn't match your scenario, try posting more detail about your challenges. Thanks!

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

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).

---

Related Topics