Integrate regular and full text index in SQL Server

Question

Hi

I have a table in SQL Server with the following columns:

id int (primary key)
text nvarchar(max) (full text indexed)
type int

and I have queries like this:

where Contains([text], @text)

or

where Contains([text], @text) AND [type] = 3

However the second query is slow. I think I should integrate full text index with [type] field.

Is there another solution?

Thanks

Answer 1

Given that you cannot add an integer field to a full text index your best bet is to add a regular index to [type].

Answer 2

I'm assuming you're not running SQL 2008, as the integrated full text engine in that version should make better decisions for a query such as yours. For earlier versions, I've had success by embedding additional keys in the text with some form of a custom tag. You'll need some triggers to keep the text up to date with the keys.

e.g., "This is my sample text. TypeKey_3"

Then your where clause becomes something like:

where Contains([text], @text AND "TypeKey_" + @type)