SQL Server 2k8全文搜索“不相关”的表使用视图或？

Question

我很新的全文搜索，我真的很想知道在多个不相关的表中执行“网站搜索”风格全文搜索的最佳方法（我计划在4个表中执行此操作）。我想使用视图像这样的：

CREATE VIEW [dbo].[Search] WITH SCHEMABINDING 
    AS 

     SELECT p.ProductId AS ItemId 
      ,'Product' AS ItemType 
      ,p.Title AS ItemTitle 
      ,p.LongDescription AS LongDescription 
      ,p.Price AS Price 
    FROM dbo.Product AS p 
    WHERE p.IsActive = 1 

    UNION 

    SELECT a.ArticleId AS ItemId 
      ,'Article' AS ItemType 
      ,a.ArticleTitle AS ItemTitle 
      ,a.Contents AS LongDescription 
      ,NULL AS Price 
    FROM dbo.Article AS a 
    WHERE a.IsActive = 1 

不过，虽然研究的正确语法索引我意识到，“一个”我需要一个唯一的索引和“b”显然意见与工会不能使用创建一个全文索引...

我看到的另一种方法是为每个表创建一个FTI，然后在一个Stored Proc中将它们联合到一个tmp表中，然后使用Order By rank选择tmp表。

我真的很感谢这方面的一些指导，大部分我发现与多个相关的表相关，其中视图的连接足以克服问题。

编辑：

@Joe亲切回答了这个问题，我会忘了，实际上在时尚解决，但关注的是，这是一个有点长篇大论，似乎它可能是最合乎逻辑的他建议的两种方式 - 我使用的是 - 我完全忘记了我必须将它分页......我不认为客户会对无尽的结果列表感到兴奋...

我的一位同事还提出了他已经实现的另一种技术，即将元数据放在表格中，并将结果基本缓存在另一张表格中，然后进行全文搜索，如果您知道你的元数据将是再加上你也仍然需要键入它给您的表格，可以立即得到实际结果或显示（全文如果需要这样来讲话）

CREATE PROCEDURE [dbo].[up_Search] 
    @Term VARCHAR(100) 
    ,@Skip INT = 0 
    ,@Take INT = 10 
AS 
DECLARE @Search TABLE 
(
    ItemId INT 
    ,ItemType VARCHAR(50) 
    ,ItemTitle VARCHAR(100) 
    ,LongDescription VARCHAR(MAX) 
    ,Price DECIMAL(10,2) 
    ,SearchRank INT 
) 

INSERT INTO @Search SELECT * FROM (

    SELECT p.ProductId AS ItemId 
      ,'Product' AS ItemType 
      ,p.Title AS ItemTitle 
      ,p.LongDescription AS LongDescription 
      ,p.Price AS Price 
      ,KEY_TBL.RANK AS SearchRank 
    FROM dbo.Product AS p 
    INNER JOIN CONTAINSTABLE(dbo.Product, Title, @Term) AS KEY_TBL ON p.ProductId = KEY_TBL.[KEY] 
    WHERE p.IsActive = 1 

    UNION 

    SELECT a.ArticleId AS ItemId 
      ,'Article' AS ItemType 
      ,a.ArticleTitle AS ItemTitle 
      ,a.Contents AS LongDescription 
      ,NULL AS Price 
      ,KEY_TBL.RANK AS SearchRank 
    FROM dbo.Article AS a 
    INNER JOIN CONTAINSTABLE(dbo.Article, ArticleTitle, @Term) AS KEY_TBL ON a.ArticleId = KEY_TBL.[KEY] 
    WHERE a.IsActive = 1 

    UNION  

    SELECT n.NewsId AS ItemId 
      ,'News' AS ItemType 
      ,n.NewsTitle AS ItemTitle 
      ,n.Contents AS LongDescription 
      ,NULL AS Price 
      ,KEY_TBL.RANK AS SearchRank 
    FROM dbo.News AS n 
    INNER JOIN CONTAINSTABLE(dbo.News, NewsTitle, @Term) AS KEY_TBL ON n.NewsId = KEY_TBL.[KEY] 
    WHERE n.IsActive = 1 

    UNION 

    SELECT b.BusinessId AS ItemId 
      ,bt.Title AS ItemType 
      ,b.Title AS ItemTitle 
      ,b.LongDescription AS LongDescription 
      ,NULL AS Price 
      ,KEY_TBL.RANK AS SearchRank 
    FROM dbo.Business AS b 
    INNER JOIN CONTAINSTABLE(dbo.Business, Title, @Term) AS KEY_TBL ON b.BusinessId = KEY_TBL.[KEY] 
    INNER JOIN dbo.BusinessType AS bt ON b.BusinessTypeId = bt.BusinessTypeId 
    WHERE b.IsActive = 1 
) AS tmp; 

WITH SearchCT AS 
(
    SELECT ItemId 
      ,ItemType 
      ,ItemTitle 
      ,LongDescription 
      ,Price 
      ,SearchRank 
      ,ROW_NUMBER() OVER (ORDER BY SearchRank DESC) AS RowNumber 
      ,COUNT(*) OVER() AS RecordCount 
    FROM @Search 
) 
SELECT ItemId, ItemType, ItemTitle, LongDescription, SearchRank, RowNumber, RecordCount 
FROM SearchCT 
WHERE RowNumber BETWEEN @Skip + 1 AND (@Skip + @Take) 
ORDER BY RowNumber 

返回0

Answer 1

我想你，你可以在这里采取两种基本方法：

1）骨料的四个表成一个单一的表，该表上的搜索。您需要为此表上的主键拥有唯一的标识符。因此，表结构将类似于您正在考虑索引视图，而且会是这个样子：

CREATE TABLE AggregatedTable 
(
    Id int IDENTITY(1,1) primary key, 
    ItemId int, 
    ItemType nvarchar(50), 
    ItemTitle nvarchar(255), 
    LongDescription nvarchar(max), 
    IsActive int 
) 

你会那么需要创建在LongDescription列全文索引。

这种方法的好处是，你可以做你的全文检索在单个表在单个查询，像：

SELECT Id, ItemId, ItemType, ct.RANK  
    FROM dbo.AggregateTable AS a INNER JOIN 
    CONTAINSTABLE (AggregateTable , *, '(light NEAR aluminum)', 1033) AS ct 
     ON a.ItemId= ct.[KEY] 
WHERE IsActive = 1 
ORDER BY ct.RANK desc 

这种方法的缺点是： 1.您必须定期运行作业以将数据从4个基表加载到聚合表中 2.您将使用磁盘空间的两倍

第二种方法是将数据保存在四个独立的表中，然后编写FTS查询UNION从四个表中得到的结果。您应该能够根据相关性对结果进行排名，然后获得前N个最相关的结果。你将不得不编写查询的东西如下：

SELECT p.ProductId AS ItemId, 'Product' AS ItemType, ct.RANK 'Rank'  
    FROM dbo.Product AS p INNER JOIN 
    CONTAINSTABLE (Product, *, '(light NEAR aluminum)', 1033) AS ct 
     ON p.ProductId = ct.[KEY] 
WHERE p.IsActive = 1 
UNION 
SELECT a.ArticleId AS ItemId, 'Article' AS ItemType, ct.RANK 
     CONTAINSTABLE (Article, *, '(light NEAR aluminum)', 1033) AS ct 
     ON p.ProductId = ct.[KEY] 
    FROM dbo.Article AS a 
    WHERE a.IsActive = 1 
    ORDER BY 'Rank' DESC 
UNION ... other two tables 

这种方法的好处是，你不需要有工作从四个表的内容汇总到一个表。

缺点是你的查询比较复杂，因为他们需要四个查询的UNION结果。

我会倾向于第二种方法。我认为这更直接，更容易维护，联合查询可以直接进行构建。