The full-text query feature in the SQL Server enables users to search for a wide range of text in the SQL tables. Consider an example. The sales management team of AdventureWorks, Inc. makes frequent searches on the ProductDescription table to develop marketing strategies. The search is based on the data stored in the Description column of the table.
A bike racing competition is scheduled to begin in Texas. The sales manager of AdventureWorks wants to see the details of all the bikes that are related to racing, so that a marketing strategy can be designed to increase the sale of these bikes. Specifically, he wants a list of all the bikes that have the keyword ‘race winner’ in the description.
As the data is large, the search query takes a long time to retrieve data from the table. In this scenario, you can apply a full-text index on the Description column of the ProductDescription table to improve the speed of searching.
To retrieve the required details by using full-text search, you need to configure full-text search on the database. For this, you need to perform the following tasks:
USE AdventureWorks
GO
Sp_fulltext_database enable
GO
CREATE FULLTEXT CATALOG cat1 AS DEFAULT
CREATE UNIQUE INDEX Ix_Desc ON Production.ProductDescription
(ProductDescriptionID)
There are certain words that are used often and may hinder a query. These words are called noise words and are excluded from the search string. For example, if you search string is “Who is the governor of California”, a full-text search will not look for words, such as ‘is’ and ‘the’. Some noise words are a, an, the, and are.
Based on the preceding scenario, you can create a full-text index on the ProductionDescription table. This index is based on the lx_desc index created earlier on the Description column of the table.
Note: You can also create full-text index in the Object Explorer window by right-clicking the table, on which you need to create the full-text index, and selecting Full-Text index-Define Full-Text Index.
A bike racing competition is scheduled to begin in Texas. The sales manager of AdventureWorks wants to see the details of all the bikes that are related to racing, so that a marketing strategy can be designed to increase the sale of these bikes. Specifically, he wants a list of all the bikes that have the keyword ‘race winner’ in the description.
As the data is large, the search query takes a long time to retrieve data from the table. In this scenario, you can apply a full-text index on the Description column of the ProductDescription table to improve the speed of searching.
To retrieve the required details by using full-text search, you need to configure full-text search on the database. For this, you need to perform the following tasks:
- Enable the full-text search in the database.
- Create a full-text catalog.
- Create a unique index.
- Create a full-text index.
- Populate the full-text index.
Enabling the Full-Text Search in the Database
Before using the full text search feature of the SQL Server, you need to enable the database using the following statement:USE AdventureWorks
GO
Sp_fulltext_database enable
GO
Creating a Full-Text Catalog
A full-text catalog serves as a container to store full-text indexes. After enabling the full text search, you need to create a full-text catalog. A full-text catalog is a container that contains full-text indexes. A full-text catalog may have multiple full-text indexes. You can create a full-text catalog by using the following command:CREATE FULLTEXT CATALOG cat1 AS DEFAULT
Creating a Unique Index
After creating the full-text catalog, you need to identify a unique index on the table. This unique index will be mapped to the values in the full-text index. You can use an existing unique index defined on the table, or create a new one. For example, you can create a unique index on the Production.ProductDescription table, as shown in the following statement:CREATE UNIQUE INDEX Ix_Desc ON Production.ProductDescription
(ProductDescriptionID)
Creating a Full-Text Index
After you have created the full-text catalog and a unique index, you can create a full-text index on the table. A full-text index stores information about significant words and their location within a given column. You can use this information to compute full-text queries that search for rows with particular words or combinations of words. Full-text indexes can be crated on the base tables but not on the views or the system tables.There are certain words that are used often and may hinder a query. These words are called noise words and are excluded from the search string. For example, if you search string is “Who is the governor of California”, a full-text search will not look for words, such as ‘is’ and ‘the’. Some noise words are a, an, the, and are.
Based on the preceding scenario, you can create a full-text index on the ProductionDescription table. This index is based on the lx_desc index created earlier on the Description column of the table.
Note: You can also create full-text index in the Object Explorer window by right-clicking the table, on which you need to create the full-text index, and selecting Full-Text index-Define Full-Text Index.