q: full text indexing

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JIM.H. - 30 Sep 2007 20:15 GMT

full text indexing
Hi,
In SQL Server 2005, if I set full text indexing enables in column MyDesc and
1. use “Select * from MyTable where MyDesc LIKE ‘%abc%’” would this be using
full text indexing? Or have to use Contains to get it be in use?
2. Once I create the full-text index, should I be setting it to populate
periodically? Isn’t it populating itself?

Reply to this Message

Mike C# - 30 Sep 2007 22:47 GMT

1. You need to use the CONTAINS or FREETEXT predicates (or CONTAINSTABLE or
FREETEXTTABLE functions) to take advantage of full-text indexing. LIKE
'%abc%' performs a scan of the column (or possibly a table scan depending on
how you've indexed the table). Performance with LIKE '%abc%' will be
horrible.

2. For full-text indexes, CHANGE_TRACKING defaults to AUTO, so the FTI is
automatically updated as the data changes. Note that WRITETEXT and
UPDATETEXT data changes are not picked up. Some folks like to schedule a
catalog rebuild/optimization as well. Also if you did not go with the
default options when creating the FTI you might need to manually manage FTI
population.

> full text indexing
> Hi,
[quoted text clipped - 5 lines]
> 2. Once I create the full-text index, should I be setting it to populate
> periodically? Isn't it populating itself?

Reply to this Message