Add full-text feature in SQL Server and Setup it for any column

Add full-text feature in SQL Server and Setup it for any column

---

If your like queries are taking so much time to execute you can apply full-text search in your queries :-

Firstly How To Add This Feature In Sql Server?

You can add full text to an existing instance by changing the SQL Server program in Programs and Features:

• Open the Programs and Features control panel.
• Select Microsoft SQL Server 2012 and click Change.
• When prompted to Add/Repair/Remove, select Add.
• Advance through the wizard until the Feature Selection screen. Then select Full-Text Search. enter image description here
  
  
• On the Installation Type screen, select the appropriate SQL Server instance.
• Advance through the rest of the wizard.

---

Then How To Setup It For Any Column Of Table In Sql Server?

To implement SQL Full Text Search programming, we have five step processes as below:
a. Data Setup
Let us take a classic example of Employee table for our illustration. As the first step, Employee table is created with the name 'EmpName' of below schema:

Our objective is to search the employee records with the free flow search of any field i.e. the user can extract the record which matches any column in the database table. As an example, the user can enter a name ('Raj') to search from EmpName table. The result may be from FirstName, LastName, ManagerFirstName, ManagerLastName. If the user enters 'Paul Raj', the full text search fetches either Paul or Raj from any of the name columns in EmpName table. Let us load the sample data of 5 below records into EmpName table as:

b. Full Text Column Setup
To initiate the full text search process, let us identify full text column, which is the consolidated data of each textcolumns that you want to include in the full-text. In our example, let us create a new column FullTextValue in EmpName table as below:

To fill the data in newly added FullTextValue column, let us execute the below query
Collapse | Copy Code

UPDATE EmpTable
SET FullTextValue = [EmpID] +
+ ' ' + [FirstName]
+ ' ' + [LastName]
+ ' ' + [ManagerFirstName]
+ ' ' + [ManagerLastName]
+ ' ' + [DivisionName]
+ ' ' + [Location]
+ ' ' + [Country]

Now, the data elements of EmpName table looks like:

c. Full Text Catalog Setup
A full-text catalog provides a mechanism for organizing full-text indexes. Each catalog can contain zero or more indexes, but each index can be associated with only one catalog A full-text catalog is a logical concept that refers to a group of full-text indexes. The catalog is not associated with a file group.
As the thumb rule, if a given column contains documents stored as binary data, we must specify a table column that identifies the type of each document in the column being indexed.
New full text catalog is created in SQL Server by expanding 'Storage' section of the selected DB instance, right click menu of 'Full Text Catalog' option as below:

The catalog system uses SQL Server full-text catalogs to store and search catalog content. In our example, a new fulltext catalog namely 'FT_Employee' has been created in the full text catalog wizard as below:

Our new catalog is created successfully by running the above wizard. We can proceed with full text index definition.



d. Full Text Index Setup
To match the newly created catalog with the proper DB table, let us run the process of defining the full text index on the table. In our example, let us go EmpTable where we want to achieve the full text search algorithm. On right click of the table in Database explorer, we get 'Full-Text index' menu, sub menu with 'Define Full-Text index' as below:

On clicking Define Full-Text index option, it launches full text indexing wizard as:

As the first step of the process, the unique index of the given table 'EmpTable' is selected from the populated drop down box. In our example, it is EmpID table with the primary key definition of PK_EmpTable. So, unique index of the table is selected as:

Next, we need to select the table columns in terms of full text queries. Developer can select multiple table columns and so the user interface is with check boxes. In our example, we have the consolidated column 'FullTextValue' as the eligible table columns for full text query and so the column is selected as:

Another important step is to select an existing full text catalog. If not exists, SQL provides the facility to create a new catalog as marked in a check box. In our example, we created our own full text catalog namely 'FT_Employee' in the previous section. So, let us select the created full text catalog FT_Employee as:

A stopword can be a word with meaning in a specific language, or it can be a token that does not have linguistic meaning. For example, in the English language, words such as "a," "and," "is," and "the" are left out of the full-textindex since they are known to be useless to a search. In our example, we disable the full text stop list by selecting off option from the above highlighted drop down box.

Now, we successfully created full text index on EmpTable with 0 error and 0 warning. If we encountered with any issue, it might be reported in the Report expand button with the details.



e. Full Text Query
It is all set now. As the last step, we can fetch the result of the full text query as:
Collapse | Copy Code

SELECT tbl.*
FROM EmpTable tbl
JOIN containstable
(EmpTable, FullTextValue, '"3"') as key_tbl
ON tbl.EmpID = key_tbl.[Key]
ORDER BY key_tbl.[Rank] asc

In this example, we are trying to fetch the records with the contained data '3' in any of the column of EmpTable. It returns the third row of EmpTable because it contains 3 in all the columns EmpID, FirstName, LastName, ManagerFirstName, ManagerLastName, DivisionName, Location and Country. SQL returns this record even if any one of the above column contains 3.
Thus, we can achieve the full text search mechanism in SQL Server.