--
--19.5.2节示例
--
创建全文目录
USE TestDB1
GO
ALTER DATABASE [TestDB1] ADD FILEGROUP [FTGroup]
GO
ALTER DATABASE [TestDB1]
ADD FILE ( NAME = N'FtsFile', FILENAME = N'C:\DATA\FtsFile.ndf') TO FILEGROUP FTGroup
GO
CREATE FULLTEXT CATALOG FT_Cat --创建全文目录
ON FILEGROUP FTGroup --指定文件组
AS DEFAULT
--
--19.5.3节示例
--
创建全文索引
CREATE TABLE Fts1 --创建测试表
(
ID INT IDENTITY,
TSQL NVARCHAR(max) NOT NULL,
CONSTRAINT PK_Fts1ID PRIMARY KEY CLUSTERED (ID)
)
GO
CREATE FULLTEXT INDEX ON Fts1([TSQL] LANGUAGE 'Simplified Chinese') --创建全文索引
KEY INDEX PK_Fts1ID
ON FT_Cat
WITH CHANGE_TRACKING=AUTO
为全文索引启用完全填充
ALTER FULLTEXT INDEX ON Fts1
START FULL POPULATION --完全填充
--
--19.5.3节示例
--
使用CONTAINS和FREETEXT进行全文搜索
SELECT *
FROM Fts1
WHERE CONTAINS([TSQL],N'化妆品') --全文搜索函数
GO
SELECT *
FROM Fts1
WHERE FREETEXT ([TSQL],N'化妆品') --全文搜索函数
使用CONTAINSTABLE()和FREETEXTTABLE()全文检索
SELECT *
FROM Fts1 INNER JOIN CONTAINSTABLE(Fts1,[TSQL],N'化妆品') ct --全文搜索
ON Fts1.ID=ct.[KEY]
ORDER BY ct.[Rank] DESC
GO
SELECT *
FROM Fts1 INNER JOIN FREETEXTTABLE(Fts1,[TSQL],N'化妆品') ft --全文搜索
ON Fts1.ID=ft.[KEY]
ORDER BY ft.[Rank] DESC
