Ever tried to do a “CONTAINS” query and ended up with an error message saying:
Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'Some Name Here' because it is not full-text indexed.
This means that SQL Server Express is looking for a full-text index perform the search but not finding them.
So, now you should create a FULLTEXT CATALOG and then create a FULLTEXT INDEX to associate with that catalog.
Simple query for creating a FULLTEXT CATALOG:(1)
USE "Database Instance Name"
CREATE FULLTEXT CATALOG "Any Name You Like"
WITH ACCENT_SENSITIVITY = OFF
Simple query for creating a FULLTEXT INDEX:(3)
CREATE FULLTEXT INDEX ON "Table Name"
(Subject LANGUAGE 1033) --"1033 is for English Word Breaker"
KEY INDEX SubjectId_UI
WITH STOPLIST = SYSTEM
Note: Here “SubjectId_UI” is a unique index (be careful, a unique index is not the same as a primary key). If you don't have a unique index, you are gonna have to create it before creating a FULLTEXT INDEX.
If you don't know how to create a unique index below is a simple query:(2)
create unique index "any unique index name" on "table name" ("column name")
If you run (3) (after running (2) if necessary) you may get an error saying:
Full-Text Search is not installed, or a full-text component can not be loaded.
This means that SQL Server Express is looking for the necessary components to create the index but not finding them.
So, here are the steps you should follow to add full-text search capability to your existing SQL Server Express 2008 without loosing the data you already have in it.
1- Download the SQL Server Express 2008 with Advanced Services from the below link:
2- Run the installer and go to “Maintenance” -> “Edition Upgrade”
3- Follow the steps until “selecting the instance” part and select the SQL Server Instance that you want to install the new components on.
4- Complete the remaining steps to complete the upgrade.
5- Installation will return to the first screen when the upgrade compeletes. If not, restart the installer.
6- Go to “Installation” -> “New SQL Server stand-alone installation or add features to an existing installation”
7- Follow the steps until “instance selection” part. Then select the instance that you want to add the full-text search feature to.
8- Select the features that you want to install (Full-Text Search)
9- Complete the following steps to complete the installation.
Now re-run (3), it should complete in less than a second successfully.
You may run this command on SSMS to check if the Full-Text Search has been installed:
(“0” means not installed and “1” means installed)
And you may run the command below to check if a FULL-TEXT INDEX has been created on your table column you specified in (3):
SELECT object_name(object_id),* from sys.fulltext_indexes;