I activated fulltext search on a database and wanted to test following 3 queries:
SELECT c1 as [key] FROM [dbo].[TableFullTextSearch] WHERE [DocContent] LIKE '%uncurbed%';
SELECT c1 as [key] FROM [dbo].[TableFullTextSearch] WHERE CONTAINS([DocContent], 'uncurbed');
SELECT [key] FROM CONTAINSTABLE ([TableFullTextSearch], [DocContent], 'uncurbed');
A Clustered index was put on
[c1], a fulltext index was put on
Results were as expected, the query using LIKE generated a clustered index scan, the other two used the fulltext index.
Elapse time according to STATISTICS TIME showed following:
5895 msusing LIKE
- 0 ms using CONTAINS
- 0 ms using CONTAINSTABLE
But when investigating the query execution plan, the query cost relative to the bach was as follows:
- 23% using LIKE
- 46% using CONTAINS
- 31% using CONTAINSTABLE
Conclusion: ignore executionplan when using fulltext search?