Need help with Sql Server Full Text Search problem.

Question

Hi folks, I have a Full Text Catalog on single table, with three fields defined :-

TABLE: Animals
Fields: Name, Breed, LatinName.

Now, the Catalog seems to be working perfectly.

eg.

CREATE FUNCTION AnimalSearch
(
    @Name NVARCHAR(200)
) RETURNS TABLE AS
RETURN 
(
    SELECT KEY_TBL.[Key] as Name,
        KEY_TBL.RANK as Relevance
    FROM CONTAINSTABLE(Animals, Name, @Name) AS KEY_TBL
)

Now, when i run this, i get the following results :- Name = ma (no results) Name = mat (no results) Name = matt (1 result - correct).

SELECT * FROM [dbo].[AnimalSearch]('ma')

Is this the correct way to use this? I've also tried replacing CONTAINSTABLE with FREETEXTTABLE .. same thing .. no results.

Any ideas, anyone?

Edit

I understand that this could be achieved in a stored proc. I'm was hoping to do this as a Table-Valued Function, so i could use this in some Linq2Sql. If it's really unperformant, then please say so.

Answer 1

Not sure it's a good idea. Table valued functions do not store statistics, so performance may suffer.