|
|
Posted on 10/4/2015 9:03:04 PM
|
|
|
1. I read a few good posts,
SQL Server 2008 Full-Text Search Overview http://www.cnblogs.com/galaxyyao/archive/2009/02/13/1390139.html
Full-text index http://www.cnblogs.com/liulun/articles/1368655.html
First, copy a paragraph:
Q: Why Full-text search?
A: Because the query speed of text is slow (it is not recommended to build an index on the text, which is the reason)
Q: Is there any difference from 2005?
A； Of course, there is a difference, and the name is preceded by an integrated. Previously, 05 had a separate index file and engine, but in 08 it was integrated into the filegroup.
Q: What is the principle?
A: Based on keywords
Q: How does it relate to ordinary indexes?
A: The purpose is to improve query speed. Full-text indexes have many of the same features as indexes
Full-text search uses semantic search (this technique sounds very profound) and can also be used for binary documents.
Q: What is it for?
A: To give the simplest example, search for papers, fuzzy matching, fast and large quantities can also rank.
Two. Then go to MSDN to see some Dongdong:
Full-text index overview
http://msdn.microsoft.com/zh-cn/library/cc879306.aspx
Some of the following quotations are copied as follows:
1. In SQL Server 2008, the full-text engine is fully integrated into the database engine. The full-text engine is now in a SQL Server process (sqlservr.exe) instead of a separate process (msftesql.exe). By integrating the full-text engine into the database engine, full-text manageability is improved, hybrid queries are optimized, and overall performance is improved. From: http://msdn.microsoft.com/zh-cn/library/ms142587.aspx
2. The creation steps of full-text search, too lazy to take screenshots, see that others have done it, quote it directly, but from the second page it is different from my local, I can't directly right-click on the table to create a full-text search, the default is grayed, I can only create an index in the full-text index directory catalog in storage. http://www.sql-server-performance.com/2010/full-text-search-2008/
3. At first, I thought that the full-text search directory should be a directory on the corresponding hard disk, which is similar to the concept of locune. But in fact, as of SQL Server 2008, the full-text directory is a virtual object and does not belong to any filegroup. A full-text table of contents is a logical concept that represents a set of full-text indexes. Source:
http://msdn.microsoft.com/zh-cn/library/ms142497.aspx
4. You can only create a full-text index for a table or index view, but not a full-text index for a normal view.
A full-text index is a special type of marker-based functional index that is generated and maintained by the full-text engine. To create a full-text search on a table or view, the table or view must have a unique, non-nullable, single-column index. The full-text engine needs to use this unique index to map each row in the table to a unique compressible key. Full-text indexes can include the columns Char, Varchar, Nchar, Nvarchar, Text, Ntext, Image, XML, Varbinary, and Varbinary(max).
5. To create a full-text index on Table A, there is a premise, that is, there must be at least one unique index on Table A, that is, if there is no index on Table A, do not want to create a full-text index on Table A.
In 6.SQL Server 2008, the full-text index size is limited only by the available memory resources of the computer running the SQL Server instance. http://msdn.microsoft.com/zh-cn/library/cc879306.aspx
7. Query multiple columns (full-text search)
By specifying a list of columns to search, you can query multiple columns using the CONTAINS predicate. These columns must be from the same table.
Use parentheses, SELECT Name, Color FROM Production.Product WHERE CONTAINS((Name, Color), 'Red');
Or use the * sign, SELECT Name, Color FROM Production.Product WHERE CONTAINS(*, 'Red');
Source: http://msdn.microsoft.com/zh-cn/library/ms142488.aspx
9. Query multiple columns for multiple tables
It can only CONTAIN or CONTAINS, such as WHERE CONTAINS(t1. Name, 'Red') or CONTAINS(t2. Name, 'Red');
10. Optimized the performance of full-text queries
Use ALTER FULLTEXT CATALOG REORGANIZE to reorganize the full-text catalog.
ALTER FULLTEXT CATALOG customer REBUILD WITH ACCENT_SENSITIVITY=OFF
For more information, just look at this: http://msdn.microsoft.com/zh-cn/library/cc879244.aspx
11. Perform a prefix search http://msdn.microsoft.com/zh-cn/library/ms142492.aspx
You can use full-text search to search for words or phrases with a specified prefix.
When you perform a prefix search, all items in the column that contain text that starts with the specified prefix are returned. For example, to search for all rows that contain the prefix top (such as topple, topping, and top itself), the query would look like this:
SELECT * FROM Product WHERE CONTAINS (ProductName, '"auto*"' );
All text that matches the text specified before the asterisk (*) will be returned.
Note: If you do not add double quotation marks before and after the text and asterisk (e.g. CONTAINS (DEscrip{filter}tION, 'top*')), the full-text search will not use the asterisk as a wildcard.
When a prefix is a phrase, each token that makes up the phrase is treated as a separate prefix. All lines containing words that begin with these prefixes are returned. For example, the prefix "light bread*" will look for lines with the text "light breaded", "lightly breaded", or "light bread", but will not return "Lightly toasted bread".
|
Previous:A Cantonese song by Jacky Cheung, it feels pretty goodNext:A girl in Lianyungang, Jiangsu Province, was stripped of her shirt in the toilet and beaten francily
|
