Getting the most out of Full Text can be like walking in a wilderness without a map, compass or satnav. There are some details in BOL, and Google can find some good articles, but the more you know about Full Text the more you realise ow little good advice exists.

A few tips...

1) Work out what queries you need in a query window before you put anything into a SP or CLR code. Your first, second, third, etc attempts are unlikely to get the best out of Full Text.

2) Using CONTAINSTABLE allows you to fine tune your results more than the other access methods. If a simple FREETEXT or CONTAINS does not give you what you need then you need CONTAINSTABLE and probably the ISABOUT clause with weightings.

3) The documentation does not make it clear that ISABOUT('value1', 'value2') really means ISABOUT('value1') OR ISABOUT('value2'). If a match is found with 'value1' then do not expect FTS to also search for a match on 'value2'.

4) If you apply weightings in ISABOUT, the best results are given if the order of the weighting is descending. e.g. if you have a series: ISABOUT('value1' WEIGHT (0.001)) AND ISABOUT('value2' WEIGHT (0.9)) AND ISABOUT('value3' WEIGHT (0.5)) will give a different result to the final rank than if the weights were given in descending order 0.9, 0.5, 0.001. Giving the weights in descending order helps give a higher rank to the clause with the heighest weight.

5) To get good performance, you need to minimise the number of Full Text accesses you make in a single T-SQL query. Aim to get everything you need in a single FREETEXT or CONTAINSTABLE clause.

6) To get good performance, you need to avoid filtering the results from a FTS search by joins to other tables. e.g. SELECT model FROM CONTAINSTABLE(trucks, colour, FORMSOF('red')) as ft JOIN trucks on ft. = trucks.key WHERE trucks.wheels = 4 will first select all types of truck where the colour is a shade of red, then filter the list to just give you 4-wheeled trucks that are a shade of red. This filtering can take a long time to complete. You need to look at how you can search for both shades of red and 4 wheels in a single CONTAINSTABLE so FTS only gives back the rows you really need.

Finally, FTS can give fantastic results, but it can take a while to work out how to use it most effectively.