ElasticSearch vs SQL Full Text Search

c# .net sql-server full-text-search elasticsearch
Adam Bremen picture Adam Bremen · Dec 11, 2013 · Viewed 14k times · Source

I want to use full text search in my project... Can anyone explain me, what is the difference between ElasticSearch and SQL Full Text Search

Or

why SQL Full Text Search is better (worse) than elastic?

documentations, presentations, schema...

Answer

Marc Gravell picture Marc Gravell · Dec 11, 2013

Define "better"... sql full text search is fairly trivial to get working (indexing and query) - but it has penalties:

  • very little (virtually no) control over how things are indexed (what the index keys are; what the lexers/stemmers/etc are; etc)
  • runs on the sql server - which is usually your least scalable infrastructure

Elastic search requires more work; you need to setup and maintain a dedicated cluster of nodes, and then provide code that performs the actual index operations, which may also involve a scheduled job that works from a change-log (processing new / edited data), building the fragments to be indexed; equally, you need to then take more time building the query. But you get a lot of control over the index and query, and scalability (a cluster can be whatever size you need). If it helps any, Stack Overflow grew up on sql full text search, then moved into elastic search when the limitations (both features and performance) proved prohibitive.

Related questions

How to get a list of StopWords used in my FullText Catalog?

Is there a way to get the StopWord list that my SQL Server 2008 FullText Catalog is using? And use it, in my C# codebehind? I want to use it in a ASP.NET page that I use to search terms …

Read More
C# Equivalent of SQL Server DataTypes

For the following SQL Server datatypes, what would be the corresponding datatype in C#? Exact Numerics bigint numeric bit smallint decimal smallmoney int tinyint money Approximate Numerics float real Date and Time date datetimeoffset datetime2 smalldatetime datetime time Character Strings …

Read More
What is the equivalent of bigint in C#?

What am I supposed to use when handling a value in C#, which is bigint for an SQL Server database?

Read More