-------------------------
----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