SQL Server 2012 Full-Text Search Configuration

 

 

Fulltext search feature in SQL Server 2012 is enabled by default. You must setup full text indexing on columns, if you need to use fulltext index on a table.

FullText Search Database configuration
DBA must perform basic steps to setup table columns within database for full-text search:

  1. DBA must create a fulltext catalog.
  2. You can create fulltext index by selecting each columns specifically you want to include in the fulltext.
  3. You must specify a table column if given column contains docs stored as binary data including images.
  4. You must dictate the language you want fulltext search to use.

Full text search allows multiple languages though the use of word breakers and stemmers, stop list.

 

FullText Catalog and Index Setup

  1. All fulltext index must fit to a full-text catalog. You may create a separate catalog for each index or you can associate one than one indexes with a catalog. Basically, fultext catalog is a virtual object and logical in nature.
  2. Create a full-text index on the table or indexed view.
  3. A full-text index is a distinct type of token-based practical index that is built and maintained by the Full-Text Engine. To generate full-text search on a table or view, it must have an exclusive, single-column, non-nullable index. The Full-Text Engine requires this unique index to map each row in the table to a unique, compressible key. A full-text index can include char, varchar, nchar, nvarchar, text, ntext, image, xml, varbinary, and varbinary(max) column.

 

FullText Indexes VS regular default SQL Server indexes

Full-text indexes

Regular SQL Server indexes

Only one full-text index allowed per table.

Several regular indexes allowed per table.

The addition of data to full-text indexes, called a population, can be requested through either a schedule or a specific request, or can occur automatically with the addition of new data.

Updated automatically when the data upon which they are based is inserted, updated, or deleted.

Grouped within the same database into one or more full-text catalogs.

Not grouped.

 

FullText Index Options

Choosing the Column Language

For information about things to consider when you are choosing the column language, see Choose a Language When Creating a Full-Text Index.

Choosing a Filegroup for a Full-Text Index

The procedure of building a full-text index is fairly I/O exhaustive (on a high level, it consists of reading data from SQL Server 2012, and then spreading the filtered data to the full-text index). As a best exercise, locate a full-text index in the database filegroup that is best for maximizing I/O performance or locate the full-text indexes in a different filegroup on another volume.

When ease of organization is important to you, we endorse that you store table data and any associated full-text catalogs in the same filegroup. Occasionally, for performance reasons, you may want to have the table data and the full-text index in diverse filegroups that are stored on different volumes to make best use of I/O parallelism. Assigning the Full-Text Index to a Full-Text Catalog.

It is significant to plan the assignment of full-text indexes for tables in full-text catalogs.

We endorse associating tables with the same update features (such as small number of variations versus large number of changes, or tables that alter often during a particular time of day) together under the same full-text catalog. By setting up full-text catalog population schedules, full-text indexes stay synchronous with the tables without unfavorably affecting the resource usage of the database server during periods of high database activity.

When you allocate a table to a full-text catalog, contemplate the following guidelines:

Always choose the smallest single index available for your full-text unique key. (A 4-byte, integer-based index is best.) This decreases the resources required by MS Search service in the file system suggestively. If the main key is large (over 100 bytes), consider selecting another unique index in the table (or creating another unique index) as the full-text sole key. Otherwise, if the full-text unique key size exceeds the maximum size allowed (900 bytes), full-text population will not be able to proceed.