Full-text Search is an optional component of SQL Server that allows fast and efficient querying of large amounts of unstructured data.  Unlike character-based comparisons used in SQL LIKE expressions, full-text search performs linguistic searches by operating on words or phrases.  Querying large amounts of data using full-text search can provide significant performance gains over character-based searches as well as gains in usability.

For example, running a LIKE query against millions of rows of text data may take tens of seconds or even minutes to return; whereas a full-text query can take only seconds or less against the same data.

Doing linguistic searches instead of character-based searches also adds other levels of usability. For example, when searching on a given word, full-text search not only tries to match on the given word, but can also match on forms of the given word or even on other words that have similar meaning.  For example, searching on “house” can also yield matches on “houses”, “housing”, or even “home”.

Instructions for setting up and configuring full-text search in SQL Server can be found in the relevant SQL Server documentation.  This document describes how Iron Speed Designer makes use of full-text search.

Iron Speed Designer supports integration with Full-text Search in SQL Server 2008 databases. 

Supported data types

Full-text indexes may be created on the following SQL Server data types:

char, varchar, nchar, nvarchar, text, ntext, image, xml, varbinary, or varbinary(max)

Iron Speed Designer supports querying on all these data types using Full-text Search.

Full-text Search supports indexing of documents including  Microsoft Word, Excel, PDF, etc.  stored in varbinary, varbinary(max), image, or xml fields. This means you can store documents in your database and easily allow users to search for content contained in those documents.

Examples used in this document

This document gives examples from the Production.Document table in the SQL Server 2008 AdventureWorks example database. Example documents stored in this table include various guides on bicycle maintenance. 

Finding full-text indexes

When Iron Speed Designer queries database schema, it looks for full text indexes.  Fields with full-text indexes can be seen in the Databases area. 

Adding full-text fields to search filters

The main benefit of integrating full-text search in a generated application is to allow users to do free-form searches using search filters.  For example, using the Document table in the Adventureworks database, the Document field which contains a full-text index can be included as one of the fields in a search filter.

Simple full-text queries

Once full-text-enabled fields have been added to search filters, users can enter simple search strings that utilize full-text search capabilities.   These are entered in the “Search for” filter on the page – the same place where non-full-text searches are done.

Examples of simple search strings are:

The SQL queries that are generated by Iron Speed Designer make use of full-text “CONTAINS” clauses.  In the Single word example the resulting CONTAINS clause looks like:

WHERE CONTAINS(Document, ' (FORMSOF(INFLECTIONAL, bracket) ')

Notice that it utilizes the FORMSOF and INFLECTIONAL qualifiers, which mean to match not only on the exact word but also on other forms of the word.

In the Multi-word example the resulting clause is similar but also contains an AND condition:

WHERE CONTAINS(Document, ' (FORMSOF(INFLECTIONAL, crank) AND FORMSOF(INFLECTIONAL, spindle)) ') 

In Quoted example, the query tries to find exact matches.

WHERE CONTAINS(Document, ' "crank arm" ')

NOTE:  Full-text queries are always case-insensitive.

NOTE:  Iron Speed Designer does not return matches with highlighting as in the previous table. Those are examples to show how matching is done.  Search results are returned as matching record rows such as:

Search filters may contain a combination of both full-text enabled fields and regular non-full-text fields.  For example the Document table in the Adventureworks database contains a full-text index on the Document field, but no full-text index on either the FileName or DocumentSummary fields. These can all be combined into one search filter.

The result is that when generating a SQL query, the full-text-enabled fields will cause “CONTAINS” clauses to be added and the non-full-text-enabled fields will cause “LIKE” clauses to be added. 

Advanced full-text queries

Iron Speed Designer also supports more advanced forms of full-text searching that make use of keywords and special characters. 

Keyword and special character terms can be combined in several ways such as:

·        hardware and assemb*

·        washer or ~seat

·        tire* not ~nut

Known limitations with keyword and special character searches:

·         Keyword and special character search terms are designed to work in simple cases.  More complex cases with multiple combinations of keywords and special characters may result in complex queries that do not return expected results.

·         One case that is known to return no results is combining two “not” terms such as
“-word1 –word2”. 

Searching in multiple fields and computed columns

When you add more than one  full text search enabled field to the search control you need to keep in mind that Iron Speed Designer generated application constructs the where clause on column by column basis. So for example in you include two fields ‘Document’ and ‘Title’ and search for ‘hardware seat’ as in example above, application will produce WHERE clause like this:

WHERE Contains([TableName].[Document],’FORMSOF(INFLECTIONAL, hardware) AND FORMSOF(INFLECTIONAL, seat))’)

Or Contains([TableName].[Title], ’FORMSOF(INFLECTIONAL, hardware) AND FORMSOF(INFLECTIONAL, seat))’)

While this will perfectly work on all contains and like queries when you are searching for the strings to be included, that has unexpected results for searches with exclusion clause. For example the search on the same columns for ‘hardware –seat’ will produce WHERE clause as:

WHERE Contains([TableName].[Document],’FORMSOF(INFLECTIONAL, hardware) AND NOT FORMSOF(INFLECTIONAL, seat))’)

Or Contains([TableName].[Title], ’FORMSOF(INFLECTIONAL, hardware) AND NOT FORMSOF(INFLECTIONAL, seat))’)

So if you have a record where Title is ‘Comfortable seat’ and Document is ‘Our hardware shop is the best’ it will be returned by this query because Document field satisfies search criteria and the fact that Title is not does not matter.

If you need to be able to exclude results based on any column (so that if excluded string matches a string in any of the columns included in the search record is excluded also) the solution is to create full text search enabled persistent computed column where concatenate all your search columns and search only in this column. This approach has additional benefit of a better performance also.

Adding full-text fields to where clauses

In addition to support for full-text queries in search filters, Iron Speed Designer allows full-text-enabled fields to be included in WHERE clauses. 

WHERE clauses are added using the “Add WHERE Clause… feature”.  The clause editor allows selection of a table and field, and operator, and a value.

The clause operators that work with full-text-enabled fields are: