This is a brilliant concept, and I'm amazed I have not seen it anywhere else!
There are some known issues with full-text search:
- when searching, you need to look for "Words", you cannot use arbitrary substrings
- you cannot (easily?) "partition/order" a full-text index by a key, eg "UserID" or "ClientID" - in a shared-tenant architecture (SaaS environment with multiple/many clients in a single DB) this can be a very serious issue!
- Administration/Maintenance is very painful in SQL Server 2000 and earlier (have not tried 2005 but reputedly much better)
If instead of using a Trigger to do the "tokenizing" in this solution you used a scheduled job, along with trigger to maintain an "UpdateRequired" flag of some sort on the record, for the job to look at, you would basically be building your own "text search light" system, suitable for all sorts of uses...
It does have major disadvantages of course:
- will use much more space for the tokens than full-text search would
- will be less efficient when tokenizing
- will be less powerful when tokenizing (no word root identification etc)
- will probably/possibly be slower when returning matches on entire table (but faster on subset by a key that you specify)
All in all a great option to keep in mind though I think - does anyone see other major disadvantages (or advantages) that I am missing?
for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.