Full Text Catalog and Full Text Search Basic Implementation In Sql Server 2014 Part 1 : Demo

Here I am sharing basic steps to create full text index and using full text search

Definition: Full-Text Search in SQL Server lets users and applications run full-text queries against character-based data in SQL Server tables. Before you can run full-text queries on a table, the database administrator must create a full-text index on the table. 

To simply: If you have huge data in string form like a book , story, volume, research paper stored in table in text column. Full text index on table will allow you to search data faster than normal indexes. This will also allow you to search similar words (synonyms) in text column.


Key Points:

• Full Text Index helps to perform complex queries against character data. 
• These queries can include word or phrase searching.
• We can create a full-text index on a table or indexed view in a database.
• Only one full-text index is allowed per table or indexed view.

Prerequisites:

• A table with at-least one unique index on any column
• Columns in table can have any of the following data types for storing text:  char, varchar, nchar, nvarchar, text, ntext, image, xml, or varbinary(max) and FILESTREAM

To Start with full text index

Step 1: Create Database + Table +Insert text data

/********************Create Database*******************/

CREATE DATABASE [FULL_TEXT]

GO

/********************Select Database******************/

USE [FULL_TEXT]

GO

/********************Create Table with  varchar columns *****************/

CREATE TABLE [dbo].[EMP](

[id] [int] NOT NULL,

[name] [nvarchar](50) NULL,

[lastname] [nvarchar](50) NULL,

[CITY] [nvarchar](50) NULL,

[age] [int] NULL,

 CONSTRAINT [PK_EMP] PRIMARY KEY CLUSTERED 

(

[id] ASC

)

) ON [PRIMARY]

GO

/****************Insert (1000000) 1 lakh rows*********************/

INSERT INTO EMP (ID,Name,LastName,CITY, age)

SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,

'Saurabh',

CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Adil'

ELSE 'Amit' END,

CASE

WHEN ROW_NUMBER() OVER (ORDER BY a.name)%1000 = 1 THEN 'New Delhi'

WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 1 THEN 'Kolkata'

WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 5 THEN 'Bangalore'

WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 3 THEN 'Chennai'

ELSE 'India' END,

ROW_NUMBER() OVER (ORDER BY a.name)%10 

FROM sys.all_objects a

CROSS JOIN sys.all_objects b

GO

/*************Create Non cluster index******************/

CREATE NONCLUSTERED INDEX [IX_EMP_City] ON [dbo].[EMP]

(

[City] ASC

) ON [PRIMARY]

GO 

/***************************************************/

Step 2.  Create a Full Text Catalog

"A full-text catalog is a virtual object that does not belong to any file group; it is a logical concept that refers to a group of full-text indexes."

So we have created Full Text Catalog now

Step 3. Create a Full Text Index

"Only one full-text index is allowed per table or indexed view, and each full-text index applies to a single table or indexed view. A full-text index can contain up to 1024 columns."

Right click on table--> Full-Text index--> Define Full-Text Index

Select unique index

Here we will define language in which we are storing our data. Few people might wonder that it supports Indian regional languages like  kannada , marathi and many more.

In SQL server 2012 / 2014 there is something new i found called as Statistical Semantics

"Statistical Semantic Search provides deep insight into unstructured documents stored in SQL Server databases by extracting and indexing statistically relevant key phrases."

In advance version we will select same and see its uses.

When you define automatic or manual change tracking, a full population of the index occurs. To avoid a population at the end of this wizard, select the Do Not Track Changes option, and clear the Start Full Population When Index Is Created check box.

Now we select full text catalog (Emp_FullText)

If you want you can schedule full text index population

I have skip the step as its just a demo.

Below is final step for wizard to create full text index.

Step 4. Full Population of Index \ Index Crawling

"Creating and maintaining a full-text index involves populating the index by using a process called a population (also known as a crawl)."

SQL Server 2014 supports the 3 types of population: 

1. Full population
2. Change tracking-based automatic or manual population 
3. Incremental timestamp-based population

If you found "start full population"just wait for 5-10 mins. Don't forget our table have 1 million rows

"Now My Full text index is ready to get used."

Once columns have been added to a full-text index, users and applications can run full-text queries on the text in the columns. These queries can search for any of the following:

• One or more specific words or phrases (simple term)
• A word or a phrase where the words begin with specified text (prefix term)
• Inflectional forms of a specific word (generation term)
• A word or phrase close to another word or phrase (proximity term)
• Synonymous forms of a specific word (thesaurus)
• Words or phrases using weighted values (weighted term)

I will write another post to learn, how you can use queries and function on full text data