Will Full text search consider indexes?

Question

Ok I have a full text search index created on my JobsToDo table, but what I'm concerned about is if this is rendering my other indexes on the table useless. I have a normal nonclustered index on the CreatedDate field in this table. So when I run my full text search it returns results, I then filter my full text search by CreatedDate >= GETDATE() - 7 to get the last 7 days worth of JobsToDo. Now is my normal index being used (on CreatedDate) or is it ignoring this index and purely searching on the full text index and then just searching the date criteria on the entire table again? My query looks like this:

// First create an index
CONSTRAINT [IX_JobsToDo] UNIQUE NONCLUSTERED 
(
 [CreateDate]
)

// Now run query
SELECT                      JobId,
                            Title,
                      FROM JobsToDO
                      FREETEXTTABLE (JobsToDo, (Title, [Description]), 'somestring')
                       AND CreatedDate >= GETDATE() - 7;

To summarise, will this query use my index I created on CreatedDate or not?

Answer 1

Yes, the query optimizer will consider using a mix of FT and non-FT indexes. See this whitepaper SQL Server 2005 Full-Text Queries on Large Catalogs: Lessons Learned for more details.

BTW your CreateDate non-clustered index is still subject to the other index usage good practices. In your case, if the number of records in the last 7 days is big enough, the query optimization may fall for the Index Tipping Point because CreateDate does not cover Title (assuming JobId is part of clustered index key, otherwise JobId also needs coverage). On the other hand, if the FT search criteria is very selective then the CreatedDate index may be omitted and the clustered index will be used to probe for the candidates found by the FT index and verify the CreatedDate condition.

Answer 2

The outcome of this totally depends on the version of SQL Server you are using. Prior to 2008, SQL Server had separate query plans for each part of a query when you had both full-text and non-full-text predicates. Since 2008, all the work is now done inside SQL Server and it comes up with a single query plan.

Bottom line: use SQL Server 2008 if full-text matters to you. The other reason for doing this is that the full-text indexes now live inside the database and are managed like all other database objects, unlike earlier versions where they were stored outside the database in the filesystem. (2005 tried to help by backing up and restoring them at the same time but that was a hack compared to the current mechanism).