SQL Server full-text indexing

-------------------------

----Full-text indexing using a small sample------

Use SQL2000 and SQL2005 respectively

------------------------

Usage Scenarios: Complex query text data stored in data columns

--Environment

Use Poofly

Go

CREATE TABLE HELLO (ID INT not NULL PRIMARY Key,hello NVARCHAR (50))

----Sp_helpindex HELLO (PRIMARY KEY constraint is unique index name pk__hello__3214ec271de57479)

PS: Here's the only constraint we'd better build on an integer column of 4B size the general self-increment column is a good choice, which reduces the resource requirements of the file system's Mircosoft search service.

GO

INSERT HELLO SELECT 1, ' TEST '

1. To enable full-text indexing, you first need to install the full text search fulltext Indexing Service

2. Start Full Text Search service

3, check whether the database full-text index is turned on, do not open the function using Sp_fulltext_database

if (select Databaseproperty (' Poofly ', ' isfulltextenabled ')) =0

Execute sp_fulltext_database ' Enable '

--Here is a small example of creating a full-text index

4. Create a full-text catalog

EXEC sp_fulltext_catalog ' poofly ', ' CREATE '

5, Register Hello table, establish full-text index data element

EXEC sp_fulltext_table ' HELLO ', ' create ', ' poofly ', ' pk__hello__3214ec271de57479 '

6. Specify column names that support full-text indexing

EXEC sp_fulltext_column ' hello ', ' hello ', ' add '

7. Create (Activate) a full-text index for a table

--activate, which is the full-text retrieval capability of the active table, that is, registering the table in the full-text catalog

EXEC sp_fulltext_table ' HELLO ', ' activate '

8. Start a full population of full-text catalogs

--Late is maintenance index with change tracking or incremental population

--exec sp_fulltext_catalog ' poofly ', ' strat_incremental '

EXEC sp_fulltext_catalog ' poofly ', ' start_full '

9. Verifying the progress of full-text catalog fills

Select FullTextCatalogProperty (' Poofly ', ' populatestatus ')

--to indicate that the fill has been completed

10. Full-text indexing via contains predicate

Select HELLO

From HELLO

where CONTAINS (*, ' TEST ')

/*

HELLO

---------------

test*/

11. Delete full-text index metadata

EXEC sp_fulltext_table ' HELLO ', ' drop '

12. Delete full-text catalogs

EXEC sp_fulltext_catalog ' poofly ', ' DROP '

--------------Ornate Split Line-----------------------

--Here's how to build a full-text index

/* Create a full-text catalog */

Create fulltext CATALOG Poofly as DEFAULT

/*c creating a full-text index */

Create FULLTEXT Index on HELLO (hello) key index pk__hello__3214ec271de57479 on Poofly

/* Full-text indexing via the CONTAINS predicate */

Select HELLO

From HELLO

where CONTAINS (*, ' TEST ')

/* Delete Full-text index */

DROP Fulltext INDEX on HELLO

/* Delete full-text Catalogs ft (cannot be deleted when full-text index is included) */

Drop FULLTEXT Catalog Poofly

-----Use the CONTAINS keyword for full-text indexing----------------

--1. Prefix Search

Select name from TB where contains (name, ' "china*" ')

PS: note here the * return result may be Chinax Chinay chinaname China

/* Return prefix is the name--of China if not "" then the system will be contains (name, ' china* ') matches the china* * *

--2. Searching using derived words

Select name from TB where contains (name, ' Formsof (inflectional, "foot") ')

/* Out results may be foot feet (all verbs in different form nouns plural form) */

--3. Word Weighted Search

Select value from TB where contains (value, ' ISABOUT (performance weight (. 8)) ')

/* Full value with a number of-1 indicates how important each word is.

--4. Near word Search

SELECT * from TB where contains (document, ' a near B ')

/* The result is that the "a" word and the "B" word near the document can be written as contains (document, ' a ~ b ') */--

--5. Boolean Logic Search

SELECT * from TB where contains (name, ' "A" and "B" ')

/* Returns a line that contains both A and B words of course here and the keyword is changed to or, and not, etc. */

----------------------------------------------------

You can also use Rreetext for fuzzy search

--Full text index of any input automatically identifies important words and constructs a query

SELECT * from TB where FREETEXT (Wendang, ' Zhubajie Chi Xi gua! ')

--============================================================

--many factors affecting the performance of full-text indexing include hardware resources and the performance of SQL itself and the efficiency of MSFTESQL services.

--Its search performance has aspects: full-Text indexing performance and full-text query performance

------------------------------------------------------------------------------------------------

Normal SQL Index	Full-Text Indexing
Storage is controlled by the database in which they are defined.	stored in the file system, but managed through the database.
Each table allows a number of normal indexes.	Only one full-text index is allowed per table.
They are automatically updated when inserting, updating, or deleting data that is their basis.	Adding data to a full-text index is called a population, and a full-text index can be requested by a schedule or a specific request, or it can occur automatically when new data is added.
Not grouped.	Grouped into one or more full-text catalogs within the same database.
Created and dropped using SQL Server Enterprise Manager, wizards, or Transact-SQL statements.	Use SQL Server Enterprise Manager, wizards, or stored procedures to create, manage

SQL Server full-text indexing