As it happens, I was messing with it today... on SEDE, and it's not there.
That is a loaded question though. let me run you through a history lesson.....
databases are rows and columns.
to make them faster, the columns could be indexed, and almost all index systems use some form of b-tree.
this means you can locate data in about log-n time.
That is for 'equi-join' conditions .... where column = 'someval'.
Databases became highly tuned by the DBA's / designers.... and they avoid, for most purposes, IO.
Indexes became much smaller than the data indexed, for the most part.
Some databases are better than others, some use mitmap indexes for some fields, etc. There are some differences, but, what's key, is that:
the index indexes the whole value in the field, not a part of it.
So, along comes big text data, and it sucks, because now you have to index subsets of the data, or, alternatively, you have to scan/process the entire field every time it is used.
So, databases have this data which does not conform to the standard mechanisms available in the system, so they have to come up with a solution.
They all did it differently.
But, the results are that you need to specify full-text on your field, and the database does some fancy analytics, and indexes the data in the column in a different way.
So, that's why you have it, to index sub-parts of text fields.... now, the question is why you need such text fields....
well, the classic example, is you want to search your e-mails, but they ar ein the database.... what to do?
Search for e-mails referencing "Code Review".
your full-text supports that..... but... the rel question is whether your database is the right tool for the job.
For the most part, there's two opinions, yes, and no.
The yes people believe that keeping your data in one place is a good thing, and leads to efficiencies.
the no people will tell you to use a system that is designed for it, like Solr/Lucene (or ElasticSearch)
Now, take Stack Exchange, they have these cool posts....
We know they store their data in SQLServer, right?
But, if you search, you are actually hitting their ElasticSearch system which has the text content indexed in a number of fields.
the default field is the 'Body', but you can also search the title, user, date, score, type fields too.
so, searching for [java] title:Database user:me table
will perform searches in elastic search on different fields, the tag, title, user, and Body.
The result will be a reference back to the SQL Server document to post in the results.
The classic terms are: structured vs. unstructured data.
The algorithms and data structures needed to index and search unstructured data do not conform well to data stored in rows and columns......
@Phrancis ...... Wake up.
5