Post reply

Full Text Indexing

  • September 18, 2008 at 8:34 am

    #191035

    I have a column that is full-text indexed.

    If I do a query on the column using LIKE %% instead of CONTAINS will SQL Server use the full-text index?

    SELECT *

    FROM SomeTable WITH (NOLOCK)

    WHERE CONTAINS([Description], 'GO')

    SELECT *

    FROM SomeTable WITH (NOLOCK)

    WHERE [Description] LIKE '%GO%'

  • September 22, 2008 at 8:20 am

    #875010

    Does anyone know the answer?

  • September 23, 2008 at 12:20 am

    #875420

    As far is i know you have to use the CONTAINS method to make usage of the fulltext search.

    So the answer is NO

    But ofcourse there is a very simple way to test it: just run the 2 queries and compare the execution plans 😉

  • September 23, 2008 at 7:04 am

    #875613

    If I do a query on the column using LIKE %% instead of CONTAINS will SQL Server use the full-text index?

    Short answer: NO

    Long answer: The SQL engine uses various words such as IN, LIKE, CONTAINS, etc. to determine logic. If LIKE always performed a full-text query then you could never perform a wildcard match using LIKE.

    Note: CONTAINS is a semi-portable full-text operand as it also exists in Oracle.

    [font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

  • September 24, 2008 at 9:30 am

    #876462

    Thanks so much for your help!!

  • September 25, 2008 at 5:43 am

    #876902

    By using the contains you can search for words and phrases but with like you can search for characters containing in the word as well.

    like %a% will result all the records containing a like apple, aeroplane etc.

    whereas contains will search for 'apple' it will not return the result like 'Like' keyword.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply