Sql Server 2017 with Transact and synonyms Problems. Microsoft does not respond.
-
I am using Sql Server 2017 with Transact and a file with synonyms in FTData. I have found several serious flaws and problems but in the Microsoft MSDN forum and in Microsoft connect no one answers me. The rest of the people say they do not know how it works. Video: https://youtu.be/Il7-DDKfHiU Problem 1: Freetexttable Error With More of 3 Words. I use Freetexttable with thousands of synonyms in a simple query in two Pc. By entering up to 3 words "Microsoft Sql Management Studio" instantly returns the result and the memory remains stable in 6Gb. If I enter 4 words he is thinking for 10 minutes and the memory consumes until 12Gb. At the start of the video you can see what it takes Sql Server to load the synonyms and every few minutes if Sql Server is not used again it loads them again. Example: SELECT [Table].*, FT.* FROM [Table] INNER JOIN FREETEXTTABLE([Table], [Contenido], 'Word1 Word2 Word3') FT ON [Table].Id=FT.[Key] WHERE ([Table].STATE IS NULL OR [Table].STATE = ' ') ORDER BY RANK DESC, Page Problem 2: Transact does not return the inflections of a word if you do not accent it correctly. When looking for the inflectional of a word, if I introduce the word with the accent the inflections appear well. The server collation is SQL_Latin1_General_CP1_CI_AI If I introduce the word without the accent, the inflections do not appear. In Spanish café and cafés are accentuated. Video: https://youtu.be/4DX-Y3eoDo4 SELECT display_term FROM SYS.DM_FTS_PARSER('FORMSOF(INFLECTIONAL, café)', 3082, 0, 0) display_term returns: cafes cafe SELECT display_term FROM SYS.DM_FTS_PARSER('FORMSOF(INFLECTIONAL, cafe)', 3082, 0, 0) display_term returns: cafe When doing the same with the synonyms, returns the same results with or without accent. SELECT display_term FROM SYS.DM_FTS_PARSER('FORMSOF(THESAURUS, cafe)', 3082, 0, 0) SELECT display_term FROM SYS.DM_FTS_PARSER('FORMSOF(THESAURUS, café)', 3082, 0, 0) display_term returns: cafe chicoria sucedaneo mezcla chicoria. In Spanish sucedáneo are accentuated. Note:After making the video I tried to recover the inflections of the word echaré (with and without accent) and returns the inflections well in both cases. So the problem is with some words. I have found a very interesting example. I use the verb "echar". Among others, it has 2 inflections that are "echaré" and "echaría". If I consult "echaré" with and without an accent, it works well