It can sound stupid, but make sure that :
- FullText Search functionality is installed
- SQL Server FullText Search(InstanceName) is started
Steps to add a fulltext index
Optionally you can decide if you want to enable the backward compatibility with catalogs created in previous versions
sp_fulltext_database 'enable' GO
You must create a unique Index on a column of the table against which you want to build fulltext index.
CREATE UNIQUE INDEX UI_INDEXNAME ON TABLENAME(COLUMNAMEID)
You must be sure a defualt catalog already exist, if it’s not the case you can create one.
CREATE FULLTEXT CATALOG CATALOGNAME AS DEFAULT GO
You can now build a fulltext index against the choosen table / column
CREATE FULLTEXT INDEX ON TABLENAME(COLUMNNAME) KEY INDEX UI_INDEXNAME GO
Now if you want to use the fulltext index, forget about the traditional like =’%…’. You must use the CONTAINS Keyword, like this :
SELECT * FROM TABLENAME WHERE CONTAINS(COLUMNNAME,'TEST')