FREETEXT Searches with SQL Server and ADO.NET
Data is king. Companies such as Google and eBay are based primarily on the value of their data. (In Google's case, the data is actually indexes of data.) Providing access to data often is either mission critical to a company or the company's raison d'être.
Recently, while working on Motown-jobs.com, I needed to implement fuzzy searches of job listings and résumés. Rather than write this code from scratch, which can be time-consuming and error-prone, I employed the Microsoft Search Engine. The MS Search engine supports searching character-based data "for values that match the meaning and not the exact wording of words."
One of the uses for the search engine is to index character-based columns in SQL Server database tables and then use the FREETEXT predicate in SQL statements to find matches. FREETEXT parses the search string, weights pieces of the search string, and then finds matches. This can be especially useful when searching things like résumés where desired skills may not be worded precisely.
This article shows you how to install, configure, and use the MS Search Engine with SQL Server.
Configuring the Microsoft Search Engine
The Microsoft Search Engine ships with MS SQL Server (Developer and Enterprise Editions, but not Desktop Engine). However, the search engine is not installed by default. To install it, follow these steps (These instructions describe adding the search engine to an existing instance of SQL Server 2000 Developer, but the Enterprise installation is identical.):
- Insert your MS SQL Server installation disk.
- On the first installation screen, select "SQL Server 2000 components." (SQL Server 7.0 or higher is required.)
- On the second screen, select Install Database Server.
- In the setup wizard, select "Local" or "Remote" and click Next.
- On the second wizard step, select "Upgrade, remove, or add components to an existing instance of SQL Server" and click Next.
- For the Instance Name step, just click Next.
- On the Existing Installation step, make sure "Add components to your existing installation" is selected and click Next.
- On the Select Components step, make sure that "Full Text Search" is selected (see Figure 1) and complete the installation.
Figure 1: Add Full-Text Search to Your SQL Server Installation
Start the MS Search Engine
After you have installed the full search capabilities, Microsoft Search will show up in the SQL Server Service Manager (see Figure 2). Make sure that Microsoft Search is in the running state. You also can open the Services applet and make sure that MS Search is set to run automatically at startup.
Figure 2: Stop and Start Microsoft Search from the SQL Server Service Manager
Tip: If you are going to use services like MS Search, you should test your applications with the service in the started and stopped states to make sure the application behaves even if the service fails.
Indexing Tables for Full-Text Searches
When Microsoft Search is installed and running, you will have some additional options in SQL Server Enterprise Manager. These options are designed to permit configuring full-text searching and are accessible from Table context menus. For example, to permit full-text searching on Motown-jobs.com's résumé table, I completed these steps:
- Open SQL Server Enterprise Manager.
- Expand the Console Root, Microsoft SQL Servers, SQL Server Group, the database server, the Databases folder, and click the Tables node.
- Right-click on the table and select Full-Text Index Table|Define Full-Text Indexing on a Table (see Figure 3).
- Complete the wizard by selecting from the listing of available character-based columns to index.
- Finally, select the table again and from the Full-Text Index Table sub-menu select Start Full Population. (You also can create a schedule that will regularly re-index the table.)
Figure 3: The Full-Text Index Table Context Menu
Having installed the search engine and configured full-text searching for desired tables, you are ready to test the feature. The quickest test is to open SQL Query Analyzer (see Figure 4) from the Tools menu of Enterprise Manager and write a SELECT statement in which the WHERE clause uses the FREETEXT predicate.
Figure 4: SQL Query Analyzer Showing the FREETEXT Predicate in Action
Listing 1 contains a SQL statement for Motown-jobs.com's Resumes table.
Listing 1: A SQL Command Using the FREETEXT Predicate
SELECT * FROM RESUMES WHERE FREETEXT(ResumeContent, 'uml modeler')
When you have confirmed that the search engine is installed and working correctly, you can incorporate the FREETEXT predicate into your queries and stored procedures. The only challenge I have found is when switching back and forth between a deployment machine and a developer's box with Microsoft SQL Server Desktop Engine (MSDE). Because MSDE doesn't support FREETEXT searching, these queries will break during development, making them hard to test.
If you write embedded queries, then you can toggle between code that uses FREETEXT statements and code that doesn't with #if DEBUG conditional code. However, if you use stored procedures, as I do, you will need to script slightly different versions of your database for development boxes.
Some Installation Required
The Microsoft Search Engine is a reusable component that you use with other tools like Exchange, Sharepoint, and BizTalk. For this reason, it is an external tool that needs to be installed and configured separately from SQL Server. However, a copy of MS Search can be installed from your SQL Server installation disks. Once installed, MS Search permits you to perform fuzzy searches of small and large amounts of character-based data.
About the Author
Paul Kimmel has written several books on object-oriented programming and .NET. Check out his upcoming book UML DeMystified from McGraw-Hill/Osborne (Fall 2005). Paul is also the founder and chief architect for Software Conceptions, Inc., founded in 1990. He is available to help design and build software worldwide. You may contact him for consulting opportunities or technology questions at firstname.lastname@example.org.
If you are interested in joining, sponsoring a meeting, or posting a job, check out www.glugnet.org, the Web page of the Greater Lansing area Users Group for .NET.
Copyright © 2005 by Paul T. Kimmel. All Rights Reserved.