December 22, 2020 at 6:35 pm
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 :
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.
December 22, 2020 at 10:09 pm
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.
December 22, 2020 at 10:42 pm
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.
Steve O.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply