If you are like me I have tried using Verity

If you are like me I have tried using Verity, but I feel it is lacking on the database searching, you need another way.  After fiddling around for a while I found the amazing Full-Text search capabilities built right into MS SQL Server 2000.  If you are running SQL Server for your sites DB then this is a great function (or set of functions) to allow an even better full text search to your website.

You will need a table called Recipe in your database.  It should be formatted like this for this exercise:

Column Name Column Type Description
ID int Primary Key
RecipeName char The name of this given recipe.
Description ntext The recipe

One thing to note is you must have permissions on the SQL Server to perform these tasks.  You must be a member of the sysadmin or the db_owner to perform all these tasks.  If your not sure what this means talk to your db administrator.

Once you have followed these few simple steps you will be able to create an awesome full-text capable query using your SQL Server DB.

STEP ONE:  Enable your DB to allow full-text searching.

<cfquery name="q_step_1" datasource="myDSN">
exec sp_fulltext_database 'enable'

This enables your database for the full-text searching.

STEP TWO:  Create a Full-Text Catalog (think of these as tables in your db but contain no additional data)

<cfquery name="q_step_2" datasource="myDSN">
exec sp_fulltext_catalog 'Full_Search', 'Create', 'path_to_save_collection'
Make sure you replace 'path_to_save_collection" with a directory to save the collection data. (You can just omit that parameter to specify the default directory that SQL Server is installed too)

STEP THREE:  Add a table to your newly created catalog.

<cfquery name="q_step_3" datasource="myDSN">
exec sp_fulltext_table 'Recipes', 'Create', 'Full_Search', 'ID'
This tell SQL Server that you want to add the table Recipes to your collection.  The ID parameter specifies that the Primary Key for that table is the column 'ID'.

STEP FOUR:  Add a column that you want to be able to search.

<cfquery name="q_step_4" datasource="myDSN">
exec sp_fulltext_column 'Recipes', ,'Description', 'add'
This tell SQL Server that you want to add the column 'Description' from the table 'Recipes' (defined earlier in step 3) to the search collection.  (You can add as many columns as you want)

STEP FIVE:  Index the collection.

<cfquery name="q_step_5" datasource="myDSN">
exec sp_fulltext_catalog 'Full_Search', 'start_full'
This tell SQL Server that you want to index the collection.  This will take a while depending on the amount of rows you have in your table that you defined in step 3.  This will create a full snapshot of the data.

STEP FIVE AND 1/2:  These steps are not necessary to complete your index, only a few extra things to help during the indexing process.

Check out the following link for more information: http://msdn.microsoft.com/library/en-us/tsqlref/ts_fa-fz_7x15.asp?frame=true
<cfquery name="q_step_5_1_2" datasource="myDSN">
SELECT FULLTEXTCATALOGPROPERTY ('Full_Search' , 'ItemCount') as TotalRecordsIndexed

<cfdump var="#q_step_5_1_2#">
A short simple query to help identify how many rows your db has indexed.
<cfquery name="q_step_5_1_2" datasource="myDSN">
SELECT FULLTEXTCATALOGPROPERTY ('Full_Search' , 'IndexSize') as SizeOfIndex

<cfdump var="#q_step_5_1_2#">
Another simple query that shows you exactly how big your index is in megabytes.

STEP SIX:  Now lets create the query!

<cfquery name="q_step_5_1_2" datasource="myDSN">
SELECT Recipes.ID, Recipes.RecipeName
FROM FROM FREETEXTTABLE (Recipes, *, 'YOUR SEARCH' , 1000) AS Search_Table INNER JOIN Recipe ON Search_Table.[key] = Recipes.ID
ORDER BY Search_Table.[rank] desc

<cfdump var="#q_step_5_1_2#">
Now this can look daunting to some but if you break the query down you can easily follow it.  First select the columns you wish to grab from the recipes table.  We have selected ID, and RecipeName.  Now we must grab the datafrom the collection using the FREETEXTTABLE function.  First we tell it we want to search the table Recipes, search all the columns in the collection (defined in step 4) search for the terms 'YOUR SEARCH' and display the top 1000 by rank.  Next we name this table Search_Table.  Now we can join that table to grab more information from the original table such as the Recipe Name.  Last we order the query by the rank.

There we go!  We have now created and queried the table using SQL 2000!  I find it a lot more controllable since it is a query and a lot faster that verity.  If you have any questions feel free to email me at ivanS@Coastalmalls.com

About This Tutorial
Author: Ivan E Spaeth
Skill Level: Advanced 
Platforms Tested: CF1,CF2,CF3,CF4,CF5,CFMX,BlueDragon
Total Views: 86,025
Submission Date: January 13, 2005
Last Update Date: June 05, 2009
All Tutorials By This Autor: 2
Discuss This Tutorial
  • In step 4, if we wanted additional columns, would we need to execute the code like this: exec sp_fulltext_column 'Recipes', ,'Description', 'add' exec sp_fulltext_column 'Recipes', ,'Ingredients', 'add' exec sp_fulltext_column 'Recipes', ,'Title', 'add' Or like this: exec sp_fulltext_column 'Recipes', ,'Description', 'add' exec sp_fulltext_column 'Recipes', ,'Ingredients', 'add' exec sp_fulltext_column 'Recipes', ,'Title', 'add'

  • perhaps a stupid question... but once we allow searching in this manner, how does one translate it into a list of related links that would work on a website?


Website Designed and Developed by Pablo Varando.