SQL Server全文搜索

USE [pratice]
GO --创建全文索引的方式1： -------------开启全文索引和创建全文索引目录 全文目录创建的路径是D:\fulltext
--fulltext_pratice是自己自定义的全文目录名称
EXEC [sys].[sp_fulltext_database] @action = 'enable' -- varchar(20) --如果数据库中已存在全文目录fulltext_pratice要先drop掉
--EXEC [sys].[sp_fulltext_catalog] @ftcat = 'fulltext_pratice', -- sysname
-- @action = 'drop' -- varchar(20) EXEC [sys].[sp_fulltext_catalog] @ftcat = 'fulltext_pratice', -- sysname
@action = 'create', -- varchar(20)
@path = N'D:\fulltext' -- nvarchar(101)

CREATE TABLE [dbo].[fulltext_test](
[id] [varchar](200) NOT NULL UNIQUE, --建立全文索引的列的要求是not null并且是唯一的！！！
[NAME] [varchar](200) NOT NULL
) ON [PRIMARY] --在id字段上创建一个非聚集索引，并且是唯一的
CREATE UNIQUE NONCLUSTERED INDEX [IX_ID] ON [dbo].[fulltext_test]
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

EXEC [sys].[sp_fulltext_table] @tabname = N'fulltext_test', -- nvarchar(517)
@action = 'create', -- varchar(50)
@ftcat = fulltext_pratice, -- sysname
@keyname = IX_ID -- sysname

--@language参数的意义在于分词，到时候会把id字段里的数据按照各个国家的语言来进行分词，具体的数字您可以自己指定
--2052代表中文，1033代表美文，2057代表英文 EXEC [sys].[sp_fulltext_column] @tabname = N'fulltext_test', -- nvarchar(517)
@colname = id, -- sysname
@action = 'add', -- varchar(20)
@language = 2052 -- int 2057 is the LCID for 英语 1033 :美语 2052：中文

SELECT * FROM sys.fulltext_languages

-------------激活索引
EXEC [sys].[sp_fulltext_table] @tabname = N'fulltext_test', -- nvarchar(517)
@action = 'activate'

USE [pratice]
GO
INSERT INTO [dbo].[fulltext_test] ( [id], [NAME] )
SELECT '','中国' UNION ALL
SELECT '','美国' UNION ALL
SELECT '','英国' UNION ALL
SELECT '','法国' UNION ALL
SELECT '','韩国' UNION ALL
SELECT '','日本' UNION ALL
SELECT '','朝鲜' UNION ALL
SELECT '','印度' UNION ALL
SELECT '','德国' UNION ALL
SELECT '','意大利'

ALTER FULLTEXT INDEX ON 表名 SET CHANGE_TRACKING OFF

------------填充索引，首次创建完全文索引之后先完全填充索引，把数据全部放入全文索引里
USE [pratice]
GO
ALTER FULLTEXT INDEX ON [dbo].[fulltext_test] SET CHANGE_TRACKING OFF
GO
ALTER FULLTEXT INDEX ON [dbo].[fulltext_test] START FULL POPULATION
GO ------------修改填充方式为自动跟踪更改，等数据有变化的时候只需要把变化的部分填充进去全文索引里就可以了
USE [pratice]
GO
ALTER FULLTEXT INDEX ON [dbo].[fulltext_test] SET CHANGE_TRACKING AUTO
GO ---------------如果是增量填充
USE [pratice]
GO
ALTER FULLTEXT INDEX ON [dbo].[fulltext_test] SET CHANGE_TRACKING OFF
GO
--或者
ALTER FULLTEXT INDEX ON [dbo].[fulltext_test] SET CHANGE_TRACKING MANUAL
GO
ALTER FULLTEXT INDEX ON [dbo].[fulltext_test] START INCREMENTAL POPULATION
GO

--（1）把自动跟踪更改设置为手动，然后UPDATE POPULATION更新填充
ALTER FULLTEXT INDEX ON [dbo].[fulltext_test] SET CHANGE_TRACKING MANUAL
GO
ALTER FULLTEXT INDEX ON [dbo].[fulltext_test] START UPDATE POPULATION;
GO --（2）把自动跟踪更改设置为手动或者关闭，然后INCREMENTAL POPULATION增量填充
ALTER FULLTEXT INDEX ON [dbo].[fulltext_test] SET CHANGE_TRACKING MANUAL
GO
--或者
ALTER FULLTEXT INDEX ON [dbo].[fulltext_test] SET CHANGE_TRACKING OFF
GO
ALTER FULLTEXT INDEX ON [dbo].[fulltext_test] START INCREMENTAL POPULATION
GO --（3）把自动跟踪更改设置为关闭，然后进行完全填充，一般完全填充只在刚刚创建完全文索引的时候使用
ALTER FULLTEXT INDEX ON [dbo].[fulltext_test] SET CHANGE_TRACKING OFF
GO
ALTER FULLTEXT INDEX ON [dbo].[fulltext_test] START FULL POPULATION
GO

'USE pratice ALTER FULLTEXT INDEX ON [dbo].[fulltext_test] SET CHANGE_TRACKING MANUAL ALTER FULLTEXT INDEX ON [dbo].[fulltext_test] START UPDATE POPULATION; '

--添加作业
USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name = N'启动对[fulltext_test]表增量填充', @enabled = 1,
@start_step_id = 1,
@description = N'已为数据库pratice中的全文目录 fulltext_pratice 计划了对[fulltext_test]表的增量填充。',
@job_id = @jobId OUTPUT
SELECT @jobId
GO
-----------------------------------------
--指定要运行本作业的服务器
EXEC msdb.dbo.sp_add_jobserver @job_name = N'启动对[fulltext_test]表增量填充',
@server_name = N'joe'
GO
--------------------------------------
--添加作业计划
USE [msdb]
GO
DECLARE @schedule_id INT
EXEC msdb.dbo.sp_add_jobschedule @job_name = N'启动对[fulltext_test]表增量填充',
@name = N'fulltext_test', @enabled = 1, @freq_type = 4, @freq_interval = 1,
@freq_subday_type = 1, @freq_subday_interval = 0,
@freq_relative_interval = 0, @freq_recurrence_factor = 1,
@active_start_date = 20130815, @active_end_date = 99991231,
@active_start_time = 120742, @active_end_time = 235959,
@schedule_id = @schedule_id OUTPUT
SELECT @schedule_id
GO
--------------------------------------
--添加作业步骤
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name = N'启动对[fulltext_test]表增量填充',
@step_name = N'全文索引', @step_id = 1, @cmdexec_success_code = 0,
@on_success_action = 1, @on_success_step_id = -1, @on_fail_action = 2,
@on_fail_step_id = -1, @retry_attempts = 0, @retry_interval = 0,
@os_run_priority = 0, @subsystem = N'TSQL', @command = N'
USE pratice
ALTER FULLTEXT INDEX ON [dbo].[fulltext_test] SET CHANGE_TRACKING OFF ALTER FULLTEXT INDEX ON [dbo].[fulltext_test] START INCREMENTAL POPULATION ', @database_name = N'master'
GO

-------------------------------------------------------------------------
--创建全文索引的方式2： 方式2我没有找到全文目录的路径在哪里指定@path
USE [pratice]
GO CREATE FULLTEXT INDEX ON 表名
(
字段名 --Full-text index column name
TYPE COLUMN FileExtension --Name of column that contains file type information
Language 2052 --2057 is the LCID for British English
)
KEY INDEX [IX_ID] ON fulltext_pratice --Unique index
WITH CHANGE_TRACKING AUTO --Population type; 填充类型为自动跟踪更改
GO
-----------------------------------------------------

-----------------------卸载全文索引------------------
EXEC sp_fulltext_table 'fulltext_test', 'deactivate'
EXEC sp_fulltext_column 'fulltext_test', 'id', 'drop'
EXEC sp_fulltext_table 'fulltext_test', 'drop'
EXEC sp_fulltext_catalog 'fulltext_pratice', 'stop'
EXEC sp_fulltext_catalog 'fulltext_pratice', 'drop'

EXEC [sys].[sp_fulltext_column] @tabname = N'fulltext_test', -- nvarchar(517)
@colname = id, -- sysname
@action = 'add', -- varchar(20)
@language = 2052

USE pratice
GO
SELECT * FROM [dbo].[fulltext_test] WHERE CONTAINS(id ,'')

--查看全文索引碎片量
SELECT * FROM sys.fulltext_index_fragments
GO --重组全文索引
ALTER FULLTEXT CATALOG fulltext_test REORGANIZE
GO
--重建全文索引
ALTER FULLTEXT CATALOG fulltext_test REBUILD
GO

<XML ID="Microsoft Search Thesaurus"><!-- Commented out <thesaurus xmlns="x-schema:tsSchema.xml">
<diacritics_sensitive>0</diacritics_sensitive>
<expansion>
_{Internet Explorer}
_IE
_IE5
</expansion>
<replacement>
<pat>NT5</pat>
<pat>W2K</pat>
_{Windows 2000}
</replacement>
<expansion>
_run
_jog
</expansion>
</thesaurus>
-->
</XML>

--下列 T-SQL 指令碼示範查詢全文檢索索引狀態以及其內容。SELECT * FROM sys.dm_fts_index_populationSELECT * FROM sys.dm_fts_index_keywords( DB_ID('tde'), OBJECT_ID('SalesLT.Product'))