Skip to main content

Populating the Full-Text Index in Search: SQL Server

After creating the full-text index, you need to populate it with the data in the columns enabled for full-text support. The SQL Server full-text search engine populates the full-text index through a process called population. Population involves filling the index with words and their location in the data page. When a full-text index is created, it is populated by default. In addition, the SQL Server automatically updates the full-text index as the data is modified in the associated tables.

However, the SQL Server does not keep a list of changes made to the indexed data when the CHANGE_TRACKING option is off. This option is specified while creating the full-text index by using the CREATE FULLTEXT INDEX statement.

If you do not want the full-text index to be populated when it is created using the CREATE FULLTEXT INDEX statement, then you must specify NO POPULATION along with the CHANGE TRACKING OFF option. To populate the index, you need to execute the ALTER FULLTEXT INDEX command along with the START FULL, INCREMENTAL, or UPDATE POPULATION clause.

For example, to create an empty full-text index on the ProductDescription table, you can execute the following statement:

CREATE FULLTEXT INDEX ON Production. ProductionDescription (Description)
KEY INDEX PK_ProductDescription_ProductDescriptionID
WITH CHANGE_TRACKING OFF, NO POPULATION

To populate the index you need to execute the following statement:
ALTER FULLTEXT INDEX ON Production. ProductDescription START FULL POPULATION

The preceding statement will populate the full-text index created on the ProductDesctiption table.

Similar to regular SQL indexes, full-text indexes can also be updated automatically as the data is modified in the associated tables. This repopulation can be time-consuming and adversely affect the usage of resources of the database server during periods of high database activity. Therefore, it is better to schedule repopulation of full-text indexes during periods of low database activity. You can specify the following types of full-text index population methods to repopulate the index:

Full Population

You can use this method when you need to populate the full-text catalog or the full-text index for the first time. After that, you can the maintain the indexes by using change tracking or incremental populations.
During a full population of a full-text catalo, index entries are built for all the rows in all the tables covered by the catalog. If a full population is requested for a table, index entries are built for all the rows in that table.

Change Tracking-Based Population

The SQL Server maintains a record of the rows that have been modified in a table set up for full-text indexing. These changes are propagated to the full-text index.

Incremental Timestamp-Based Population

The incremental population method updates the full-text index with the data that has been changed since the last time the index was refreshed. For an incremental population refresh to work, the indexed table must have a column of the timestamp data type. If a table does not have a column of the timestamp data type, then only a full population refresh can be done.

Comments

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 …