Hello, I was wondering if SQL Server is actually utilizing its fulltext index on searches that can be in the middle of text (such as LIKE %searchKeyword%)?
I forgot where I read it, but I did read somewhere that the fulltext index would only be utilized when it is used in a "StartsWith" manner (searchKeyword%) where you search for a LIKE with a fixed beginning and a variable ending.
Please let me know if this is true?
true, it works with keyword in middle of a text also. for example the link below uses full text search..
and the word men is found in middle of their contents. hope it helps.
Full-text indexes (if you have them, they aren't enabled by default) are accessed using functions like CONTAINS() and FREETEXT(), not the LIKE operator.
Normal indexes (e.g., "CREATE INDEX ix_tbl_fld ON mytable(textfield)") can be used by the LIKE operator, but not when the pattern begins with a % wildcard... these searches require a scan of all of the values in the table.
If you have a lot of rows with the same value repeated, using table compression under SQL Server 2008 may improve performance of LIKE '%keyword%' searches, even without an index (I don't have it, so I can't test this theory...).
If you read the MSDN documentation carefully, you'll see you can only search for fixed expressions like " hockey ", or for prefix-based expressions, like " hockey* " - but not for anything else, unfortunately.
See:
Supported Forms of Query Terms
Unfortunately, that's pretty much all there is. You cannot search for " hockey " :-(
Marc
The best way to do free-text search in SQL Server, that gives you not only a powerful search* but also returns your results ranked, is to use FreeTextTable.
eg:
SELECT *
FROM FreeTextTable(YourTableName,(Column1,Column2), 'search term')
INNER JOIN YourTableName as T ON T.ID = [Key] ORDER BY RANK DESC
This will give you ranked results in order of how near they matched (via the RANK column).