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
USE AdventureWorks; GO CREATE UNIQUE INDEX ui_ukJobCand ON HumanResources.JobCandidate(JobCandidateID); CREATE FULLTEXT CATALOG ft AS DEFAULT; CREATE FULLTEXT INDEX ON HumanResources.JobCandidate(Resume) KEY INDEX ui_ukJobCand; GO
- 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';
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.
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,
On click of Active Full Text Catalog in the menu it will load a report as shown below,
Fortunately, I see there is no Full-Text Catalogs.
So there is no fix required.
I can move on to the next issue 🙂
If you want to know more about Full-Text Search Issue and fix you can refer the document below,