Sql Server Full-Text Search Protips Part 1: Setup

Wyatt Barnett
Wyatt Barnett

As I have previously mentioned, Sql Server’s Full-Text search can let a developer create some very slick features disturbingly easily. And, unlike many other Full-Text implementations, it is not limited to plain text fields. It can also search within binary fields with the proper setup considerations. Needless to say, there are a few protips to making Full-Text indexes work and taking advantage of them. In this post, I will tell you how to get Full-Tex

Getting Started

First, you are going to need a copy of Sql Server 2000 or 2005 Standard edition. MSDE or Sql Express do not have Full-Text capabilities. In order to enable Full-Text search, you must do a few things.

  • Enable Full-Text search on the database.
  • Create a Full-Text catalog.
  • Enable Full-Text searching on specific columns in your data.

To do this, we can run some system stored procedures. I should note that these are depereciated in 2005 in favor of DDL statements, but they still work and are a bit simpler to digest. Here is an example of the SQL necessary to create a Full-Text index in a database:

exec sp_fulltext_database 'enable'
exec sp_fulltext_catalog 'Catalog_Name', 'create'

Table Design Considerations

Once there is a Full-Text index in place, you can start indexing columns. But lets not get ahead of ourselves. First and foremost, using fulltext indexing requires a single unique key on the table. Second, for any table where you are using Full-Text indexes, you really should have a TIMESTAMP column. This is a small binary column that is automatically updated whenever the row is changed. It is necessary as it acts as a flag for the indexing engine to allow it to incrementally index the table.

There is one other considerations for using Full-Text indexing on binary fields, such as word documents stored in a database. And that consideration is you must have a text field with the file extension (‘doc’ for word, for example). This is because the indexing needs to know what kind of file it is dealing with in order to parse the file and find the data. Out of the box, Sql Server can index text files, html files and Word documents. But it can be extended using IFilters. For example, Adobe provides a PDF IFilter for your use.

Enabling Full-Text Indexing on a Table & Columns

Lets say we have a rather simple table we wish to index, document_attachments. It has six fields:

  • Id (INT IDENTITY NOT NULL, primary key)
  • FileName (varchar(255) NOT NULL)
  • FileDescription(varchar(1000) NULL)
  • FileExtension (varchar(10) NOT NULL)
  • SaveStamp (TIMESTAMP)
  • FileData (IMAGE)

In order to allow for fulltext queries on your table, you must first enable the table for fulltext indexing, then enable fulltext indicies on specific columns. If we wished to create a fulltext index on the FileDescription & FileData fields, we would use the following T-SQL:

exec sp_fulltext_table @tabname='document_attachments', @action='create', @ftcat=[CatalogName], @keyname=[Name of Primary Key index]
exec sp_fulltext_column @tabname='document_attachments', @colname='FileName', @action='add'
exec sp_fulltext_column @tabname='document_attachments', @colname='FileData', @action='add', @type_colname='FileExtension'

Indexing Considerations

Now, before you use any fulltext catalog, it must be indexed. There are two separate scenarios here. For “normal fields”, that is fields that are not IMAGE or TEXT, Sql Server is capable of automatically tracking changes to columns for you. And, if you are working with Sql 2005, you really should be using VARCHAR(MAX) and VARBINARY(MAX), so this will not be a problem for some. But those of us still working on Sql 2000 will be required to setup an indexing schedule on any of the old school long text or binary columns in order to keep things fresh. Exactly how often to index depends on the application and your requirements. Do remember it is a bit of an expensive process, so you do not want to fire it off every 15 seconds to keep things “fresh.”

In any case, to enable change tracking (automated indexing) on a table, use the following T-SQL:

exec sp_fulltext_table 'document_attachments', 'start_change_tracking'

As for setting up the schedule, the best way is to fire up enterprise manager, find your Full-Text catalog, right click on it and choose Schedules. Then setup whatever schedule you feel appropriate.

But Wait Wyatt! Isn’t there more!

Why yes, there is more. Stay tuned for the next installment of Sql Server Full-Text Protips: Using Full-Text Queries for Fun and Profit.

Thanks and kick it if you like it.

How can I improve the performance of my SQL Server Full-Text Search?

Performance optimization is crucial for any database system. For SQL Server Full-Text Search, you can improve performance by regularly updating and optimizing the full-text catalog. This can be done by using the ALTER FULLTEXT CATALOG command with the REORGANIZE option. Additionally, consider using a dedicated filegroup for your full-text catalog to isolate it from other database activities.

What are the limitations of SQL Server Full-Text Search?

While SQL Server Full-Text Search is a powerful tool, it does have some limitations. For instance, it does not support wildcard searches at the beginning of a word, and it does not support exact phrase searches with noise words. Also, it may not return expected results with inflectional forms of certain words.

How can I troubleshoot issues with SQL Server Full-Text Search?

Troubleshooting SQL Server Full-Text Search can be done using various methods. You can use the sys.dm_fts_index_keywords system function to view the full-text index keywords for a specified table or indexed view. Additionally, you can use the FULLTEXTCATALOGPROPERTY function to return the property value of a full-text catalog.

How can I use SQL Server Full-Text Search with other languages?

SQL Server Full-Text Search supports multiple languages. You can specify the language when creating a full-text index using the LANGUAGE option. This allows the Full-Text Engine to use the appropriate word breaker and stemmer for the specified language.

Can I use SQL Server Full-Text Search with other data types?

Yes, SQL Server Full-Text Search supports various data types, including char, varchar, nchar, nvarchar, text, ntext, image, xml, and varbinary(max). However, for binary data types, you need to install and register the appropriate filter.

How can I secure my SQL Server Full-Text Search?

Security is a critical aspect of any database system. For SQL Server Full-Text Search, you can secure it by granting or denying permissions to users. You can use the GRANT, DENY, and REVOKE statements to manage permissions.

How can I monitor the status of my SQL Server Full-Text Search?

Monitoring the status of SQL Server Full-Text Search can be done using the FULLTEXTCATALOGPROPERTY function. This function returns the property value of a full-text catalog, which can provide information about the status of the catalog.

Can I use SQL Server Full-Text Search with large databases?

Yes, SQL Server Full-Text Search is designed to work with large databases. However, performance may be affected with very large databases. Regularly updating and optimizing the full-text catalog can help improve performance.

How can I use SQL Server Full-Text Search with XML data?

SQL Server Full-Text Search supports XML data. You can create a full-text index on an xml data type column and use the CONTAINS or FREETEXT predicates to search for XML data.

Can I use SQL Server Full-Text Search with spatial data?

No, SQL Server Full-Text Search does not support spatial data types. However, you can use other SQL Server features, such as spatial indexes, to search for spatial data.