sql server 定义全文索引

在未做全文检索以前,个人主页只能对Fs_Title(文章标题)字段进行模糊查询(like’%*%’),如果对内容进行模糊查询,速度极慢,有时还可能报错,今天终于有空了,把这个查询做好了,经过测
试,检索一万条纪录不到两秒时间。ENJOY~~ 复制代码 代码如下:useLuanLuanDB
execsp_fulltext_database’enable’
execsp_fulltext_catalog’ArtileFullText’,’create’,’E:\SearchPage’
execsp_fulltext_table’TArticle’,’create’,’ArtileFullText’,’PK_TArticle’
execsp_fulltext_column’TArticle’,’Fs_Title’,’add’
execsp_fulltext_column’TArticle’,’Fs_Content’,’add’
execsp_fulltext_table’TArticle’,’start_full’测试代码:select*fromTArticlewhereContains(*,'”&key&”‘)andFi_IsShow=’1’orderbyFi_IDDESC

sp_fulltext_catalog ‘doc’,’create’,’F:\temp’
–在AB01表上创建全文所引
sp_fulltext_table 
‘Doc_ArchivesResource’,’create’,’doc’,’PK_Doc_ArchivesResource’ 
–在AB01表上添加列ZA0101为索引字段,索引的语言为 简体中文
sp_fulltext_column ‘Doc_ArchivesResource’ ,      ‘memo’,
‘add’,0x804
–激活已经停用的为 AB01 收集全文索引数据的功能
sp_fulltext_table ‘AB01′,’activate’;
–停用 AB01 的全文索引,从而无法再为 AB01
收集全文索引数据。全文索引元数据依然保留,该表可以被重新激活。
–如果启用了更改跟踪,停用一个活动索引将冻结索引的状态:停止正在进行的所有填充,不再向索引传播更改
sp_fulltext_table ‘AB01′,’Deactivate’;
–启动全文索引的增量填充
sp_fulltext_table 
‘Doc_ArchivesResource’,’start_change_tracking’ 
–一旦发生更改,就启动将被跟踪的更改传播给全文索引。
sp_fulltext_table 
‘Doc_ArchivesResource’,’Start_background_updateindex’ 
select * from Doc_ArchivesResource’ WHERE CONTAINS(ZA0101,’安徽’)

 

USE [RCA]

GO

execute sp_fulltext_database ‘enable’

GO

execute sp_fulltext_catalog ‘CUSTOMER_NAMECN’,’create’

GO

execute sp_fulltext_table ‘RCA_Customer_Master’,

‘create’,’CUSTOMER_NAMECN’,’PK_RCA_澳门金沙vip,Customer_Master’

GO

execute sp_fulltext_column ‘RCA_Customer_Master’,

‘Chinese_Cust_name’,’add’

GO

execute sp_fulltext_table ‘RCA_Customer_Master’,’activate’

GO

execute sp_fulltext_catalog ‘CUSTOMER_NAMECN’,’start_full’

GO

 

Then manually configure the full text catalogs according to below
enclosed screenshot:

                       

  1. Select ‘Simplified Chinese’      as language for broker
  2. Set ‘Automatic’ for Track      Changes