SQL Server 2005 features an integrated full-text search engine, which several people have e-mailed me to ask about recently. (ColdFusion users can use
If you want to play with SQL Server 2005 full-text search capabilities, you need to do the following:
1) Once a database has been created, support for full-text must be enabled before any full-text operations can be performed. To enable full-text support the sp_fulltext_database stored procedure is used. This stored procedure updates the currently selected database, so be sure to USE the correct database before issuing this statement:
EXEC sp_fulltext_database 'enable';
If you are using SQL Server Management Studio and use the interactive New Database dialog to create your database, you can check the “Use full-text indexing” checkbox, which causes the above mentioned stored procedure to be automatically executed.
2) SQL Server stores full-text data in a catalog (a file that needs to be created). A single catalog can be used for multiple tables and indexes. If a catalog does not already exist, create one using CREATE FULLTEXT CATALOG:
CREATE FULLTEXT CATALOG my_catalog;
This creates a catalog named my_catalog in the default catalog location. To specify the actual file location the IN PATH attribute can be specified.
3) Once a catalog has been created you can define the actual full-text indexes for each table containing columns that you want searchable. Indexes are created using CREATE FULLTEXT INDEX like this:
CREATE FULLTEXT INDEX ON table(column)
KEY INDEX table_primary_key
ON my_catalog;
When creating a full-text index you specify the table and column to be indexed as table(column). More than one column may be indexed if needed, to do this simply specify the column names (comma delimited). The key with which to uniquely identify rows is required, and so KEY INDEX is used to provide the name of the table’s primary key. And finally, the ON clause specifies the catalog to be used to store full-text data, and here the just created catalog is used (unless a default catalog has been defined).
Once set up, you’ll be ready to use the CONTAINS and FREETEXT functions in your WHERE clauses to perform full-text searching.
Setting Up SQL Server 2005 Full-Text Searching
6 responses to “Setting Up SQL Server 2005 Full-Text Searching”
-
Interesting! Do you know whether you have to manually re-index on insert/update/delete or whether SQL 2005 handles it for you (and if so whether per transaction, nightly, etc.)? Also any idea of the performance hit for larger tables with frequent updates if re-indexing is per transaction?
-
Peter, it’s configurable actually. By default indexes are updated in real-time, but you can make it happen on demand )maybe a scheduled event, calling ALTER FULLTEXT CATALOG catalogName REBUILD;. You can check index build status with SELECT * FROM sys.fulltext_indexes;, and catalog with SELECT * FROM sys.fulltext_catalogs;. Checking the status lets you know if the indexing is up to date, in progress, etc.
— Ben -
Very cool – thanks. One more reason to consider porting to 2005!
-
There is always the "old" method of creating a verity collection on your database info, and then using that to search (depending on what you are trying to accomplish)…
-
Thank you. You have saved me hours. I could kiss you 🙂
-
Can you please provide some search examples using ASP Classic, using parameterized (safe) SQL coding?
Leave a Reply