SQL Server 2005 Full-text search: Noise Words

Question

Using ContainsText, If I search: "Report Part 1" in quotes it returns the correct result (Report Part 1). However, if I search: Report Part 1 it gives me zero results.

My understanding was that full-text would take out the 1 as a noise word, and then do a search for contains Report and Part. I assumed that the results would give me Report Part 1, Report Part 2, etc, not zero results.

Can anyone give me insight as to why full text search is working this way?

Answer 1

This might be due to SQL Server filtering out searches with noise words. What happens if you enable 'transform noise words'?

sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'transform noise words', 1
RECONFIGURE
GO

This makes SQL Server transform all noise words in your query to a '*'.