Although we have ElasticSearch running in our environment, our most important search functionality is still handled by SQL Server. And no, it is not by writing a statement like WHERE Name LIKE '%term%' . That would probably work on a small table, but we have a lot of data to dig through, so we need something smarter than substring matching. SQL Server has a built-in answer for this: Full-Text Search . In this post I walk through the building blocks and the main features. In the next post I'll show what happened when a customer ran into a surprising side effect. Why do we need this? A LIKE with a leading wildcard can't use an index seek. SQL Server has to scan every row and compare strings. On top of that, LIKE knows nothing about language: searching for 'ride' won't find 'riding', and there is no concept of relevance. Full-text search works differently. It builds an inverted index: a list of words (tokens) and, for every word, the rows wh...