Killing FTS In almost every application that I've helped build, we had a need to perform textual searches of fields. Even short fields, like names, usually need some sort of wildcard search to let users find information in the database. When we get to larger volumes, such as note or description fields, it's essential that the application allow a way to find information that doesn't exactly match some term. SQL Server has had the Full-Text Search (FTS) subsystem for a long time. Built as an improvement over the LIKE keyword, FTS implements its own index system that better understands language and takes some burden off the developer when trying to find keywords and terms inside of a volume of text. However, this system hasn't advanced much since 2005, with limited improvements in each version, and more stable performance, but not substantially improved. New languages get added, but not development language improvement. These days, many of the individuals that need to search text fields used ElasticSearch instead. In previous years, Lucerne was a popular choice, along with other software add-ons. In fact, most people want to use anything other than SQL Server's FTS. Even at SQLServerCentral, our experimentation with FTS led us to abandon this early on as the work required to structure a UI around the CONTAINS code, along with poor search performance, made the decision easy. Google custom search or another package are far superior. I wonder if it's time for SQL Server to abandon FTS. Maybe their limited development resources would be better spent integrating other third party engines into SQL Server. Or maybe some of their profits are better spent licensing or purchasing a different technology for searching. Certainly they should deprecate the current FTS implementation and allow applications using it to continue to do so, but cease additional work beyond limited maintenance. At the very least, they should move in some direction. I'm sure Microsoft could come up with ways to improve FTS, but I don't know they can do it quickly enough, or that it's a good use of developer resources. There is other technology that does this very well, so take advantage of it and build hooks that make integration simple. That might be the big search win for SQL Server databases. Steve Jones - SSC Editor Join the debate, and respond to today's editorial on the forums |
