Full Text Search

-- Refer to http://blog.sqlauthority.com/2008/09/05/sql-server-creating-full-text-catalog-and-index/
/*
the following command is to change the UI method to codes.
*/
use AdventureWorks2008R2;
go

create fulltext catalog FTCatalog
/*
create fulltext catalog cataname on filegroupname
*/

create fulltext index on HumanResources.Employee
(Gender, JobTitle,LoginID,MaritalStatus,NationalIDNumber)
key index PK_Employee_BusinessEntityID
on FTCatalog
WITH Change_Tracking AUTO

SELECT BusinessEntityID, JobTitle
FROM HumanResources.Employee
WHERE FREETEXT(*, 'Marketing Assistant');

SELECT BusinessEntityID,JobTitle
FROM HumanResources.Employee
WHERE CONTAINS(JobTitle, 'Marketing OR Assistant');

SELECT BusinessEntityID,JobTitle
FROM HumanResources.Employee
WHERE CONTAINS(JobTitle, 'Marketing AND Assistant');

 Gender, JobTitle,LoginID,MaritalStatus,NationalIDNumber list all the column names which has been enabled fulltext search.

with varbinary(max) column, use as following

create fulltext index on HumanResources.Employee
(Gender, JobTitle,LoginID,MaritalStatus,NationalIDNumber,
columnName1 type column columnName2)
key index PK_Employee_BusinessEntityID
on FTCatalog
WITH Change_Tracking AUTO

 columnName1 is the colunmn contains the varbinary(max) data, columnName2 is the column contains the file extention.