SQL Server full-text catalog Full-text indexing using methods and differences explain _mssql

To introduce the storage class object in SQL Server first, haha, first introduce the concept, let novice veteran have a cognitive
SQL Server Management Studio includes the Full-text catalog, partition functions, and partition schemes nodes in the storage node of its Object Explorer, as shown in the following illustration:

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 composed of Full-text indexes of 0 or more data tables in the same database.
Note that you can only create one Full-text index for each datasheet. Therefore, once a Full-text index is created on a datasheet, the data table will only be subordinate to a Full-text catalog. Other words

A database can contain one or more Full-text catalogs, one Full-text catalog can contain one or more full-text indexes, but one data table can only be subordinate to a single database Full-text catalog and a Full-text index.

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 the data in the data table where you want to create the Full-text index. This process is also known as populating a Full-text index.
(3) After you have completed both of these steps, you can begin to use the Full-text indexing feature.

The way to create a Full-text catalog is simple. The ability to create Full-text catalogs can be achieved through SSMS and T-SQL.

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

(1) Full-text indexing can be used to retrieve char, varchar, nchar, nvarchar, text, ntext, image, XML, varbinary, or varbinary (max) type fields, which is a good way to solve the fuzzy query of mass data.
(2) A table can only establish one Full-text index (but can be for multiple fields).
(3) Comparison with like MSDN explanation

Unlike Full-text Search, the like Transact-SQL predicate is only valid for character patterns (char, varchar, nchar, nvarchar). In addition, you cannot use the LIKE predicate to query formatted binary data. In addition, performing like queries on a large number of unstructured text data is much slower than performing the same full-text query on the same data. A like query for millions of of lines of text data may take several minutes to return 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 mode scan of the entire table. Instead, the Full-text query recognizes the language, and it applies specific transformations when indexing and querying, such as filtering for non-indexed words and making thesaurus and warp extensions. These transformations can help full-text queries improve their recall and final ranking of results

Finally, 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 a contains statement is to search in all columns or columns of a table: a word or phrase, a prefix to a word or phrase, another word similar to a word, a derivative of a word, and a word that appears repeatedly.
(2) The function of the FREETEXT statement is to search for a free text format string in all columns or columns of a table and return the data rows that match the string. Therefore, the function that the FREETEXT statement performs is also called the free-text query.

T-SQL statements

Copy Code code as follows:

Use Pratice
SELECT * from Dbo.test WHERE CONTAINS (d, ' cardinality row ')

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"