use DBFullText

-- 建表
create table Student
(
    id int primary key identity(1,1) not null,
    name nvarchar(30) null,
    familyAddress nvarchar(100) null,
    schoolAddress nvarchar(100) null
)

--插入一些数据
insert into Student values('SAVEA','187 Suffolk Ln.','1900 Oak St.')
insert into Student values('VICTE','2, rue du Commerce','23 Tsawassen Blvd.')
insert into Student values('BLONP','24, place Kléber','25, rue Lauriston')
insert into Student values('PARIS','265, boulevard Charonne','2732 Baker Blvd.')
insert into Student values('OLDWO','2743 Bering St.','2817 Milton Dr.')
insert into Student values('WANDK','Adenauerallee 900','Åkergatan 24')
insert into Student values('BERGS','Berguvsvägen  8','Carrera 22 con Ave. Carlos Soublette #8-35')
insert into Student values('SANTG','Carrera 52 con Ave. Bolívar #65-98 Llano Largo','Erling Skakkes gate 78')
insert into Student values('OCEAN','Grenzacherweg 237','Jardim das rosas n. 32')
insert into Student values('LEHMS','Sierras de Granada 9993','Via Ludovico il Moro 22')
insert into Student values('SIMOB','South House 300 Queensbridge','P.O. Box 555')


--检查 DBFullText 是否支持全文索引，如果不支持全文索引，则使用sp_fulltext_datebase打开该功能
if (select databaseproperty ('DBFullText','IsFulltextEnables'))=0
exec sp_fulltext_database 'enable'


--创建全文目录（‘全文目录名‘，’创建/删除‘） 
exec sp_fulltext_catalog 'FT_Student','create'


--创建全文索引（‘表名‘，’创建/删除‘，’名称‘，’约束名‘），这里的约束名就是建表的时候自动生成的主键约束
exec sp_fulltext_table 'Student','create','FT_Student','PK_Student'


--设置全文索引列（‘表名‘，’列名‘，’添加/删除‘） 
exec sp_fulltext_column 'Student','familyAddress','add'
exec sp_fulltext_column 'Student','schoolAddress','add'


--激活表的全文检索能力，也就是在全文目录中注册该表
exec sp_fulltext_table 'Student','activate'


--填充全文索引目录
exec sp_fulltext_catalog 'FT_Student','start_full'


--测试一下
select * from Student where contains (familyAddress,'South')
select * from Student where contains (schoolAddress,'Dr.')