Feb 23 2021 01:20 AM
In the table "KBitAllgemeineDokumente" we have an field "XXVOLLTEXT". For this field an fulltext catalog exist on the SQL Server 2019. Our Application fill the field during we archive documents and the SQL Server fill then the fulltext catalog. When we search the value is really only in one record. The fulltext search bring five records.
When we search in the field XXVolltext we get one record
SELECT * FROM [KBitAllgemeineDokumente] where [XXVolltext] like '%0102013298%'
When we search over the fulltext catalog we get five records.
SELECT * FROM [KBitAllgemeineDokumente] INNER JOIN CONTAINSTABLE ( [KBitAllgemeineDokumente], [XXVolltext], '0102013298') AS KEY_TBL1 ON [KBitAllgemeineDokumente].XXSYSID= KEY_TBL1.[KEY]
A search only over the field XXVolltext is no solution, because it is a "LIKE Search" and this will be do every time a fulltablescan on the database.
We have already done a rebuild of the fulltext catalog. Can you help us?
Feb 23 2021 06:03 AM
@MartinO1905, why do you use CONTAINSTABLE, that can return more the one result row?
Use CONTAINS in the WHERE clause instead, like
SELECT *
FROM [KBitAllgemeineDokumente]
WHERE CONTAINS([XXVolltext], N'0102013298')
See
https://docs.microsoft.com/en-us/sql/t-sql/queries/contains-transact-sql?view=sql-server-ver15
Feb 23 2021 07:18 AM
Hi, thanks. But when we search which contains we get also 5 records.
We get also 5 records when we search with "".
SELECT * FROM [KBitAllgemeineDokumente] WHERE CONTAINS([XXVolltext], '"0102013298"')
Anything wrong in the full-text catalog?