Friday, February 24, 2012

Full-text search does not automatically update index when CHANGE_TRACKING AUTO

On Sql Server 2005 Standard if I insert a new row into SomeTable that has a full-text index:

insert SomeTable(keywords)values('this is a test')

and then query:

SELECT * FROM Message WHERE Contains(keywords, ' "test" ');

I get the expected rows all rows that have "test" in the keyword.

On Sql Server 2005 Express new rows are not returned unless I rebuild the catalog.

Is this a known limitation of Express?

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

CREATE TABLE [dbo].[SomeTable](
[key] [int] IDENTITY(1,1) NOT NULL,
[keywords] [nvarchar](255) NOT NULL
CONSTRAINT [PK_SomeTable] PRIMARY KEY CLUSTERED

CREATE FULLTEXT CATALOG ft AS DEFAULT;

CREATE FULLTEXT INDEX ON [dbo].[SomeTable] KEY INDEX [PK_SomeTable] ON [ft] WITH CHANGE_TRACKING AUTO
ALTER FULLTEXT INDEX ON [dbo].[SomeTable] ADD ([keywords])
ALTER FULLTEXT INDEX ON [dbo].[SomeTable] ENABLE

Are you rebuilding the catalog everytime to get the new rows?

No comments:

Post a Comment