实例：asp+sql中的全文索引(3)

1、启动全文索引服务
运行cmd，运行net start mssearch
2、以对数据库cms_database的数据表JumbotCMS_Article的title和content列建立全文索引，之后使用索引查询title列或content列中包含有“中国” 或“美国”字符串的文章为例
3、打开查询分析器，运行如下脚本

use master
go
--创建数据库
Create DATABASE [cms_database]
COLLATE Chinese_PRC_CI_AS
GO

exec sp_dboption N'cms_database', N'autoclose', N'true'
GO

exec sp_dboption N'cms_database', N'bulkcopy', N'false'
GO

exec sp_dboption N'cms_database', N'trunc. log', N'true'
GO

exec sp_dboption N'cms_database', N'torn page detection', N'true'
GO

exec sp_dboption N'cms_database', N'read only', N'false'
GO

exec sp_dboption N'cms_database', N'dbo use', N'false'
GO

exec sp_dboption N'cms_database', N'single', N'false'
GO

exec sp_dboption N'cms_database', N'autoshrink', N'true'
GO

exec sp_dboption N'cms_database', N'ANSI null default', N'false'
GO

exec sp_dboption N'cms_database', N'recursive triggers', N'false'
GO

exec sp_dboption N'cms_database', N'ANSI nulls', N'false'
GO

exec sp_dboption N'cms_database', N'concat null yields null', N'false'
GO

exec sp_dboption N'cms_database', N'cursor close on commit', N'false'
GO

exec sp_dboption N'cms_database', N'default to local cursor', N'false'
GO

exec sp_dboption N'cms_database', N'quoted identifier', N'false'
GO

exec sp_dboption N'cms_database', N'ANSI warnings', N'false'
GO

exec sp_dboption N'cms_database', N'auto create statistics', N'true'
GO

exec sp_dboption N'cms_database', N'auto update statistics', N'true'
GO

if( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) )
    exec sp_dboption N'cms_database', N'db chaining', N'false'
GO

use [cms_database]
GO

if not exists (select * from master.dbo.syslogins where loginname = N'jumbot2007')
BEGIN
    declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N'master', @loginlang = N'简体中文'
    if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
        select @logindb = N'master'
    if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')
        select @loginlang = @@language
    exec sp_addlogin N'jumbot2007', '7002tobmuj', @logindb, @loginlang
END
GO

if not exists (select * from dbo.sysusers where name = N'jumbot2007')
    EXEC sp_grantdbaccess N'jumbot2007', N'jumbot2007'
GO

exec sp_addrolemember N'db_owner', N'jumbot2007'
GO
--新建文章表
Create TABLE [JumbotCMS_Article] (
                [Article_ID] [bigint] IDENTITY (1, 1) NOT NULL ,
                [Channel_ID] [int] Default 0 NOT NULL ,
                [Class_ID] [int] Default 0 NULL ,
                [Special_ID] int default(0) NOT NULL,
                [Title] [nvarchar] (60) COLLATE Chinese_PRC_CI_AS NOT NULL ,
                [Author] [nvarchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
                [CopyFrom] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
                [Editor] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
                [Tags] [nvarchar] (60) COLLATE Chinese_PRC_CI_AS NULL ,
                [UpdateTime] [smalldatetime] Not NULL ,
                [Content] [ntext] COLLATE Chinese_PRC_CI_AS NOT NULL ,
                [DefaultPicUrl] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
                [UploadFiles] ntext COLLATE Chinese_PRC_CI_AS NULL,
                [Passed] [int] Default 0 NOT NULL ,
                [Intro] ntext COLLATE Chinese_PRC_CI_AS NULL,
                ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Alter TABLE [JumbotCMS_Article] WITH NOCHECK ADD CONSTRAINT [PK_Article_ID] PRIMARY KEY  CLUSTERED ([Article_ID])  ON [PRIMARY]
GO

--检查数据库cms_database是否支持全文索引，如果不支持
--则使用sp_fulltext_database 打开该功能
if(select databaseproperty('cms_database','isfulltextenabled'))=0  execute sp_fulltext_database 'enable'

--建立全文目录FT_cms_database
execute sp_fulltext_catalog 'FT_cms_database','create'

--为JumbotCMS_Article表建立全文索引数据元
execute sp_fulltext_table 'JumbotCMS_Article','create','FT_cms_database','PK_Article_ID'

--设置全文索引列名
execute sp_fulltext_column 'JumbotCMS_Article','title','add'
execute sp_fulltext_column 'JumbotCMS_Article','content','add'

--建立全文索引
--activate，是激活表的全文检索能力，也就是在全文目录中注册该表
execute sp_fulltext_table 'JumbotCMS_Article','activate'

--填充全文索引目录
execute sp_fulltext_catalog 'FT_cms_database','start_full'
go

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

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