Enable Full-Text Search on View with Inner Join

Question

I'm running Sql Server 2008 R2 and I need to enable Full-Text search on a view with a inner join. My problem is that I don't know how to create my Full-Text Index.

When I use the Full-Text Indexing Wizard I get this error.

A unique column must be defined on this table/view.

In order for you to better understand my problem, please see the following example from w3school http://www.w3schools.com/sql/sql_join_inner.asp The last select is just my view.

PersonOrderView  - View
    SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
    FROM Persons
    INNER JOIN Orders
    ON Persons.P_Id=Orders.P_Id
    ORDER BY Persons.LastName     <- Order by is not important for me

Persons - Table
P_Id (PK, int, not null)
LastName(nvarchar(50), null)
FirstName(nvarchar(50), null)
Address(nvarchar(50), null)
City(nvarchar(50), null)

Orders - Table
O_Id(PK, int,  not null)
P_Id(FK, int, not null)
OrderNo(nvarchar(50), not null)

Answer 1

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.