How do I force a refresh of a fulltext index within a transaction in mssql?

Question

I am trying to figure out how to get the following code to return the row that it just inserted - a co-worker pointed out and suggested running ALTER FULLTEXT CATALOG uiris_production REBUILD but that cannot be run within a user transaction.

The reason this has to be in a transaction is that this is coming from a test framework where the test is wrapped in a transaction and then rolled back.

declare @search varchar(64)
set @login_test = 'foobar'
set @search = '"' + @login_test + '*"'

begin transaction
   insert into users(login) values (@login_test)
   select login from users where contains(login, @search)
commit

Answer 1

First, make sure your full text index is set up for automatic change tracking. change_tracking_state should be 'A' for this query.

select t.name, fi.change_tracking_state 
    from sys.fulltext_indexes fi
        inner join sys.tables t
            on fi.object_id = t.object_id
    where t.name = 'users'

But, even with automatic change tracking, there is a certain degree of latency in updating fulltext. You may need to build a WAITFOR into your unit test to accommodate this.