[技術筆記] SQL Server Full-text Search全文檢索

--Implementing Full Text Search with T-SQL stored Procedures / Eli Leiba
Enabeling Full Text search in T-SQL is usually not so "popular" as doing it with the EnterPrize Manager
So Here are the T-SQL steps you have to do in order to implement FTS in T-SQL

--1 Enabling full text on the database
EXEC sp_fulltext_database 'enable'

--2 Create the Catalog (if does not exist)
EXEC sp_fulltext_catalog 'MyCatalog','create'

--3 add a full text index on a Table
EXEC sp_fulltext_table 'Products', 'create', 'MyCatalog', 'pk_products'
EXEC sp_fulltext_table 'Categories', 'create', 'MyCatalog', 'pk_categories'

--4 add a column to the full text Index
EXEC sp_fulltext_column 'Products', 'ProductName', 'add'
EXEC sp_fulltext_column 'Categories', 'Description', 'add'

--5 activate the index
EXEC sp_fulltext_table 'Products','activate'
EXEC sp_fulltext_table 'Categories','activate'

--6 start full population
EXEC sp_fulltext_catalog 'MyCatalog', 'start_full'

-- usage in T-SQL (CONTAINS and FREETEXT predicates)
-- Using the index in T-SQL
USE Northwind
GO
SELECT ProductId, ProductName, UnitPriceFROM Products
WHERE CONTAINS( ProductName, ' "sasquatch " OR "stout" ' )
GO
USE Northwind
GO
SELECT CategoryNameFROM Categories
FREETEXT (Description, 'sweetest candy bread and dry meat' )
GO