Trigram Search In SQL Server

Paul White shows how to implement trigram wildcard searches in SQL Server:

The basic idea of a trigram search is quite simple:

  1. Persist three-character substrings (trigrams) of the target data.
  2. Split the search term(s) into trigrams.
  3. Match search trigrams against the stored trigrams (equality search)
  4. Intersect the qualified rows to find strings that match all trigrams
  5. Apply the original search filter to the much-reduced intersection

We will work through an example to see exactly how this all works, and what the trade-offs are.

A must-read.  N-grams in SQL Server is an example of a non-obvious data architecture which performs much better than the obvious alternative, at least when the conditions are right.

Related Posts

Rant: NoSQL Isn’t A Thing

Grant Fritchey is in rare form today: Go and search through it for a NoSQL data management system. I’ll wait. You found one that was named NoSQL didn’t you. Oracle NoSQL, because Oracle. Of course. However, under the Database Model what did it say? Document Store. Why? BECAUSE NOSQL IS NOT A DATA STORAGE ENGINE! […]

Read More

In Defense Of The Layered Architecture

Sylvia Fronczak defends the layered architecture approach to software development: There are disadvantages to the layered architecture approach. And some aspects of it have been deemed not architecturally pure for domain-driven design. However, there are disadvantages to most approaches. The key is to understand both the advantages and disadvantages. Pick what architectural patterns work best […]

Read More

Categories

September 2017
MTWTFSS
« Aug Oct »
 123
45678910
11121314151617
18192021222324
252627282930