title: CONTAINS predicate in SQL Server 2008 nor returning all the rows that it should
body: <h1>Background</h1>
  <p>In SQL Server 2008, I'm using a Full Text Index on a column named boatType on a table named boats.</p>
  <p>The boatType field contains a comma separated list of boatType to which a boat belongs. For example &quot;multihull,trimaran,cruiser&quot;.</p>
  <p>My SQL query that does the search uses the CONTAINS predicate like this:</p>
  <p>CONTAINS(boatType, '&quot;multihull&quot;')</p>
  <h1>The problem</h1>
  <p>Everything was working fine until recently I noticed a particular row was not getting returned, however, other rows that had exactly the same data were getting returned.</p>
  <h1>The short term solution</h1>
  <p>I tried rebuilding the index by removing Full Text Indexing from the boatType column, then re-adding it. I waited for the index to re-populate and that fixed the problem i.e. all expected rows were returned.</p>
  <h1>What's the long term solution?</h1>
  <p>OK, so problem fixed in the short term, but if this happens once it can happen again.</p>
  <p>I've assumed (perhaps wrongly) that the &quot;change tracking = automatic&quot; setting on the Full Text Index means that any updates to the data cause an automatic update of the index.</p>
  <p>I have not set any population schedules up as I've assumed that I don't need to if I'm using &quot;change tracking = automatic&quot;. Again, perhaps I'm wrong.</p>
  <p>In support of my assumptions above, new data that's added to the database or changed does seem to trigger an update of the index as most of the rows that should appear in searches do appear. It's just  minority.</p>
  <p>What's the best thing to do here?</p>