sql server 2000 全文索引全解

本文转至【http://blog.youkuaiyun.com/bakers/archive/2007/04/07/1555833.aspx】

对 Microsoft® SQL Server™ 2000 数据的全文索引支持涉及两个功能：对字符数据发出查询的能力和创建及维护基础索引以简化这些查询的能力。

 

全文索引在许多地方与普通的 SQL 索引不同。

普通 SQL 索引	全文索引
存储时受定义它们所在的数据库的控制。	存储在文件系统中，但通过数据库管理。
每个表允许有若干个普通索引。	每个表只允许有一个全文索引。
当对作为其基础的数据进行插入、更新或删除时，它们自动更新。	将数据添加到全文索引称为填充，全文索引可通过调度或特定请求来请求，也可以在添加新数据时自动发生。
不分组。	在同一个数据库内分组为一个或多个全文目录。
使用 SQL Server 企业管理器、向导或 Transact-SQL 语句创建和除去。	使用 SQL Server 企业管理器、向导或存储过程创建、管理和除去。

 

这些差异使大量管理任务变得不可缺少。全文管理是在几个层次上实施的：

服务器

可以对服务器范围的某些属性（如 resource_usage）加以设置，以便增加或减少全文服务所使用的系统资源数量。

• 说明  全文引擎作为名为 Microsoft 搜索的服务在 Microsoft Windows NT® Server 和 Microsoft Windows® 2000 Server 上运行。对于 Microsoft SQL Server 个人版，Microsoft 搜索服务不可用。尽管这意味着 Microsoft 搜索服务既未安装在 Microsoft Windows 95/98 上，也未安装在 Windows NT 工作站或 Windows 2000 Professional 客户端上，但这些客户端在连接到 SQL Server 标准版安装或企业版实例时可以使用这项服务。

• 数据库

  必须启用数据库才能使用全文服务。可以在已启用的数据库中创建和除去一个或多个全文目录的元数据。

• 全文目录

  全文目录包含数据库中的全文索引。每个目录可以用于数据库内的一个或多个表的索引需求。该目录中的索引是使用这里介绍的管理功能来填充的。（全文目录必须驻留在与 SQL Server 实例相关联的本地硬盘驱动器上。不支持可移动的驱动器、软盘和网络驱动器）。在每个服务器上最多可创建 256 个全文目录。

  • 说明  Windows NT 故障转移群集环境完全支持全文索引。有关更多信息，请参见在故障转移群集中运行全文查询。

  • 表

    首先，必须为全文支持启用表。然后，为与该表相关联的全文索引创建元数据（如表名及其全文目录）。表启用后，可以用为全文支持而启用的列中的数据填充它。如果表的全文定义被更改（例如，添加一个也将为全文检索而索引的新列），则必须重新填充相关的全文目录以使全文索引与新的全文定义同步。

  • 列

    可以从非活动的注册表中添加或除去支持全文查询的列。

  在所有这些级别上，可使用工具检索元数据和状态信息。

   

  和常规 SQL 索引一样，当在相关表中修改数据时，可自动更新全文索引。或者，也可以适当的间隔手工重新填充全文索引。这种重写可能既耗时又大量占用资源，因此，在数据库活动较少时，这通常是在后台运行的异步进程。

  应将具有相同更新特性的表（如更改少的与更改多的，或在一天的特定时段内频繁更改的表）组合在一起，并分配给相同的全文目录。通过以此方法设置全文目录填充调度，使得全文索引和表保持同步，且在数据库活动较多时不对数据库服务器的资源使用产生负面影响。

  为全文目录中的表安排全文索引的位置是非常重要的。在为全文目录指定表时，应该注意下列基本原则：

  • 始终选择可用于全文唯一键的最小唯一索引。（4 个字节且基于整数的索引是最佳的。）这将显著减少文件系统中 Microsoft 搜索服务所需要的资源。如果主键很大（超过 100 字节），可以考虑选择表中其它唯一索引（或创建另一个唯一索引）作为全文唯一键。否则，如果全文唯一键的大小达到允许的上限（450 字节），全文填充将无法继续进行。
    
    
  • 如果进行索引的表有成千上万行，请将该表指定给其自己的全文目录。
    
    
  • 应该考虑对其进行全文索引的表中发生的更改数以及表的行数。如果要更改的总行数，加上上次全文填充期间表中出现的行数达到成千上万行，请将该表指定给其自己的全文目录。

 2. 第一步: 建立表和unique索引

use   database
drop   table  news
go

create   table  news
(
  news_id  int   not   null ,
  title  varchar ( 100 ),
  department  varchar ( 50 ),
  abstract  varchar ( 900 ),
  author  varchar ( 20 ),
  issue_time  varchar ( 20 ),
  url  varchar ( 100 ),
  context  text ,
   primary   key  (news_id)
)
go  

if   exists ( select  name  from  sysindexes  where  name = ' news_index ' )
   begin
      drop   index  news.news_index
   end
create   unique   index  news_index  on  news(news_id)

go

 

 

3. 第二步: 创建全文目录,选择索引字段,激活索引表

-- ----------开启全文索引服务
-- -------- author: baker   07/7/4 ---------------
/*
     使用说明: 运行该程序要满足以下几个条件
    1.  没有在数据库ahpc中建立全文目录  ahpc_ft
    2.  表NEWS已经建立unique的索引 news_index
    3.  表NEWS无数据,即 建立表news的之后需要立即开启全文索引服务
*/

-- -----------开启全文索引和创建全文索引目录
exec  sp_fulltext_database  ' enable '
exec  sp_fulltext_catalog  ' ahpc_ft ' , ' drop '
exec  sp_fulltext_catalog  ' ahpc_ft ' , ' create '


-- -----------为news表创建全文索引 可索引列为 title,abstract,text

exec  sp_fulltext_table  ' news ' , ' create ' , ' ahpc_ft ' , ' news_index '
exec  sp_fulltext_column  ' news ' , ' title ' , ' add '
exec  sp_fulltext_column  ' news ' , ' abstract ' , ' add '
exec  sp_fulltext_column  ' news ' , ' context ' , ' add '

-- -----------激活索引
exec  sp_fulltext_table  ' news ' , ' activate '
exec  sp_fulltext_table  ' news ' , ' start_full '
exec  sp_fulltext_catalog  ' ahpc_ft ' , ' start_full '

-- 检查全文目录填充情况
While   fulltextcatalogproperty ( ' ahpc_ft ' , ' populateStatus ' ) <> 0
begin

-- 如果全文目录正处于填充状态，则等待5秒后再检测一次
waitfor  delay  ' 0:0:5 '
end


-- select fulltextcatalogproperty('ahpc_ft','populateStatus')

 

4. 第四步: 测试

 

use   database
drop   table  news
go

create   table  news
(
  news_id  int   not   null ,
  title  varchar ( 100 ),
  department  varchar ( 50 ),
  abstract  varchar ( 900 ),
  author  varchar ( 20 ),
  issue_time  varchar ( 20 ),
  url  varchar ( 100 ),
  context  text ,
   primary   key  (news_id)
)
go  

if   exists ( select  name  from  sysindexes  where  name = ' news_index ' )
   begin
      drop   index  news.news_index
   end
create   unique   index  news_index  on  news(news_id)

go

4. 第四步: 测试

-- insert  into [news] (news_id,title,abstract,context)values(1,'information,computer,technology','华南热带农业大学植保学院院长、博士生导师郑服从4月6日在接受记者采访时表示，香蕉是一种光合作用能力很强的植物，这种植物在热带地区长期进化，非常适宜热带地区的气候条件。 ','南热带农业大学植保学院院长、博士生导师郑服从4月6日在接受记者采访时表示，香蕉是一种光合作用能力很强的植物，这种植物在热带地区长期进化，非常适宜热带地区的气候条件')
-- -------- 全文索引事例,@key为关键字
declare   @key   varchar ( 10 )
set   @key = ' 博士 '
exec  sp_fulltext_catalog  ' ahpc_ft ' , ' start_full ' ;  select   *   from  news  where   contains (context, @key )

 

说明：在SQL SERVER2008中不建议使用sp_fulltext_database、sp_fulltext_catalog处理全文索引功能，而是采用CREATE FULLTEXT INDEX方式。