Full Text Index vs Index
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.
Full Text Index
A full-text index is a special type of index that provides index access for full-text queries against character or binary column data. A full-text index breaks the column into tokens and these tokens make up the index data. Before you can create a full-text index you must create a FULL TEXT CATALOG and this catalog is where the full-text index data is stored. A full-text catalog can contain many indexes but a full-text index can only be part of one catalog. Also, only one full-text index can be created on each table so if you have more than one column you need to be indexed the columns have to be moved to separate tables. It is also important to note that full-text indexes are not updated right away as is the case with regular indexes. Populating full-text indexes can be resource intensive so there are more options that let you control when they are updated.
The 3 different options are:
- Full population
- Automatic or manual population based on change tracking
- Incremental population based on a timestamp
Use FULL Text Index search when you have a large volume of data and you want to perform search for textual data columns for specific words and phrases. Full Text Index can be used to search words, phrases and multiple forms of a word or phrase using FREETEXT (), CONTAINS () with “and” or “or” operators (FREETEXT, CONTAINS).
Note:
You cannot create an index on a varchar(max) field. The maximum amount of bytes on a index is 900. If the column is bigger than 900 bytes, you can create the index but any insert with more then 900 bytes will fail.
You must have at least one unique index defined.
Examples:
select name, DATABASEPROPERTY(name,'IsFulltextEnabled')
from master..sysdatabases where dbid > 4
select fulltextserviceproperty('isfulltextinstalled')
--EXEC sp_fulltext_database 'enable'Sources:
https://www.mssqltips.com/sqlservertutorial/9136/sql-server-full-text-indexes/
https://www.c-sharpcorner.com/article/full-text-index-in-sql-server/
http://blog.aspcore.net/post/2017/08/01/implementing-full-text-search2
Comments