Full-Text Search in SQL Server

The full-text search (FTS) is a feature of SQL Server that allows fast and efficient querying of large quantities of unstructured data. This feature was introduced with SQL Server 2000 and has been improved in later versions.

The studies are based on the definition of so-called full-text index through a process called population, which updates the indexes with the words and the locations where they are located in tables in a database.Gli indexes are stored in structures called catalog and can define multiple structures of this type for each database.

A full-text index is a special type of index whose structure is different from that of traditional indices of individual words and contains the text you want indexed.

During the process of populating the engine indexing (MSFTESQL) extracts the text content of the table row by row and uses the services of specific components, called iFilter to obtain a text stream from the columns to be indexed.

The iFilter used depend on the data type of the column where the data resides. For example, the columns of type CHAR, NCHAR, VARCHAR, TEXT and similar indexing engine applies the text IFilter.

To search the full text of a table requires three conditions:

• the table must have a column with unique values ​​and non-null (typically a primary key);
• must create a catalog to store full-text indexes for a given table;
• you need to create full-text indexes on text columns that you want to search.

And 'possible to set the indexes on columns of different types: CHAR, NCHAR, VARCHAR, TEXT, NTEXT, IMAGE, VARBINARY (MAX), XML.

As mentioned above, the full-text search with SQL Server was introduced in 2000 but later has been improved and enhanced features:

• introduction of an indexing service for faster more powerful full-text searches;
• ability to perform full-text queries on linked servers;
• ability to query full-text of arbitrary sets of columns (instead of a single column or all as they did in SQL Server 2000);
• ability to specify the language to be used for the insertion of the words in an index;
• Backup and restore of full-text catalog;
• Full-text indexing for XML data;
• Integration with SQL Profiler.

• <<
• 1
• 2
• 3
• 4
• 5
• >>