使用Inner Join在视图上启用全文搜索

问题描述

我正在运行Sql Server 2008 R2，我需要在内部连接的视图上启用全文搜索。我的问题是，我不知道如何创建我的全文索引。

当我使用全文索引向导时，出现此错误。

为了让您更好地理解我的问题，请参阅以下w3school的示例最后一个选择只是我的看法。

  PersonOrderView  - 查看
 SELECT Persons.LastName，Persons.FirstName，Orders.OrderNo 
 FROM Persons 
 INNER JOIN Orders 
 ON Persons.P_Id = Orders.P_Id 
 ORDER BY Persons.LastName <  - 按顺序对我来说并不重要
 
人员 - 表
 P_Id（PK，int，非空值）
 LastName（nvarchar（50），null）
 FirstName（nvarchar（50），null）
 Address（nvarchar（50），null）
 City（nvarchar（50），null）
 
 Orders  -  Table 
 $ O $ ld（PK，int，not null）
 P_Id（FK，int，not null）
 OrderNo（nvarchar（50），非空）
  pre> 
 
解决方案
您只能在，这就是为什么你得到错误。要在表或视图上创建全文搜索，它必须具有唯一的单列不可空的索引。
 
 
 
换句话说，您应该创建查看如下所示的内容： 
 
 
  CREATE VIEW ViewSearch WITH SCHEMABINDING AS 
 SELECT Persons.P_Id AS ID，Persons.LastName， Persons.FirstName，Orders.OrderNo 
 FROM Persons 
 INNER JOIN订单ON Persons.P_Id = Orders.P_Id 
 GO 
 CREATE UNIQUE CLUSTERED INDEX IX_ViewSearch ON ViewSearch（ID）
  
 SQL全文搜索通过称为填充的过程来构建全文索引，填充索引的单词和它们在您的表格和行中出现的位置。这就是为什么你需要一个能够唯一标识你每一行的字段，这就是为什么你需要将视图编入索引。
 
 
 
更多信息和。
 
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.  
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)
 解决方案 
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 and here.
                        
这篇关于使用Inner Join在视图上启用全文搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！