A Trigger

Question

Post reply

A Trigger

nazaninahmady_sh

Default port

Points: 1465
More actions
June 11, 2010 at 11:54 pm

#222019

Hi
i have a full text index on a table ,
i want to write a trigger on this table ,
i want after every insert a record, this full text index become run, would you please help me?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

platipino SSC Enthusiast Points: 152 More actions · Answer 1

Indexes are never triggered.

You might provide more information to get results.

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 2

Do you have a timestamp column on the indexed table? My experience, and I'm pretty sure i found an article about it in BOL, is that this is required for automatic incremental updates.

My tables have a timestamp columns, and I never trigger full text index updates during normal operation since. The full text indexer picks up any changes to the table and updates the full text index immediately.

I do a weekly full rebuild of the index.

I have CHANGE_TRACKING set to AUTO.

Paul White SSC Guru Points: 150467 More actions · Answer 3

With change tracking set to auto, SQL Server takes care of maintaining the full-text index for you. There is no need (or point) in writing a user trigger, and a timestamp (rowversion) column is not required.

Any query that modifies a table that is full-text indexed with change tracking set to auto has its query plan modified to include operators that update internal information about changed data. A background process uses that internal data to maintain the full-text index asynchronously.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 4

Paul, my mistake. I found the document in BOL I mentioned (), and the timestamp is required if you want to manually start an incremental population. It doesn't say that it is required when CHANGE_TRACKING is set to AUTO, but for some reason I can remember that the full text index update was much faster with a timestamp column. My guess/conclusion at that time was that both the automatic population and the manual population would benefit from the timestamp column.

I use SQL Server 2005 by the way. I know that FTS has changed a lot between 2005 and 2008.

Paul White SSC Guru Points: 150467 More actions · Answer 5

Nils Gustav Stråbø (6/13/2010)
Paul, my mistake. I found the document in BOL I mentioned (), and the timestamp is required if you want to manually start an incremental population. It doesn't say that it is required when CHANGE_TRACKING is set to AUTO, but for some reason I can remember that the full text index update was much faster with a timestamp column. My guess/conclusion at that time was that both the automatic population and the manual population would benefit from the timestamp column.

You're right that a rowversion/timestamp column is required for incremental population (which requires change tracking to be set manual or off). I'm really pretty sure that change tracking uses a mechanism that is unaffected by the presence of a timestamp/rowversion column, but I'm prepared to be corrected on that 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 6

🙂 You will not be corrected.... simply because you are correct. I just tested it on our development server, and there is no performance penalty if the table does not have a timestamp when CHANGE_TRACKING=AUTO.

Edit: And you are also correct about the extra steps in the execution plan. I wasn't aware of that. Nothing is better than starting the day by learning something new. Thanks Paul 🙂

Paul White SSC Guru Points: 150467 More actions · Answer 7

Nils Gustav Stråbø (6/14/2010)
...I just tested it on our development server, and there is no performance penalty if the table does not have a timestamp when CHANGE_TRACKING=AUTO...

Thanks for taking the time to test that out, Nils 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi