SQL Server Full-Text Search – Managing Thesaurus Entries

Hi we can query multiple tables in a full-text search by using views ,but we some advantages and disadvantages . Here in your case you created view dbo.vw_recipe_search it seems your are not using it in altered procedure when i query it i am getting results as per intended

  SELECT *
        FROM dbo.vw_recipe_search r 
        INNER JOIN FREETEXTTABLE(vw_recipe_search, *, 'salsa peas') kt
            ON r.search_id = kt.[KEY]
        ORDER BY RANK DESC;

The problem in this approach is ranking and weightage of each column , like suppose if u want to give high weight to title column than descrip you have to do a lot of work in order by rank column . We used Levenstein string comparision function to order for better results set

I do not believe that is a supported feature of SQL Server full text searching. That would require a wildcard resolution to words and then a thesaurus lookup of each matching word to gather the thesaurus terms.

This basically maps to a pretty complex query: some one of a group of prefixed words is very near to some one of another group of prefixed words which all then go through a thesaurus lookup to provide even more words. Based on previous experience, that is just not supported.

(I see online that you have asked this elsewhere in the last few months, but without any answers, so I hope that this helps.)

I believe that you can create something useful for your query, but it probably requires externalizing the thesaurus entries by doing something like the following:

DECLARE @FirstGroup TABLE (AGroup NVARCHAR(50));
INSERT INTO @FirstGroup VALUES ('hdd');
INSERT INTO @FirstGroup VALUES ('hard disk');
INSERT INTO @FirstGroup VALUES ('hard disc');
INSERT INTO @FirstGroup VALUES ('harddisk');

DECLARE @SecondGroup TABLE (BGroup NVARCHAR(50));
INSERT INTO @SecondGroup VALUES ('sata');
INSERT INTO @SecondGroup VALUES ('msata');
INSERT INTO @SecondGroup VALUES ('esatap');

DECLARE @SearchString NVARCHAR(2000);
SET @SearchString = ''

SELECT @SearchString = @SearchString + '("'+AGroup+'"~"'+BGroup+'") OR '
FROM @FirstGroup CROSS JOIN @SecondGroup 

SELECT @SearchString = LEFT(@SearchString, LEN(@SearchString) - 3);

SELECT * FROM dbo.TBL_NAME
 WHERE contains(field_name,@SearchString);

So if you load the tables with the candidate thesaurus entries and make the OR connections in the code, it should work just fine.

You did not mention which version of SQL Server you are running, but if you are using SQL Server 2012, you might want to use the newer and more powerful syntax. However, this article also has hints about the legacy NEAR context.
http://technet.microsoft.com/en-us/library/ms142568(v=sql.110).aspx