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)

TheBoyan · Accepted Answer · 2019-11-14T11:11:39.637

24

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.

edited Nov 14 '19 at 11:11

answered Dec 13 '11 at 09:19

TheBoyan

6,802
3
45
61

But is it possible to create on my example? Not sure where to go, from your answer. – gulbaek Dec 13 '11 at 17:55
1

@gulbaek - I have added an example, but I strongly suggest that you read the articles in the links. The first link describes how full-text search works and the second one is a guide on how to create a fts against multiple tables. – TheBoyan Dec 13 '11 at 18:13
@gulbaek - you can also find useful information on how to create a full-text index here: http://msdn.microsoft.com/en-us/library/ms187317.aspx – TheBoyan Dec 13 '11 at 18:30
1

If the person has multiple orders, wouldn't the person's ID appear in the view multiple times and, therefore, no longer be unique? Or do you only mean that it must be unique within the table it came from? – adam0101 Apr 24 '17 at 14:41
1

Two of the links are broken now, so the answer is even less useful now. – NetMage Nov 13 '19 at 04:08
@NetMage Thanks for letting me know, links do tend to disappear for whatever reason. I've made an update. – TheBoyan Nov 14 '19 at 11:15

Enable Full-Text Search on View with Inner Join

1 Answers1

Linked