SQL Server: Normal Index vs. Fulltext Index

Question

what exactly is the difference (and advantages/disadvantages) between a fulltext index and a regular index on a varchar column? When would I use which index?

I have a multitude of varchar columns (addresses - city name, street name etc.) which I need to be searchable in the most performant way and I'm trying to figure out which index type to use and why.

Thank you!

Answer 1

It depends on the kind of search you want to do. For example, you can't use a normal index with this query:

SELECT * FROM [MyTable] WHERE [MyColumn] LIKE '%' + @SearchText + '%'

It's not sargable. This is sargable, but the selectivity might not be very good:

SELECT * FROM [MyTable] WHERE [MyColumn] LIKE @SearchText + '%'

You use a full-text index completely differently:

SELECT * FROM [MyTable] WHERE CONTAINS([MyColumn], @SearchText)

Answer 2

Usually, when searching with a normal index, you can search only in a single field, e.g. "find all cities that begin with A" or something like that.

Fulltext index allows you to search across multiple columns, e.g. search at once in street, city, province, etc. That might be an advantage if you want to do something like a Google-style search - just punch in a search term and find all rows that have that search term anywhere in any of the varchar columns.

Additionally, with a regular search, you are fairly limited in what you can do - you can search for an exact match or just LIKE - that's about it.

With fulltext index, you can search for word forms (ran, run, etc.) and also for similar words by specifying your own thesaurus. You can search based on several languages if that's an issue. You can search for entries that have two or more terms that are "NEAR" one another.

Marc

Answer 3

From the MSDN:

In contrast to full-text search, the LIKE Transact-SQL predicate works on character patterns only. Also, you cannot use the LIKE predicate to query formatted binary data. Furthermore, a LIKE query against a large amount of unstructured text data is much slower than an equivalent full-text query against the same data.

A LIKE query against millions of rows of text data can take minutes to return; whereas a full-text query can take only seconds or less against the same data, depending on the number of rows that are returned.