SQL Server fulltext - Search failures
After four years of working without error our full text searches have been returning results that are incorrect. Sometimes we get fewer matches than expected, sometimes more. Back to back searches for the exact same search criteria can give different results. This problem seems to come and go at random times. Mostly it works correctly.
The full text table contains 30,038 entries and is rarely updated, two records per week would be a lot.
MSSQL version 11.0.7493.4.
You can try the search by visiting Missouri Revisor
Any insights appreciated.
The query to search for mule. which should return 14 matches.
Code:
SET NOCOUNT ON; SELECT [BID] FROM [StatConst].[revision].[SectFullText] WHERE CONTAINS(body, '"mule" ') and [hist] Is Null
[StatConst].[revision].[SectFullText] has four columns,
BID int
Body nvarchar(MAX)
hashnum int
hist int
Re: SQL Server fulltext - Search failures
1) Since you're using double quotes (simple_term = exact match case insensitive), have you tried the LIKE-Operator instead? Maybe lower casing everything before comparing
Just to test if the results are the same resp. "wonky"
2) Since "hist" is an int, have you tried hist=0 (or-ed with Is Null)? Maybe someone changed the default-value
Re: SQL Server fulltext - Search failures
Quote:
Originally Posted by
Zvoni
1) Since you're using double quotes (simple_term = exact match case insensitive), have you tried the LIKE-Operator instead? Maybe lower casing everything before comparing
Just to test if the results are the same resp. "wonky"
2) Since "hist" is an int, have you tried hist=0 (or-ed with Is Null)? Maybe someone changed the default-value
If I rebuild and repopulate the problem goes away.