CODEWITHSUNDEEP
sqlsql-serverindexingfull-text-search
Niels Brinch
Niels Brinch

Reputation: 3672

Full Text Search in interaction with index that has other columns

I have a table with the following columns:

Id, int
FirstName, nvarchar
LastName, nvarchar
Description, ntext

I have a Full Text Catalog covering the Description field.

My query:

SELECT FirstName, LastName
WHERE LastName = 'Johnson'
AND CONTAINS(Description, 'house')

Now I'm wondering which index to use to optimize this query in order to be able to take full advantage of the Full Text Catalog. Would SQL Server be able to take advantage of an index on LastName in this case?

(I know I could just test it, but my real table and query structure is obviously not as simple as the above, so I'd rather know from someone who actually knows how it's supposed to work)

Upvotes: 1

Views: 106

Answers (1)

Remus Rusanu
Remus Rusanu

Reputation: 294417

Yes, an index on LastName will help. SQL Server may create a plan that does basically a join between a range scan on the NC index for 'Johnson' and the result of the full text index search. This whitepaper contains some details that are of interest to you: SQL Server 2005 Full-Text Queries on Large Catalogs: Lessons Learned.

Upvotes: 3

Related Questions