SQL Server Full-Text search configuration, query first experience

one, using SQL Server full-text search configuration

To use the full-text search service for SQL Server, you need to configure the following.

1. Open full-Text search service:

　　

2. Open the full-text indexing function of the database:

　　-- Open the full-text search function of the database 'enable';   

3. Create a full-text index catalog:

　　-- creating a full-text index catalog create  fulltext catalog Indexcatalog     [PRIMARY'E:\SQLServerIndex  DEFAULT       

4. Create a unique index:

　　-- Create a unique index ASC) 

5. Create a full-text index:

  -- Create full-text index create fulltext index on  article (articlecontent) key index indexcontent on indexcatalog -- Indexcontent is the unique index name            

second, use full-text search

　　Contains Search method

1. Simple word Search-search for one or more specific words or phrases. Words can include one or more characters, with no spaces or punctuation in the middle. Phrases can consist of multiple words separated by spaces, but there can be punctuation or no punctuation between words:

　　-- search for article content containing Zhang San or John Doe Records CONTAINS (articlecontent,' Zhang San 'or ' John Doe ')    

2. Prefix Word Search Method-search for the specified text at the beginning of the word or phrase, prefix word search method is mainly used in search English, such as search for "do" word, you can doctor, document, download and other words are searched out.

　　--The search article contains a record of the word ' CONTAINS ' (articlecontent, ' recognition * ')-- think, recognize    

3. Weighted Word Search Method-to add weights to multiple search conditions, the higher the weighted value of the record in the front, when the search for the record with multiple strings as a condition, you can add a weighted value for different strings, which is a value between 0 and 1, the higher the weighted value of the record in the front row. In fact, in the returned result set of the SELECT statement, it is not sorted by the size of the weighted value, because weight does not affect the results of the contains query, but only affects the ordering in the CONTAINSTABLE query:

　　-- in CONTAINS search, the weights do not affect the sort from  article CONTAINS (articlecontent,'ISABOUT ("Beijing" weight (0.9), " Shanghai "Weight (0.6)," Guangzhou "weight (0.5)       )

4. How to search near words-you can search for words or phrases that are adjacent to another word or phrase:

　　-- search for the second word near the first word in the content of the article CONTAINS (articlecontent,'"Zhang San" close "John Doe")    

FreeText Search Method

FreeText Search method Compared with contains search method, its search results are very inaccurate, because the FREETEXT search method is to separate each word in a sentence to search . Using contains search to search for records with the "Ministry of Education", the search will be a record containing three strings of "Moe" in the record.

　　-- Search the content of the article contains ' process ' word and ' social ' word of record FREETEXT (articlecontent,' Cheng Club     

　　ContainsTable Search Method

1, containstable Basic Search:

　　--containstable Search article content contains a record of friendship ContainsTable (article,articlecontent,' friendship as  table1  

Notice that the return middle finger contains two fields:

　　

is actually the data in the ContainsTable table.

2. CONTAINSTABLE return the required columns:

 　　 --containstable Search returns the required column SELECT * from article JOIN containstable (article,articlecontent,< c7> '"Environment"',2) as table1 on article.articlecontent = table1.  [KEY] ORDER by table1. RANK DESC                

　　

3, Constainstable according to the criteria of sorting

--Sort    JOIN containstable according    to criteria (article,articlecontent,",as table1 = Table1. [KEYDESC         　　

See that the query results are actually sorted by qualifying:

　　

4, constainstable by the right value ranking

 　　 -- weight value sort select * from article join  Containstable (Article,articlecontent,  isabout ("Thailand" weight (0.9), "High speed rail" weight (0.6), "Environment" weight (0.1))  ") as TABLE1 on article.articlecontent = TABLE1. [key order by TABLE1. RANK desc           

The results are sorted by weight value:

　　

using freetexttable search

Single word search, but more arguments are available: The syntax is as follows:

Freetexttable (*'freetext_string' []     []) 

Example:

　　

　　Search Image Field

In the field of the image type of SQL Server 2005, not only can you store graphics files, but you can also store many file types such as executables, music files, text files, and so on. SQL Server 2005 supports full-text search for the contents of some files stored in fields of the image type, but you must indicate the type of files stored in the Image field when you create the full-text index.

SQL Server 2005 supports querying the contents of plain text files, Web pages, Word files, Excel files, and PowerPoint files stored in an image, with the extension name fields of txt, htm, doc, xls, and PPT, respectively. When the full-text index is created, the query for the contents of the file in the Image field is the same as the query method for the other fields:

FromCONTAINS (file,' database ')   

Third, summary and evaluation

The following is a common SQL statement for this SQL Server full-text search:

--Open the full-text search function of the database:exec sp_fulltext_database enable --database enables full-text indexing --the full-text search feature of the database: exec Sp_fulltext_ Database  ' disable; --database disables full-text indexing --If you forget full-text indexed catalogs, tables, columns created full-text index exec sp_help_fulltext_catalogs --full-text index catalog exec sp_help_ Fulltext_columns --full-text indexed column exec sp_help_fulltext_tables --full-text index table             

Feel this thing a little bit of chicken, the biggest limit is that because the full-text index can only be built on a unique index column, resulting in the creation of a full-text index column can not exceed the length of 900 bytes, this limit it is basically not considered, because even an article can not be indexed, only for some very small string.

SQL Server Full-Text search configuration, query first experience