全文索引

全文索引是基于要编制索引的文本中的各个标记来生成倒排序、堆积且压缩的索引结构。

每个表或索引视图只允许有一个全文索引。该索引最多可包含 1024 列。，该对象中必须有一唯一并且非空的列。

全文索引大小受运行SQL SERVER实例的计算机的可用内存资源限制。

对全文索引的创建和维护过程 称为 “索引填充”。索引填充分为：完全填充、基于时间戳的增量式填充、基于更改跟踪的填充

--确定是否安装了全文组件。如果已经安装该服务，此语句返回 1，否则返回 0。

SELECT FULLTEXTSERVICEPROPERTY('IsFullTextInstalled');

--确定已分配给全文的资源使用级别 1（后台）到 5（专用）

SELECT FULLTEXTSERVICEPROPERTY ('ResourceUsage');

全文目录

首先第一步：

为数据库创建全文目录。一个全文目录可以包含多个全文索引，但一个全文索引只能用于构成一个全文目录。每个数据库可以不包含全文目录或包含多个全文目录。

例如 ：

create fulltext catalog my_full_catalog with accent_sensitivity =on as default;

-- 可选项 with accent_sensitivity =on|off 指定该目录的全文索引是否区分重音。在更改此属性后，必须重新生成索引。默认情况下，将使用数据库排序规则中所指定的区分重音设置。若要显示数据库排序规则，请使用 select * from sys.databases

第二步：

创建索引(来自联机帮助)：

CREATE FULLTEXT INDEX ON table_name

[ ( { column_name

[ TYPE COLUMN type_column_name ]

[ LANGUAGE language_term ]

} [ ,...n]

) ]

KEY INDEX index_name

[ ON <catalog_filegroup_option> ]

[ WITH [ ( ] <with_option> [ ,...n] [ ) ] ]

<catalog_filegroup_option>::=

{

fulltext_catalog_name

| ( fulltext_catalog_name, FILEGROUP filegroup_name )

| ( FILEGROUP filegroup_name, fulltext_catalog_name )

| ( FILEGROUP filegroup_name )

}

<with_option>::=

{

CHANGE_TRACKING [ = ] { MANUAL | AUTO | OFF [, NO POPULATION ] }

| STOPLIST [ = ] { OFF | SYSTEM | stoplist_name }

}

注解:

column_name

全文索引中包含的列的名称。只能对类型为 char、varchar、nchar、nvarchar、text、ntext、image、xml 和 varbinary 的列进行全文索引

若要指定多个列，请按如下方式重复 column_name 子句：

CREATE FULLTEXT INDEX ON table_name (column_name1 […], column_name2 […]) …

TYPE COLUMN type_column_name

table_name 中列的名称，用于存储 column_name 的文档类型。type_column_name 的类型必须为 char、nchar、varchar 或 nvarchar。仅当 column_name 中的一个或多个列属于类型 varbinary(max) 或 image 时，才指定 type_column_name；

KEY INDEX index_name

table_name 的唯一键索引的名称。KEY INDEX 必须是唯一的单键不可为 Null 的列。为全文唯一键选择最小的唯一键索引。

fulltext_catalog_name

用于全文索引的全文目录。数据库中必须已存在该目录。此子句为可选项。如果未指定，则使用默认目录。

FILEGROUP filegroup_name

针对指定的文件组创建指定的全文索引。该文件组必须已存在。如果未指定 FILEGROUP 子句，则全文索引位于与基表或视图相同的文件组中（对于非分区表），或者位于主文件组中（对于分区表）。

CHANGE_TRACKING [ = ] { MANUAL | AUTO | OFF [ , NO POPULATION ] }

指定 SQL Server 是否维护对索引数据的所有更改的列表。更改跟踪不会记录通过 WRITETEXT 和 UPDATETEXT 进行的数据更改

MANUAL

指定是使用 SQL Server 代理按计划传播更改跟踪日志，还是由用户手动进行传播。

AUTO

指定在关联的表中修改了数据时，SQL Server 自动更新全文索引。默认值为 AUTO

OFF [ , NO POPULATION]

指定 SQL Server 不保留对索引数据的更改的列表。

仅当 CHANGE_TRACKING 为 OFF 时，才能使用 NO POPULATION 选项。如果指定了 NO POPULATION，则 SQL Server 在创建索引后不会对其进行填充。只有在用户使用 START FULL 或 INCREMENTAL POPULATION 子句执行 ALTER FULLTEXT INDEX 命令后，才会填充索引。如果未指定 NO POPULATION，则 SQL Server 创建索引后将对其进行完全填充。

STOPLIST [ = ] { OFF | SYSTEM | stoplist_name }

将全文非索引字表与索引关联起来。不使用属于指定非索引字表的任何标记填充索引。如果未指定 STOPLIST，则 SQL Server 会将系统全文非索引字表与索引关联起来。

OFF

指定没有与全文索引关联的非索引字表

SYSTEM

指定应对此全文索引使用默认的全文系统 STOPLIST。

stoplist_name

指定要与全文索引关联的非索引字表的名称。使用称为“非索引字表”的对象在数据库中管理非索引字。非索引字表是一个由非索引字组成的列表，这些非索引字在与全文索引关联时会应用于该索引的全文查询。若要对全文索引使用非索引字表，需要拥有 REFERENCE 权限。

语法:

CREATE FULLTEXT STOPLIST stoplist_name

[ FROM { [ database_name. ] source_stoplist_name } | SYSTEM STOPLIST ]

[ AUTHORIZATION owner_name ]

stoplist_name

非索引字表的名称。stoplist_name 最多可包含 128 个字符。stoplist_name 在当前数据库中的所有非索引字表中必须是唯一的.

创建全文索引时将使用 stoplist_name。

database_name

source_stoplist_name 指定的非索引字表所在的数据库的名称。如果未指定，则 database_name 默认为当前数据库。

source_stoplist_name

指定通过复制现有的非索引字表来创建新的非索引字表。如果不存在 source_stoplist_name，或者数据库用户不具有正确的权限，CREATE FULLTEXT STOPLIST 将失败，并返回错误。如果源非索引字表的非索引字中指定的任何语言未在当前数据库中注册，CREATE FULLTEXT STOPLIST 将成功，但会返回警告且不会添加相应的非索引字。

SYSTEM STOPLIST

指定用默认存在于 Resource 数据库中的非索引字表创建新的非索引字表。

AUTHORIZATION owner_name

指定拥有此非索引字表的数据库主体的名称。owner_name 必须是当前用户所属的主体的名称，或当前用户必须对 owner_name 拥有 IMPERSONATE 权限。如果未指定，则所有权授予当前用户。

--创建全文非索引字表的三种方式;

1、CREATE FULLTEXT STOPLIST my_Stoplist ---创建新的全文非索引字表

2、create fulltext stoplist next_Stoplist from test.my_Stoplist ---从现有的全文非索引字表复制全文非索引字表

3、create fulltext stoplist from_system_Stoplist from system STOPLIST ---从系统全文非索引字表复制全文非索引字表

例如：

create fulltext index on t_b(remarks) key index [PK__t_b__uniqueindex] on my_fulltext_index

with (change_tracking=auto,stoplist=system)

GO

ALTER FULLTEXT INDEX ON [dbo].[t_b] ENABLE--修改表上的全文索引为激活态(ENABLE 激活全文索引；DISABLE 关闭全文索引。)

GO

DROP FULLTEXT INDEX ON t_b ---删除表上的全文索引

--与全文索引相关的关键词和函数有

1、contains 和 freetext 都返回 true 或 false 。都在 SELECT 语句中的 where 或 having 子句中使用。它们只能用在指定选择条件中。

2、containstable 和 freetexttable 都返回零行、一行或多行的表，只能使用在 FROM 子句中。它们返回的表中会增加 KEY 和 RANK 两列。系统就是根据 RANK(值越小，相关性越低) 的值进行排名的。containstable 和 freetexttable 中 可以指定要进行全文搜索的表、要在表中搜索的列（或所有列）以及搜索条件。

3、CONTAINSTABLE 和 FREETEXTTABLE 函数用来指定全文查询以返回每行的相关性排名。

select remarks from t_b where contains|freetext (Remarks,'testfulltext')

select remarks from t_b where contains|freetext (Remarks,'"wenzhong"or "zhongwen"')

select remarks from t_b where contains|freetext (Remarks ,'"zhao*"')

go

SELECT a.Remarks, a.Loop_Int,a.name, b.RANK

FROM t_b AS a

INNER JOIN CONTAINSTABLE|FREETEXTTABLE (t_b, Remarks,

'("zhao" ) OR

("wenzhong" )') AS b ON a.ID = b.[KEY]

WHERE b.RANK > 5

AND a.Name <> 'testfulltext'

ORDER BY b.RANK DESC

 

---全文搜索体系结构的各组件 ---------来自联机帮助

 

全文非索引字表

为了精简全文索引，SQL Server 提供了一种机制，用于去掉那些经常出现但对搜索无益的字符串。这些去掉的字符串称为“非索引字”。在索引创建期间，全文引擎将忽略全文索引中的非索引字。也就是说全文查询将不搜索非索引字。

非索引字可以是在特定语言中具有含义的词，也可以是不具有语言含义的“标记”。例如，在英语中，诸如“a”、“and”、“is”和“the”之类的词将被排除在全文索引之外，这是因为已经知道它们对搜索没有用处。