Applying Index, View, As Well As Full – Text Search Part – 8

Activating The Full – Text Searching Technique In The Database

The individual is required to activate the database, before practicing the full – text searching technique in the SQL Server. The subsequent command does that:

USE XYZBank

GO

SP_FULLTEXT_DATABASE ENABLE

GO

Forming A Full – Text Catalog

The full – text catalog helps as a receptacle for keeping the full – text indexes. When the full – text searching technique is activated, an individual is required to form a full – text catalog. The full – text catalog is a receptacle which holds full – text indexes. The full – text catalog might have numerous full – text indexes. An individual can form a full – text catalog by means of the subsequent statement:

CREATE FULLTEXT CATALOG Loan_Catalog AS DEFAULT

Forming A Unique Index

Once the full – text catalog is formed, the individual is required to recognize a unique index on the table or relation. This particular unique index is going to be plotted to the information in the full – text index. The individual may practice using a present unique index demarcated on the particular table or relation, otherwise may form a fresh one. For an instance, the individual can form a unique index on the Management . LoanDetails table or relation, as presented in the subsequent command:

CREATE UNIQUE INDEX INDX_LoanType ON Management . LoanDetails ( LoanID )

Forming A Full – Text Index

Once, an individual have formed the full – text catalog as well as a unique index, the individual now can form a full – text index on the particular table or relation. The full – text index stokes data about the important words plus their position inside a given column or attribute. An individual can make use of this data to calculate the full – text queries which search for rows or tuples with specific words or else amalgamations of words. The full – text indexes can be formed on the underlying tables or relations or base tables or relations however it cannot be formed on the views or on the system tables or relations.

There are few words which are castoff frequently in addition it might hamper a query. These words are known as Noise Words plus they are omitted from the searching sequence. For an instance, if the searching sequence is “ What is the radius of the Earth ?” a full – text searching technique will not include the words, like “ is ” and “ the ”. A number of noise words are “ a , an , the , are , is, was , were ”.

Grounded on the above situation, an individual can form the full – text index on the Type column or attribute, as displayed in the subsequent command:

CREATE FULLTEXT INDEX ON Management . LoanDetails ( Type ) KEY INDEX INDX_LoanType

The above command will be forming the full – text index on the Management . LoanDetails table or relation. This index is grounded on the INDX_LoanType index formed previously on the LoanID column or attribute of the table or relation.

The individual may form the full – text index in the Object Explorer window through right – clicking the table or relation too, on which the individual wants to form the full – text index, then choosing Full – Text Index option Define Full – Text Index option.

Generating The Full –Text Index

When the full – text index is formed, an individual is required to generate it by the information in the column or attributes allowed for full – text sustenance. The SQL Server full – text search engine generates the full – text index over a process known as Population. Population includes satisfying the index through words as well as their position in the Data Page. After a full – text index is formed, it is generated by default. Additionally, the SQL Server inevitably updates the full – text index as and when the information is altered in the linked tables or relations. But, the SQL Server do not retains a list of alterations done to the indexed information as soon as theCHANGE_TRACKING decision is set to OFF. This decision is indicated at the time of forming the full – text index by means of theCREATE FULLTEXT INDEX command.

Suppose, an individual doesn’t desire the full – text index to be generated at the time of formation by means of the CREATE FULLTEXT INDEX command, then the individual should stipulate NO POPULATION decision along with theCHANGE_TRACKING OFF decision. In the direction of generating the index, an individual will be required to run the ALTERFULLTEXT INDEX statement with START FULL or INCREMENTAL or UPDATE POPULATION section.

For an instance, to form a blank full – text index on the Management . LoanDetails table or relation, the individual can run the subsequent command:

CREATE FULLTEXT INDEX ON Management . LoanDetails ( Type ) KEY INDEX PK_LoanDetails_LoanID WITH CHANGE_TRACKING OFF, NO POPULATION

In the direction of generating the index an individual is required to run the subsequent command:

ALTER FULLTEXT INDEX ON Management . LoanDetails START FULL POPULATION

The above command will be generating the full – text index formed on the Management . LoanDetails table or relation.

Alike to normal SQL indexes, the full – text indexes can be update inevitably as and when the information is altered in the related tables or relations. This regeneration can be uncontrollable with respect to time as well as may undesirably disturb the practice of resources of the database server in the course of periods of great database action. Hence, it is healthier to schedule regeneration of the full – text indexes throughout the periods of minimum database action. An individual can stipulate the subsequent sorts of full – text index generation ways to regenerate the index:

· Full Generation

· Alteration Tracking – Centric Generation

· Incremental Timestamp – Centric Generation

Full Generation

An individual can make use of this technique the minute an individual want to generate the full – text index or the full – text catalog for the very first time. Afterwards, the individual can manage the indexes by means of alteration tracking or incremental timestamp. In the course of a full generation of a full – text catalog, index records are made for every rows or tuples in every single table or relation sheltered through the catalog. If a full generation is demanded for a table or relation, index records are made for every row or tuple in that table or relation.

Alteration Tracking – Centric Generation

The SQL Server sustains an entry of the rows or tuples which have been altered in a table or relation system for full – text indexing. These alterations are transmitted to the full – text index.

Incremental Timestamp – Centric Generation

The incremental generation technique updates the full – text index by the information which has been altered from the time when the index was last restored. In an incremental generation restore works only when, the indexed table or relation is having a column or attribute of the timestamp data type. If a table or relation is not having a column or attribute of the timestamp data type, then a full generation restore can be achieved only.

How To Search Information Through A Full – Text Search?

Once the full – text index has been formed on a table or relation, an individual can enquiry the table or relation by means of the full – text bases. The full – text bases are castoff to stipulate in what manner the search sequence must be explored in the table or relation. The subsequent bases can be castoff at the time of carrying out the full – text search:

· FREETEXT – Once the search conditions are specified, FREETEXT searches for several alternatives of word or a group of words assumed in the search column or attribute. FREETEXT is designed for the Prefix search.

Take the earlier situation of the education loan; an individual can make use of the FREETEXT base to get the anticipated result, as displayed in the subsequent command:

SELECT Type FROM Management . LoanDetails WHERE FREETEXT ( Type , ‘ Domestic Education Loan ’ )

· CONTAINS – This base is castoff in enquiries the minute an individual desire to search for an exact phrase or for the precise equality. It searches for the closeness of words inside a text also.

For an instance, an individual can make use of the subsequent command to search for the word ‘Loan’ adjacent to the word ‘Education’ in the Management . LoanDetails table or relation:

SELECT Type FROM Management . LoanDetails WHERE CONTAINS ( Type , ‘ Loan NEAR Education ’ )

This is the last part of the “Applying Index, View, As Well As Full – Text Search” series. Hope the readers have acquired some knowledge about the topic.

Thanks…