sp_BlitzErik's answer hits on a lot of good points, but I don't think that's why you shouldn't use Full Text Search. Full text search isn't there to do what you think it does. It's not there to search multiple fields. It's there to vectorize word content and make use of dictionaries, stubbing, lexers, gazetteers, stop-word elimination, and a slew of other tricks none of which apply. Or, have not yet been shown to apply.
I don't agree with the solution either, though I'm not sure how to do this better in SQL Server. Let's recreate his data for PostgreSQL -- it's a lot cleaner to create in in PostgreSQL too.
CREATE TABLE fulltextindexesarestupid
CASE WHEN Id % 15 = 0 THEN 'Bad'
WHEN Id % 3 = 0 THEN 'Idea'
WHEN Id % 5 = 0 THEN 'Jeans'
END AS StopAbusingFeatures
FROM generate_series(1,1000000) AS id;
Now what you want is an enum type,
CREATE TYPE foo AS ENUM ('Bad', 'Idea', 'Jeans');
ALTER TABLE fulltextindexesarestupid
SET DATA TYPE foo
Now you've collapsed the strings to integer representations. But even better you can query them like before.
WHERE StopAbusingFeatures = 'Bad';
This has the effect.
- conceals the fact that you're categories are an enumerated type. That complexity is encapsulated in the type and hidden from the user.
- it also places the maintenance on those categories on the type.
- it's standardized.
- it doesn't grow the row size.
Without these benefits, you're essentially just trying to optimize out string comparison. But alas, I'm not even sure how sp_BlitzErik gets to the answer given the code in the suggestion,
like '%rock%' or
like '%paper%' or
like '%scisor%' or
like '%car%' or
You can collapse the tokens down to integers using an enum, or the hand-rolling method suggested by sp_BlitzErik but if you can do the collapsing why are you doing the unanchored-like too? Ie, If you know '%pasta%' is the token 'pasta' why do you have the
% on both sides of it. Without '%' this an equality check and it should pretty fast even as text.