Home / server / Questions / 30416
Accepted
Michael Pryor
Asked: 2009-06-24 08:17:05 +0800 CST

When does SQL Server consult it's full text catalog?

  • 772

Does SQL Server only look at its full text catalog when there is a CONTAINS in the query? Or would it also do that for a LIKE query?

Background: Our application used to use Full Text Catalogs in SQL Server and CONTAINS queries to do Full Text Searching. Last year we switched to Lucene and do all of our own indexing now. So we no longer rely on the Full Text Catalog (in fact we remove it).

A customer claims they turned on the Full Text Indexing in SQL Server and now our app is returning hits for queries that it wasn't before. There are absolutely ZERO sql queries run against our db with the CONTAINS clause, so I just wanted to verify that this is the only SQL clause that cause SQL Server to look at a Full Text Catalog.

sql-server

2 Answers

  1. Best Answer

    Full text search will hit the catalogs for full text search queries only (CONTAINS, CONTAINSTABLE, FREETEXTTABLE, FREETEXT). LIKE will never consult a full-text index.

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

    Have you tested the reported behaviour yourselves?

    • 3

  2. Incase interested, Just check out this video tutorial which explains in depth about FULLTEXT Search and difference between LIKE predicate and predicates which hit FT Catalog like FREETEXT and CONTAINS.

    Video - Intro to Full Text Search URL - http://wtv.watchtechvideos.com/topic231.html

    • 0