SQL Server 2008 freetexttable on phrase words

Question

I read the related topics to my question but none of them would provide any results even though my issue is similar with the ones described.

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/a20ddb82-c3e7-4e1f-91b9-f865f9d2a6c5/
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/42409ada-9535-4920-aebf-6e583df689e8

In sql 2005 when I search for "traffic room meeting" (double quotation) will return nothing.

In sql 2008 searching for the same "traffic room meeting" will return 177 rows that do not contain the search phrase as is at all, even more than that, some of the results do not contain any word as typed but just words like "meet".
Even more puzzling, if I search for "traffic room" I will get 150 rows, less than the more complicated phrase "traffic room meeting". For sure it looks like somehow even though I want to search the phrase enclosed under double quotation, SQL Server 2008 is doing a search like: traffic or room or meeting 

Is this a bug or do I miss some settings ? I know about the new changes that were implemented in 2008, specifically at this link but even so, the behavior is not consistent with the specification:
http://technet.microsoft.com/en-us/library/ms143709.aspx

Feature	Scenario	SQL Server 2005	SQL Server 2008
FREETEXT or FREETEXTTABLE	Searching for a exact phrase.	If the search string (freetext_string) is enclosed within double quotation marks, stemming and thesaurus matches are not performed, and the string returns only exact matches as if it were a phrase search. For example, specifying "fast ships" in a FREETEXT predicate would return only rows that contained "fast ships". The phase "ship your package fast" would not be returned.	Phrase searches are no longer allowed by FREETEXT and FREETEXTTABLE, which perform stemming and thesaurus matches regardless of whether single or double quotation marks enclose the search string. For example, searching on "fast ships" would return both "fast ships" and "ship your package fast".

Answer 1

Too much talking on my previous posting, I really need an answer. This is my issue, short-version. Table is ContentMain (ContentId, ContentTitle)

This is the request, and I cannot explain the results, I thought it should be empty.

WITH Entries AS ( 
	SELECT ROW_NUMBER() OVER (ORDER BY f1.rank desc) AS RowNumber, f1.rank rank1, ContentID, ContentTitle
	FROM ContentMain
LEFT JOIN FREETEXTTABLE(ContentMain, (ContentTitle), '"traffic meeting room"') on ContentMain.ContentID=f1.[key] 
where not f1.rank is null)

SELECT rank1, ContentID, ContentTitle
FROM Entries ORDER BY RowNumber 

rank1     ContentID                            ContentTitle

--------------------------------------------------------------------------------

40          1315                                    Room Booking                                                                              
25          135                                      Animals in Potential Danger in Traffic or on Railway                                      

25          2355                                    Traffic Signal                                                                            

25          2385                                    Traffic Calming Requests                                                                  

25          2402                                    School Zone Traffic Issues                                                                

25          2416                                    Cut Through Traffic                                                                       

25          2456                                    Traffic Signs and Stop Signs                                                              

25          2461                                    Traffic Counts                                                                            

25          2518                                    Traffic Signal Timing Reports

Answer 2

In SQL 2005 wrapping your search phrase in double quotes would disable all the fuzzy features of freetext, ie it would not stem, nor would it do nearness or expand your search to a thesaurus based search. It was the functional equivanent of a phrase based contains/containstable search.

In SQL 2008 this has been disabled, it now does a stemmed, near, theasurus based search.

---

looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 3

Thanks a lot, that explains why I am getting "meeting" when search for "meet". It would be great if you can comment on my other question. 

When I search for  '"traffic meeting room"'  in ContentTitle column why am I getting "Room Booking" as result ??? It is only room that fits the search, the other two words are not present (i.e. traffic and meeting). Is this normal ?

Microsoft example says: searching on "fast ships" would return both "fast ships" and "ship your package fast".

That's great but when I search "fast ships" it shouldn't return "fast cars", "bluenose is a racing ship" - shouldn't it ?

And this is what's happening in my case, can anyone confirm this behaviour ?

Answer 4

It should but the ranking of the rows containing "fast cars" and "bluenose is a racing ship" should be much lower than rows contains "fast ships"

---

looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941