full text indexing

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?

1. Your first query will not need to use your full text index, instead it will likely just use your PK_index. You can click the "Include Actual Execution Plan" to see how the query is performed. In a query that uses contains, you will see a Remote Scan task - this is because full-text indexing is not internal to MSSQL, but uses a windows service.

2. Yes populating your full-text indexes every now and again is recommended, because any changes tounderlying data columns are not immediately reflected in the full-textindex (due to the external structure for storing full-text indexes). So, to keep your full-text indexes up to date, you need to populate or a crawl them periodically.

Hope this helps,

Tones.

|||

TonyMilne:

2. Yes populating your full-text indexes every now and again is recommended, because any changes tounderlying data columns are not immediately reflected in the full-textindex (due to the external structure for storing full-text indexes). So, to keep your full-text indexes up to date, you need to populate or a crawl them periodically.

You can ask SQL server to track the changes automatically and update the indexes. Use below 2 queries to configure automatic indexing.

EXECsp_fulltext_table <table name>,'Start_change_tracking'EXECsp_fulltext_table <table name>,'Start_background_updateindex'

The action 'Start_change_tracking' asks SQL Server to start and incremental population of full text index.

The action 'Start_background_updateindex' asks SQL Server to propagate the changes as and when they occur.

So, each of your Insert, Update and Delete query on the table will automate prompt the incremental population and the indexes will be automatically updated.