Full Text Index with Database Project Problem

I was running into issues with scripting full text indexes to my database project when doing a database compare from a SQL Server 2008 R2 database to my Visual Studio 2010. A new index was being created in my project each time I compared. I had the full text indexes working in the database so was unsure why visual studio kept trying to create new indexes in my database project and when I reversed the comparison it tried to drop my existing full text indexes.

This issue was report to Microsoft Connect but they were unable to reproduce and marked the issue as closed.

I traced the issue down to the “key index” value being missing from the full text index which was generated during the database -> database project comparison.

The issue is caused because the index which the full text index was keyed off of was really an extension of my primary key and was automatically named during the primary key creation. I was using the index shown below.

The solution was to create a new index and name it myself.  I had to delete my existing full text index and create a new full text index and reference my newly created index.  I just deleted my entire catalog and started over since but you may be able to just remove a single index instead of starting over completely.  After creating the new index and new full text index to reference it the Visual Studio database comparison correctly brought in the name of the index and subsequent database comparisons worked as expected.

The Microsoft Connect article also lists my workaround now.

MS SQL Server Management Studio Intellisense Refresh

In MS SQL Server 2008 you will occassionally run into a problem where it appears you have an error in your query because intellisense doesn’t recognize changes that recently occured in your database. You need to refresh the intellisense in order for the recent changes to not appear as errors in your queries. To do this, you can go to Edit -> Intellisense -> Refresh Local Cache or you can type ctrl+shift+R.