--iFTS(Interated Full-Text Search),整合的全文搜索引擎
--可在char,nchar,nvarchar,varchar和text,ntext,image,xml等类型进行搜索
--创建全文目录
CREATE FULLTEXT CATALOG AdvFTCatalog
ON FILEGROUP [PRIMARY]
WITH ACCENT_SENSITIVITY=ON
AUTHORIZATION [dbo];
--创建全文检索
--1033表示该地区的地区代码(LCID),即美国英语，缺省使用系统默认非索引字表
CREATE FULLTEXT INDEX ON SalesLT.ProductModel
(
Name LANGUAGE 1033,
CatalogDescription LANGUAGE 1033
)
KEY INDEX PK_ProductModel_ProductModelID
ON (AdvFTCatalog)
WITH
(
CHANGE_TRACKING AUTO,
STOPLIST=SYSTEM
);
GO
--CONTAINS谓词搜索
--查询CatalogDescription为bike的数据
select * from SalesLT.ProductModel
where contains(CatalogDescription,N'ride',language 1033)
--查询Name和CatalogDescription为bike的数据
select * from SalesLT.ProductModel where FreeTEXT(*,N'bike')
--查询CatalogDescription列中含词干为shift的变形词
select * from SalesLT.ProductModel where contains((Name,CatalogDescription),N'FORMSOF(INFLECTIONal,shift)', language 1033)
--查询Name中含有单词tour的变形词(如touring)或者单词mountain的行 SELECT * from SalesLT.ProductModel where contains(Name,N'FORMSOF(INFLECTIONal,tour) OR mountain')
--含有前缀的搜索
SELECT * from SalesLT.ProductModel where contains(Name,N'"road*"')
--查找单词aluminum和blueprint相近的实例(两单词间距离不大于50个词)
select * from SalesLT.ProductModel where contains(Name,N'aluminum NEAR blueprint')
--freetexttable和containstable和freetext和contains作用相近,
--只是返回Key(唯一索引)和Rank(iFTS相关性)两列
select * from containstable(SalesLT.ProductModel,[Name], N'FORMSOF(INFLECTIONAL,tour)')
--搜索匹配的前五行 SELECT * FROM FREETEXTTABLE(SalesLT.ProductModel,*,N'aluminum', language 1033,5) ft inner join SalesLT.ProductModel pm on ft.[KEY]=pm.ProductModelID;
