Are you developing enterprise software? Then you probably faced with the problem caused by LIKE ‘%...%'. There are some options how you can overcome on this issue even without switching to another database or database architecture once you are matured relational database like Microsoft SQL Server.
The personal part also matters, some solutions are not code based, read about it at the end of the article.
Business problem
When you are building a real enterprise software you usually have some sort of data distribution across table - supposed that you are using a relational database system - and in all cases you have several hundred thousands or million of records on certain tables. What you also have is a large number of users who used to use Google Search with its simple “one textbox” query interface and they are happy with that so they are asking you to allow them to use something similar.
“Like” is your friend
In social media for sure… In SQL, maybe… Like can only work effectively when you are able to deal with your customer/users on the cost of the implementation/complexity of the environment/functionality. If you can’t do that like can quickly emerge as a performance bottleneck. Let’s discuss why and what agreements you need to make to be able to use the simplest approach from an implementation perspective.
Prerequisites for using “Like”
Basically nothing, which makes it super easy to use. You just need to make sure you store your texts in character typed column like char, nchar, varchar and nvarchar. You can even use it with “unlimited” length (nvarchar(max), varchar(max)).
Potential issues
Designing supporting indexes for the like statement is limited, but as a developer you can have multiple approaches. All of these have only one aim: avoid full table scans on large tables, basically to limit the number of strings in which the like checks for presence of the search phrase.
Of course it also matters when you need to check for certain words in fields containing book length text, but we will skip “War and peace” issue for now.
#sql #software-development #sql-server #full-text-search #software-design
