Full Text Search Installed or Not

Full text search installed or not

My answer:

If FULLTEXTSERVICEPROPERTY says it's not installed, then I would install from the original media. Run through the installer and simply add Full Text Search from the features page.

FTS is fully in the SQL Engine in 2008 so if it thinks it isn't installed, then ya best make it happy.

My opinions/ponderings:

Did you move a database from a previous SQL installation that had full text installed? That might explain the row in sys.fulltext_catalogs.

When you open a Database in SSMS, under the Storage Folder, Full Text Catalog folder do you have the option to add a New Catalog when you right click?

In SQL Configuration Manager do you see the Full Text Daemon Launcher service?

Full-Text Search is not installed, or a full-text component cannot be loaded

Looks like the problem is that the full text catalogue was created BEFORE the full text component was installed.

I was fortunate to be have the luxury to drop the database and recreate the tables from scratch. The above three commands now worked.

If I couldn't drop the database, then probably deleting the full text catalogue and then recreating it would solve it (better than my drop database sledgehammer approach!)

Can't find full text search in SQL Server 2019 Setup

I changed the installer to "custom" like @AaronBertrand said and it worked perfect.

I didn't even need to reinstall the whole server and all databases were still there.
If you have the same problem still make a backup of all your database just in case.

How to install Full-text search into SQL Server 2016?

Sounds like you are using LocalDB, which doesn't support it.

Full-text indexing is supported on Express with Advanced Services and "better", Editions and supported features of SQL Server 2016 - Programmability:

Feature                           Enterprise     Standard     Web     Express with Advanced Services     Express
Full-text and semantic search     Yes            Yes          Yes     Yes                                No

You will need to use Express with Advanced Services, rather than LocalDB if you want to use Full-text Searching.

How to add full-text search to SQL Server Express 2019 installation

Use this official download of the Microsoft SQL Server Express web installer. Select "Download Media" and pick the install medium that is right for you.

Download page

Web installer after picking "Download Media"

SQL Server FullText Search not returning expected rows

CONTAINS:

• A word or phrase.

• The prefix of a word or phrase.

SELECT *
FROM [dbo].[Test_FT_Search]
WHERE CONTAINS([Account_Number],'4445002020891'); --a word or phrase

SELECT *
FROM [dbo].[Test_FT_Search]
WHERE CONTAINS([Account_Number],'"44450020208*"') ; --prefix of word: prefix+asterisk in double quotes 

SQL Server Full Text Search not returning expected results

It appears you are misunderstanding CONTAINS and LIKE, they are not equivalent operators. From CONTAINS (Transact-SQL):

CONTAINS can search for:

• A word or phrase.
• The prefix of a word or phrase.
• A word near another word.
• A word inflectionally generated from another (for example, the word drive is the inflectional stem of drives, drove, driving, and driven).

A word that is a synonym of another word using a thesaurus (for
example, the word "metal" can have synonyms such as "aluminum" and
"steel").

Emphasis my own.

CONTAINS does not support a leading wildcard, only a trailing one. As a result, (as I say in my comment) for a column (title) with the value 'abaycant' the expression WHERE title LIKE '%baycan%' will return TRUE, however, CONTAINS(title,'baycan') will not.

If you have the value 'baycant' then LIKE 'baycan%' and CONTAINS(title,'baycan*') (and LIKE '%baycan%') would all return TRUE; the former 2 would also be SARGable too (the one in parenthesis would not be).

If the value were 'best baycant' then both CONTAINS(title,'baycan*') and LIKE '%baycan%' would return TRUE, but only the former would be SARGable.

This is also further supported later in the argument section in relation to the phrase parameter (the second parameter) when using it as a prefix (there is not option in the arguments for suffix or "in the middle of"):

<prefix_term>

Specifies a match of words or phrases beginning with the specified
text. Enclose a prefix term in double quotation marks ("") and add an
asterisk (*) before the ending quotation mark, so that all text
starting with the simple term specified before the asterisk is
matched. The clause should be specified this way: CONTAINS (column,
'"text*"'). The asterisk matches zero, one, or more characters (of the
root word or words in the word or phrase). If the text and asterisk
are not delimited by double quotation marks, so the predicate reads
CONTAINS (column, 'text*'), full-text search considers the asterisk as
a character and searches for exact matches to text*. The full-text
engine will not find words with the asterisk (*) character because
word breakers typically ignore such characters.

When <prefix_term> is a phrase, each word contained in the phrase is
considered to be a separate prefix. Therefore, a query specifying a
prefix term of "local wine*" matches any rows with the text of "local
winery", "locally wined and dined", and so on.

FREETEXT will not change this. FREETEXT (Transact-SQL):

When FREETEXT is used, the full-text query engine internally performs
the following actions on the freetext_string, assigns each term a
weight, and then finds the matches:

• Separates the string into individual words based on word boundaries (word-breaking).
• Generates inflectional forms of the words (stemming).
• Identifies a list of expansions or replacements for the terms based on matches in the thesaurus.

Again, emphasis my own.

A value like 'abaycant' doesn't have any word boundaries, so FREETEXT(title, 'baycan') will not work. If you need a leading wildcard, due to the need to search within a word, you cannot utilise a Full Text Search, as an FTS indexes the words, not the characters.

---

Related Topics