Issue: 1, Full-Text Search has changed in SQL Server 2008

Full-Text Search in SQL SERVER 2005

Full-text search allows fast and flexible indexing for keyword-based query of text data stored in a Microsoft SQL Server database. In contrast to the LIKE predicate, which only works on character patterns, full-text queries perform linguistic searches against this data, by operating on words and phrases based on rules of a particular language.

To create a full-text index on a table, the table must have a single, unique not null column. For example, consider a full-text index for the Document table in Adventure Works in which the DocumentID column is the primary key column. A full-text index indicates that the word “instructions” is found at word number 24 and word number 44 in the DocumentSummary column for the row associated with a DocumentID of 3. This index structure supports an efficient search for all items containing indexed words and advanced search operations, such as phrase searches and proximity searches.

When processing a full-text query, the search engine returns to SQL Server the key values of the rows that match the search criteria. If you want to use a full-text query to find the documents that include the word “instructions”, the DocumentID values of 3, 4, 6, 7 and 8 are obtained from the full-text index. SQL Server then uses these keys to return the matching rows.

The following example creates a full-text index on the HumanResources.JobCandidate table.

USE AdventureWorks;
CREATE UNIQUE INDEX ui_ukJobCand ON HumanResources.JobCandidate(JobCandidateID);
CREATE FULLTEXT INDEX ON HumanResources.JobCandidate(Resume) KEY INDEX ui_ukJobCand;
Creates a full-text index on one or more columns of a table or indexed view in a database. Only one full-text index is allowed per table or indexed view. By default, a database is enabled for full-text search when the database is created.

Here is a list of terms and components that you need to be familiar with when using Full-Text Search.

Full-text index
Stores information about significant words and their location within a given column. This information is used to quickly compute full-text queries that search for rows with particular words or combinations of words. For more information, see Full-Text Indexes.

Full-text catalog
A full-text catalog contains zero or more full-text indexes. Full-text catalogs must reside on a local hard drive associated with the instance of SQL Server. Each catalog can serve the indexing needs of one or more tables within a database. Full-text catalogs cannot be stored on removable drives, floppy disks, or network drives, except when you attached a read-only database that contains a full-text catalog.

Word breaker
For a given language, a word breaker tokenizes text based on the lexical rules of the language. For more information, see Word Breakers and Stemmers.

Is a word or a character string identified by the word breaker.

For a given language, a stemmer generates inflectional forms of a particular word based on the rules of that language. Stemmers are language specific. For more information, see Word Breakers and Stemmers.

Given a specified file type, for example .doc, filters extract text from a file stored in a varbinary(max) or image column. For more information, see Full-Text Search Filters.

Population or Crawl
Is the process of creating and maintaining a full-text index. For more information, see Full-Text Index Structure.

Noise words
Are frequently occurring words that do not help the search. For example, for the English locale words such as “a”, “and”, “is”, and “the” are considered noise words. These words are ignored to prevent the full-text index from becoming bloated. For more information, see Noise Words.

Full-Text Search in SQL SERVER 2008 R2

SQL Server 2008 provides the functionality for applications and users to issue full-text queries against character-based data in SQL Server tables. Before full-text queries can be run on a given table, the database administrator must create a full-text index on the table. The full-text index includes one or more character-based columns in the table. These columns can have any of the following data types: char, varchar, nchar, nvarchar, text, ntext, image, xml, or varbinary(max). Each full-text index indexes one or more columns from the base table, and each column can have a specific language. Beginning in SQL Server 2008, full-text search supports more than 50 diverse languages, such as English, Spanish, Chinese, Japanese, Arabic, Bengali, and Hindi. For a complete list of the supported full-text languages

It is same as like SQL Server 2012. For more details refer the section below,

Full-Text Search in SQL SERVER 2012

Full-Text Search in SQL Server and Azure SQL Database lets users and applications run full-text queries against character-based data in SQL Server tables. Before you can run full-text queries on a table, the database administrator must create a full-text index on the table. The full-text index includes one or more character-based columns in the table. These columns can have any of the following data types: char, varchar, nchar, nvarchar, text, ntext, image, xml, or varbinary(max)and FILESTREAM.

Full-text queries are not case-sensitive. For example, searching for “Aluminum” or “aluminum” returns the same results.

Full-text queries use a small set of Transact-SQL predicates (CONTAINS and FREETEXT) and functions (CONTAINSTABLE and FREETEXTTABLE). However, the search goals of a given business scenario influence the structure of the full-text queries. For example:

  • e-business—searching for a product on a website:
    SELECT product_id 
    FROM products 
    WHERE CONTAINS(product_description, "Snap Happy 100EZ"
        OR FORMSOF(THESAURUS,'Snap Happy')
        OR '100EZ') 
    AND product_cost < 200 ;
  • Recruitment scenario—searching for job candidates that have experience working with SQL Server:
    SELECT candidate_name,SSN 
    FROM candidates 
    WHERE CONTAINS(candidate_resume,"SQL Server") AND candidate_division = 'DBA';

Comparing LIKE to Full-Text Search

 In contrast to full-text search, the LIKETransact-SQL predicate works on character patterns only. Also, you cannot use the LIKE predicate to query formatted binary data. Furthermore, a LIKE query against a large amount of unstructured text data is much slower than an equivalent full-text query against the same data. A LIKE query against millions of rows of text data can take minutes to return; whereas a full-text query can take only seconds or less against the same data, depending on the number of rows that are returned.

Full-text search architecture consists of the following processes:

  • The SQL Server process (sqlservr.exe).
  • The filter daemon host process (fdhost.exe).For security reasons, filters are loaded by separate processes called the filter daemon hosts. The fdhost.exe processes are created by an FDHOST launcher service (MSSQLFDLauncher), and they run under the security credentials of the FDHOST launcher service account. Therefore, the FDHOST launcher service must be running for full-text indexing and full-text querying to work. For information about setting the service account for this service, see Set the Service Account for the Full-text Filter Daemon Launcher.

These two processes contain the components of the full-text search architecture. These components and their relationships are summarized in the following illustration. The components are described after the illustration.

full-text search architecture
How to fix the Issue?

Backup and restore the database

Restore a database that has been backed up in a former version of SQL Server. If this database contains full-text catalogs, these must also be upgraded to the new architecture when you restore.

Note that in SQL Server 2008, there is no concept of backing up or restoring a full-text catalog between 2008 instances, as this is just a logic concept. You can, however, back up or restore individual full-text indexes in a given database filegroup. You just need to specify the particular files within a given filegroup that contain the full-text index. At full-text index creation time, the user can specify in which filegroup the full-text index should be created. For more information, see SQL Server Books Online.

How to check Full-Text Catalog exists in Database?

You can check the full-text catalog by expanding the database -> Storage folder as shown in the screenshot below,

Full Text Catalog

On click of Active Full Text Catalog in the menu it will load a report as shown below,

Full Text Catalog Result


Fortunately, I see there is no Full-Text Catalogs.

So there is no fix required.

I can move on to the next issue 🙂

Important Note:

If you want to know more about Full-Text Search Issue and fix you can refer the document below,


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s