SQL 2008 full text word break pointers

full-text-searchsql

If I do a full text search on SQL 2008, can I get a pointer ( File , or Database) so that I don't have to load the 100MB memo field to by Business Object and do search again ?

Best Solution

It does not appear that SQL Server 2008 supports the retrieval of offset pointers to the found keywords within the memo field.

Full text search does not search the memo fields, but searches an index that specifies which keywords are in which documents. The information about where these words appear within each document does not seem to be available in the full text search index.

Microsoft offers a type of query called sys.dm_fts_index_keywords_by_document. With it, you can enable the following use cases:

“I want to know how many keywords the full-text index contains”

“I want to know if a keyword is part of a given doc/row”

“I want to know how many times a keyword appears in the whole full-text index”  (sum(occurrence_Count) where keyword=……)

“I want to know how many times a keyword appears in a given doc/row”

“I want to know how many keywords a given doc/row contains”

“I want to retrieve all the keywords belonging to a given doc/row”

However, scenarios not covered in this release:

“I want to know the offset (word or byte) of a given keyword in a given doc/row”

“I want to know the distance (in words) between two keywords per a given doc/row”

Sources:

http://technet.microsoft.com/en-us/library/cc721269.aspx#_Toc202506233

http://msdn.microsoft.com/en-us/library/cc280607.aspx