Having recently explored Verity 2K, I have come across another way of implementing a search feature on your site. If most of your content is stored an MS SQL database then you can make use of Full Text Catalogues to search your content for keywords. This article is a little bity as it just covers the basic steps without many in-depth explanations. For those who would like to know more about the various steps, please just consult on-line help books as that’s where all of this stuff was lifted from. So without further ado, let’s get started.
Preparing a full text catalogue
sp_fulltext_database. This stored procedure initialises full-text indexing or removes all full-text catalogs from the current database, and the syntax is as follows:
USE Northwind
EXEC sp_fulltext_database ‘enable’
Creating a full text catalogue
sp_fulltext_catalog, this stored procedures drops and creates a full-text catalog, and starts and stops the indexing action for a catalog. Multiple full-text catalogs can be created for each database, e.g.:
USE Northwind
EXEC sp_fulltext_catalog ‘our_test_catalogue’, ‘create’
Preparing the table to be indexed for full-text searching
sp_fulltext_table, this marks or unmarks a table for full-text indexing, e.g.:
USE Northwind
EXEC sp_fulltext_table ‘Categories’, ‘create’, ‘our_test_catalogue’, ‘PK_Categories’
Adding a column to a catalogue
sp_fulltext_column specifies whether or not a particular column of a table participates in full-text indexing.
USE Northwind
EXEC sp_fulltext_column Categories, Description, ‘add’
To actually populate the catalogue
SQL Server MMC >> Database to be catalogues >> Full Text catalogue >> Actual catalogue >> Right click, then start full population.
Maintenance
You’ll want to run a scheduled task to populate the catalogue regularly with new and updated records, as this is not handled automatically.
Searching
Searching your catalogs couldn’t be simpler, all you need to do is write a select statement that runs against your catalog.
Basic search: Select * from Categories where contains(Description, ‘formsof(inflectional, coffee)’)
Advanced search: Use CONTAINSTABLE as it is faster on larger record sets
Weighting: Weighting allows you to weigh the importance of your keywords. In this case bread has higher importance.
USE Northwind
GO
SELECT FT_TBL.CategoryName, FT_TBL.Description, KEY_TBL.RANK
FROM Categories AS FT_TBL INNER JOIN
CONTAINSTABLE(Categories, Description,
‘ISABOUT (breads weight (.8),
fish weight (.4), beers weight (.2) )’ ) AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC
GO
Matches the entire string: Below is an example of matching an entire string, in this case the entire keywords to be searched for are: “soft drinks”
USE Northwind
GO
SELECT FT_TBL.CategoryName, FT_TBL.Description, KEY_TBL.RANK
FROM Categories AS FT_TBL INNER JOIN
CONTAINSTABLE(Categories, *, ‘“soft drinks”’) AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC
GO
Breaking the string apart without “” and using operators ‘and’, ‘or’:
USE Northwind
GO
SELECT FT_TBL.CategoryName, FT_TBL.Description, KEY_TBL.RANK
FROM Categories AS FT_TBL INNER JOIN
CONTAINSTABLE(Categories, *, ‘soft and drinks’) AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC
GO
Searching for a list of keywords:
USE Northwind
GO
SELECT FT_TBL.CategoryName, FT_TBL.Description, KEY_TBL.RANK
FROM Categories AS FT_TBL INNER JOIN
CONTAINSTABLE(Categories, *, ‘ISABOUT (soft, drinks, bread)’) AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC
GO