Full Text Search and Enablement at DB Level - Day One.

Question

Full Text Search and Enablement at DB Level - Day One.

SteveOC

Ten Centuries

Points: 1255
More actions
December 22, 2020 at 6:35 pm

#3823279
I have 2 DBs - they are 'vendor' DBs - part of an application.
They both originated from a single source DB - so were identical to start with.
One DB has been upgraded and has new functionality from the vendor that utilises full text search - the other hasn't.
The SQL instance has Full Text Search installed and in use for other DBs,
I have run some queries and determined the following :
Non-upgraded DB:
FTS not enabled at DB level - No FTS catalog exists - No Indexed Columns/Tables
Upgraded DB:
FTS not enabled at DB level - FTS Catalog exists - Lots of Indexed Columns/Tables
I am guessing here that the upgrade created the catalog and the Indexed Columns/Tables but that it did not enable FTS at DB level.
The questions I have are :
1. Can you create a catalog and index in a DB that does not have FTS enabled (if not somebody has disabled it)?
2. Can anything access the catalog/Index in a DB that does not FTS enabled?
3. If the answer to 2 is yes - is the data thus stale - or does SQL sever still maintain the Index with FTS not enabled?
4. What will happen if I enable FTS at DB level - will it do a rebuild from scratch or try to do some kind of Update/catchup?
The application upgrade has screens that supposedly utilise FTS and was demoed today. Nothing failed - but to all intents and purposes FTS is not enabled on this DB (at DB Level) so I am now wondering what it is doing - i.e. is there some kind of failback mode to do substring searches - or is it perhaps using stale data since it is not clear in the docs what the enable at DB level flag actually enables ( i.e. the ability to search using FTS, the maintenance of the Index or what)?
TIA.
Steve O.
- This topic was modified 3 years, 4 months ago by SteveOC.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

SteveOC Ten Centuries Points: 1255 More actions · Answer 1

I ran this query - it was sub-second - and returned 4 rows where the word 'blah' had a space before and a space after it

select * from rsitem where contains(itemdesc,'blah')

I ran this query - it took 10 seconds - and returned 20 rows where the word 'blah' was a substring including the 4 returned above

select * from rsitem where itemdesc like '%blah%'

The updated dates from FTCRAWL all indicate that the Index for FTS is being updated (Auto)

select * from sys.fulltext_indexes

So to all intents and purposes it looks like FTS is working for this DB - and yet this says it is not enabled:

So what is this flag and why does FTS work in this DB if it says it is not enabled?

Steve O.

SteveOC Ten Centuries Points: 1255 More actions · Answer 2

Having found multiple references to the statement that FTS is enabled by default in SQL Server 2008 and cannot be disabled, I am wondering if the fact that these databases likely predate 2008 is anything to do with this.

Certainly, the Full-Text Index option is Greyed out in SSMS for tables in these 2 DBs where isfulltextenabled is set to 0, however FTS otherwise appears to be working.

I tried this (which took a few seconds) and it now shows enabled

exec sp_fulltext_database 'enable';

However Full Text index is still Greyed Out.

Edit - update - I refreshed in SSMS and it is no longer Greyed out.

So the flag seems to have no effect other than whether the option is Greyed out in SSMS?

Steve O.

This reply was modified 3 years, 4 months ago by SteveOC.