How to implement full text indexing.

Problem: How to implement full text indexing.

Solution: CREATE FULLTEXT INDEX

In order to create a full text index on a table or an indexed view, first we have to create a full text catalog on the database.

Only Character datatypes (char, varchar) can be indexed, also only one full text index can be built in a table or indexed view. Full text indexing is required for Full-text search functionality in SQL Server that supports full-text queries against character-based data.

Also for a full-text index there must have a unique index which acts as a key index and generally preferred integer values and primary keys for performance reasons.

Example:

In this article I am going to show you how we can full text index a column.

Example 1:

Let’s create an address table and index the column using full text.

In order to create a full text, the table has to have a unique index and also full text catalog exist before we create a full text index.

CREATE UNIQUE INDEX ui_uAddress ON dbo.Address(AddressPkey); 

CREATE FULLTEXT CATALOG fta AS DEFAULT; 

CREATE FULLTEXT INDEX ON dbo.Address(AddressLine1)  

   KEY INDEX ui_uAddress  

   WITH STOPLIST = SYSTEM; 

To view the list of full text catalog, full text indexes you can run by the database using the following query:

SELECT t.name AS TableName, c.name AS FTCatalogName ,i.unique_index_id

FROM sys.tables t

JOIN sys.fulltext_indexes i 

  ON t.object_id = i.object_id 

JOIN sys.fulltext_catalogs c 

  ON i.fulltext_catalog_id = c.fulltext_catalog_id