Efficient Dynamic Query - Part 3

If you miss Part 1 & 2, click the following links.
Efficient Dynamic Query - Part 1
Efficient Dynamic Query - Part 2

In my previous post, I mentioned to share about how to setup Full-Text search in MS SQL Server. So, here you go. I am using MSSQL 2008 R2 for this post, it should be similar for MSSQL 2012.

1. Add feature to your existing MSSQL server:

Insert your MSSQL 2008 R2 installer ISO or disc, then install new or add feature to your existing MSSQL server. In the feature selection window, make sure to check the "Full-Text Search". Then, proceed to complete the installation.

2. Define full-text index on table:

3. Select the unqieu index, can be primary key (clustered index) or unique non-clustered index.

 

4. Select the columns that need full-text index, the language selection is optional, default is English.

5. Set the track changes occurance.

6. Give a catalog name, or select existing one if you have any and wish to join this table to existing catalog.

7. Define index population schedule (optional), the population schedule can be either perform on particular table only or on particular catalog that may contain multiple tables.

8. If you wish to schedule index population, for production environment, recommend that you set the schedule to run population during non peak hour. For development environment, you can set the schedule to run population while CPU is idle. The reason is index population require quite some CPU power and may cause table lock.

9. Verify the actions then you are done with defining full-text index.

 10. If you never schedule any index population, you can do it manually too.

After you are done with above configuration, now you can use the following syntax in your query:

1. CONTAINS

Similar to equal operator (=). You can use AND, OR, NOT operand in the expression.

SELECT *
FROM [dbo].[Profiles]
WHERE CONTAINS (Hobbies, 'cooking')
--WHERE CONTAINS (Hobbies, 'cooking OR fishing')
--WHERE Hobbies = 'cooking'

2. FREETEXT

Similar to LIKE operator

SELECT *
FROM [dbo].[Profiles]
WHERE FREETEXT (Hobbies, 'fish')
--WHERE Hobbies LIKE '%fish%'

3. CONTAINSTABLE

Get a full-text search table with CONTAINS.
The return table only contains 2 columns: KEY and RANK.
KEY is the unique ID of the returned row.
RANK is the score of how well the row matched the criteria. The more score represent the more accurate.

SELECT *
FROM [Profiles] AS T1 INNER JOIN
   CONTAINSTABLE ([Profiles], Hobbies, 
      'cooking'
   ) AS T2
   ON T1.ID = T2.[KEY]

4. FREETEXTTABLE

Similar to CONTAINSTABLE, but predicate with free text search.

SELECT *
FROM [Profiles] AS T1 INNER JOIN
   FREETEXTTABLE ([Profiles], Hobbies, 
      'fish'
   ) AS T2
   ON T1.ID = T2.[KEY]

More detail from MSDN.