The difference between full-text catalogs and full-text indexes on SQL Server

A long time did not write an essay, originally wanted to write a full-text index of SQL Server essay, unfortunately there is no time, has been dragged to the present time to write, sorry to keep you waiting ~

First introduce the storage class object in SQL Server, haha, first introduce the concept, let novice veteran have a cognition

SQL Server Management Studio incorporates full-text catalogs, partition functions, and partition scheme nodes into its storage node in Object Explorer.

As shown in the following:

The text is coming! Haha, please lead: full-text catalog

Full-Text Catalogs

Database Storage | The full-text catalog node is the node that is used to save and manage full-text indexing. A full-text catalog is typically made up of full-text indexes of 0 or more data tables in the same database.

It is important to note that only one full-text index can be created for each data table. Therefore, once a full-text index is created on a data table, the data table will be subordinate to only one full-text catalog. Other words

A database can contain one or more full-text catalogs, and a full-text catalog can contain one or more full-text indexes, but one data table can have only one database full-text catalog and full-text index (as with MySQL).

The main steps for using full-text indexing in SQLSERVER2005 are as follows :

(1) First, you need to create a full-text catalog.

(2) Then populate the full-text catalog with data from the data tables that need to create the full-text index. This process is also known as populating the full-text index.

(3) After completing these two steps, you can begin using the full-text indexing feature.

The method of creating a full-text catalog is straightforward. The ability to create full-text catalogs is realized through SSMS and T-SQL.

As for the specific steps to create, here will not say, there are articles on the Internet, I will not reprint

Finally, the advantages and disadvantages of the full-text index and considerations:

(1) Full-text indexing can be retrieved for char, varchar, nchar, nvarchar, text, ntext, image, XML, varbinary, or varbinary (max) type fields.

is a good way to solve the fuzzy query of massive data.

(2) A table can only be built with one full-text index (but can be multiple fields).

(3) Comparing with like MSDN explanation

Unlike full-text search, the like Transact-SQL predicate is valid only for character patterns (char, varchar, nchar, nvarchar). In addition, you cannot use the LIKE predicate to query formatted binary data. In addition, performing a like query on a large amount of unstructured text data is much slower than executing the same full-text query on the same data. A like query over millions of lines of text data can take several minutes to return the results, and for the same data, the full-text query takes only a few seconds or less, depending on the number of rows returned and their size. Another consideration is like just performing a simple pattern scan of the entire table. Instead, full-text queries recognize the language, which applies specific transformations at index and query time, such as filtering non-indexed words and making thesaurus and warp extensions. These transformations can help full-text queries improve their recall and the final ranking of results

Finally, let's talk about the use of full-text indexing :

The full-text search statements provided by SQL Server 2000 and SQLSERVER2005 are mainly contains and FREETEXT. (1) The function of the contains statement is to search in all columns of a table or in a specified column: a word or phrase; the prefix of a word or phrase; another word similar to one word; A word that is derived; a word that repeats itself.

(2) The function of the FREETEXT statement is to search for a free-text formatted string in all columns or columns in a table, and to return rows of data that match that string. Therefore, the function that the FREETEXT statement executes is also called the free-text query.

T-SQL statements

UseCONTAINS (d,' cardinality line ')   

Pratice is one of my test databases, D is one of the fields in the test table, and the above T-SQL statement means finding records in the D field that contain the string "cardinality row"

--The following T-SQL instruction code shows the full-text check index status and its contents. SELECT * from Sys.dm_fts_index_populationselect * from Sys.dm_fts_index_keywords (db_id (' tde '), object_id (' Saleslt.product '))

Http://blogs.technet.com/b/technet_taiwan/archive/2015/06/02/sql-database-new-features-tde-and-full-text-search.aspx

There are 3 ways to fill: 1, Full fill, 2, incremental fill, 3, automatic tracking changes

F

F

F

F

F

sql2008 Full-Text indexing is now stored in the database full-text index based char/varchar/nvarchar/xml/varbinary provides 50 filter full-text indexes using language-specific word breakers breaker and stemmers Stemmer Specifies the specific language in which the breaker between the words is excluded from the word (word) called the noise word stop word, by specifying the noise word to avoid a large number of words (words) that are not keywords at all. A table/indexed view can have only one full-text index stemmer n. Stem, A terrier; a sucker.

Be sure to use a separate filegroup to hold the full-text catalog!

F

F

F

F

F

The full-text index population is not completed immediately, because the data must be submitted to the index engine, which is then applied by the indexing engine, the word breakers, stemmers, language files, the noise word list stop lists, and finally merge the changes into the index merge to the index

The language specification determines which word breakers and stemmers need to be loaded by the full-text indexing engine

SQL2008 has third-party word breakers and stemmers

The word breaker also recognizes the distance between words in the data, and this proximity (proximity) is added to the full-text data, and MySQL's full text is also approximate, which is a unique feature of full-text indexing, and like%% is not available

SQL Server uses Stemmers to identify various forms of keyword changes

F

F

F

F

F

F

F

F

Full-text predicates (full-Text search keywords) are submitted to the full-text indexing engine, and the full-text indexing engine uses word breaker to search for keywords, add tokens, and return data to the optimizer

The predicate after the where, if not the full text, is called the predicate, if the full text is the full- text predicate

The language parameter specifies Yes, which national language

The optimizer cannot calculate the separate statistics on a full-text index with parameter sniffing , and all keywords that must be given to full-text search are Unicode types for best performance

Synonym file

F

F

F

F

F

F

F

F

F

F

Create synonym XML file add noise word list rebuild full-text index noise word list stop list or noise word file If you submit a parameter in a full-text predicate that is a noise word , the query does not return any results (and he does not need to access the underlying data at all) Sql2005 and earlier versions of the noise Word file are configured under the Ftdata folder, and the list of noise words for sql2008 is stored in a database in SQL Server

Incremental population Full-text indexes must have a timestamp

Synonym and noise word storage path C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTDATA

There are only two files under the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTDATA folder: Synonyms and interference words stored

Noise word list stop list or noise word file or stop word

Full-text key terminology

F

F

F

F

The difference between full-text catalogs and full-text indexes on SQL Server