SQL Server 2017: Unexpected Results with Full-Text Search on Multilingual Data
I've hit a wall trying to I need some guidance on Hey everyone, I'm running into an issue that's driving me crazy..... I tried several approaches but none seem to work. I'm experiencing unexpected results when performing full-text searches on a SQL Server 2017 database that contains multilingual data, specifically English and Spanish. I set up full-text indexing on a column of type `nvarchar(MAX)` that includes both languages, but the searches do not return the expected matches. For instance, when I execute the following query: ```sql SELECT * FROM Documents WHERE CONTAINS(TextColumn, '"casa"'); ``` I anticipated it would return records containing the word 'casa', but it returns no results even though there are records that include this word in the `TextColumn`. I have also tried using `FREETEXT` instead of `CONTAINS`, but the results remain the same. I ensured that the full-text index is properly created using: ```sql CREATE FULLTEXT INDEX ON Documents(TextColumn)KEY INDEX PK_Documents ON MyFullTextCatalog; ``` The full-text catalog is set to use the default language, which is English. I considered that this might be affecting the search results, so I explicitly specified the language for the index: ```sql ALTER FULLTEXT INDEX ON Documents SET LANGUAGE 'Spanish'; ``` However, this approach didn't yield the desired outcome either. I've also checked the `sys.fulltext_index_fragments` and `sys.fulltext_indexes` views to confirm that the index is populated and not fragmented. I suspect there might be an issue with how SQL Server handles multilingual data in full-text searches or maybe the way I've configured my full-text search environment for this use case. Could anyone shed light on best practices for implementing full-text search on a multilingual dataset in SQL Server, or suggest any troubleshooting steps I might have missed? Also, I am aware that SQL Server supports language-specific word breakers and stemmers; do I need to create separate full-text indexes for each language in this scenario? For context: I'm using Sql on Windows. Is there a better approach? I'm using Sql 3.11 in this project. Thanks in advance! This is for a application running on Debian. The stack includes Sql and several other technologies.