Too many Count / Fulltext catalog

%3CLINGO-SUB%20id%3D%22lingo-sub-2158489%22%20slang%3D%22de-DE%22%3EToo%20many%20Count%20%2F%20Fulltext%20catalog%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2158489%22%20slang%3D%22de-DE%22%3E%3CP%3EIn%20the%20table%20%22KBitGeneralDocuments%22%20we%20have%20an%20field%20%22XXVOLLTEXT%22.%20For%20this%20field%20an%20fulltext%20catalog%20exist%20on%20the%20SQL%20Server%202019.%20Our%20Application%20fill%20the%20field%20during%20we%20archive%20documents%20and%20the%20SQL%20Server%20fill%20then%20the%20fulltext%20catalog.%20When%20we%20search%20the%20value%20is%20really%20only%20in%20one%20record.%20The%20fulltext%20search%20bring%20five%20records.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20we%20search%20in%20the%20field%20XXFull%20text%20we%20get%20one%20record%3C%2FP%3E%3CP%3ESELECT%20*%20FROM%20%5BKBitGeneralDocuments%5D%20where%20%5BXXFull%20Text%5D%20like%20'%250102013298%25'%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20we%20search%20over%20the%20fulltext%20catalog%20we%20get%20five%20records.%3C%2FP%3E%3CP%3ESELECT%20*%20FROM%20%5BKBitGeneralDocuments%5D%20INNER%20JOIN%20CONTAINSTABLE%20(%20%5BKBitGeneralDocuments%5D%2C%20%5BXXFull%20Text%5D%2C%20'0102013298')%20AS%20KEY_TBL1%20ON%20%5BKBitGeneralDocuments%5D.%20XXSYSID%3D%20KEY_TBL1.%5BKEY%5D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20search%20only%20over%20the%20field%20XXFull%20text%20is%20no%20solution%2C%20because%20it%20is%20a%20%22LIKE%20Search%22%20and%20this%20will%20be%20do%20every%20time%20a%20fulltablescan%20on%20the%20database.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20have%20already%20done%20a%20rebuild%20of%20the%20fulltext%20catalog.%20Can%20you%20help%20us%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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?

2 Replies

 

@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 

@olafhelper 

 

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?