Create Full Text index on 2008 R2
Problem:
One of my db user sent me request. Please run this query against cms database. I was running from my web app and I got timeout error.
select Top 10 * from cmsContent
where ContentXML like '%Sephora iPad App Personalizes Skin Care%'
Consult:
You will end up getting nothing from the query because that column data type is in text format. I can immediately doubt this because on the name of the column and the condition he is using. So, what do I have to do?
Solution:
- Enable Full Text
- Create Full Text Catalog
- Create Full Text index
- Wait to populate
I need a Full text index on that column. How can I do that on SQL 2008 R2? Full text is disabled by default on the database label.
Enable Full Text
Let us enable it. Right click on the database / Facets / IsFullTextEnabled = True
Create Full Text Catalog
At the database label go to Storage / Full Text Catalogs / Name the Catalog / OK
Go to property of the catalog that you just have created. Select Tables/Views รจSelect a table or tables you want to create full text index in and move them to the right. From the right select the table and select the column from the bottom pane / OK.
You can go to table and create full text index from there as well and wait until populate. Once populate you can query that table like this.
select * from cmsContent
where CONTAINS(ContentXML, 'Sephora AND iPad AND App AND Personalizes AND Skin AND Care')
Notice that there are AND in the search. You also can use or as well. Follow the example as bellow.
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');
