Skip to main content

Configuring Full-Text Search in SQL Server

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:
  • 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
Sp_fulltext_database enable

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:

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

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.


Popular posts from this blog

difference between structure and union in C Language

In c language article we will see the difference between union and structure. Both are the user define datatype in c language. See the table which is mentioned below: ASP.NET Video Tutorial Series Structure Union1.The keywordstruct is used to define a structure 1. The keyword union is used to define a union. 2. When a variable is associated with a structure, the compiler allocates the memory for each member. The size of structure is greater than or equal to the sum ofsizes of its members. The smaller members may end with unused slack bytes. 2. When a variable is associated with a union, thecompiler allocates thememory by considering the size of the largest memory. So, size of union is equal to the size of largest member. 3. Each member within a structure is assigned unique storage area of location. 3. Memory allocated is shared by individual members of union. 4. The address of each member will be in ascending order This indicates that memory for each member will start at different offset v…

Difference between Linear search and Binary Search in c language

SQL Video Channel : Download all SQL Video

Binary Search Linear Search Works only on sorted items. such as  1,2,3,4,5,6  etc
Works on sorted as well as unsorted items. 12,4,5,3,2,1 etc Very efficient if the items are sorted Very efficient if the items are less and present in the beginning of the list. such as Suppose your list items are : 12,3,4,5,1 and you want to search 12 number then you get beginning in the list. Works well with arrays and not on linked lists. Works with arrays and linked lists.
Number of comparisons are less More number of comparisons are required if the items are present in the later part of the array or its elements are more.

Memory representation of Linked List Data Structures in C Language

Memory representation of Linked List

             In memory the linked list is stored in scattered cells (locations).The memory for each node is allocated dynamically means as and when required. So the Linked List can increase as per the user wish and the size is not fixed, it can vary.

               Suppose first node of linked list is allocated with an address 1008. Its graphical representation looks like the figure shown below:

      Suppose next node is allocated at an address 506, so the list becomes,

  Suppose next node is allocated with an address with an address 10,s the list become,

The other way to represent the linked list is as shown below:

 In the above representation the data stored in the linked list is “INDIA”, the information part of each node contains one character. The external pointer root points to first node’s address 1005. The link part of the node containing information I contains 1007, the address of next node. The last node …