Requirements

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')