How hard is it to incorporate full text search with SQL Server?

Question

I am building a C#/ASP.NET app with an SQL backend. I am on deadline and finishing up my pages, out of left field one of my designers incorporated a full text search on one of my pages. My "searches" up until this point have been filters, being able to narrow a result set by certain factors and column values.

Being that I'm on deadline (you know 3 hours sleep a night, at the point where I am looking like something the cat ate and threw up), I was expecting this page to be very similar to be others and I'm trying to decide whether or not to make a stink. I have never done a full text search on a page before.... is this a mountain to climb or is there a simple solution?

thank you.

Answer 1

First off, you need to enabled Full text Searching indexing on the production servers, so if thats not in scope, your not going to want to go with this.

However, if that's already ready to go, full text searching is relatively simple.

T-SQL has 4 predicates used for full text search:

• FREETEXT
• FREETEXTTABLE
• CONTAINS
• CONTAINSTABLE

FREETEXT is the simplest, and can be done like this:

SELECT UserName
FROM Tbl_Users
WHERE FREETEXT (UserName, 'bob' )

Results:

JimBob
Little Bobby Tables

FREETEXTTABLE works the same as FreeTEXT, except it returns the results as a table.

The real power of T-SQL's full text search comes from the CONTAINS (and CONTAINSTABLE) predicate...This one is huge, so I'll just paste its usage in:

CONTAINS
    ( { column | * } , '< contains_search_condition >' 
    ) 

< contains_search_condition > ::= 
        { < simple_term > 
        | < prefix_term > 
        | < generation_term > 
        | < proximity_term > 
        | < weighted_term > 
        } 
        | { ( < contains_search_condition > ) 
        { AND | AND NOT | OR } < contains_search_condition > [ ...n ] 
        } 

< simple_term > ::= 
    word | " phrase "

< prefix term > ::= 
    { "word * " | "phrase * " }

< generation_term > ::= 
    FORMSOF ( INFLECTIONAL , < simple_term > [ ,...n ] ) 

< proximity_term > ::= 
    { < simple_term > | < prefix_term > } 
    { { NEAR | ~ } { < simple_term > | < prefix_term > } } [ ...n ] 

< weighted_term > ::= 
    ISABOUT 
        ( { { 
                < simple_term > 
                | < prefix_term > 
                | < generation_term > 
                | < proximity_term > 
                } 
            [ WEIGHT ( weight_value ) ] 
            } [ ,...n ] 
        ) 

This means you can write queries such as:

SELECT UserName
FROM Tbl_Users
WHERE CONTAINS(UserName, '"little*" NEAR tables')

Results:

Little Bobby Tables

Good luck :)

Answer 2

Full text search in SQL Server is really easy, a bit of configuration and a slight tweak on the queryside and you are good to go! I have done it for clients in under 20 minutes before, being familiar with the process

Here is the 2008 MSDN article, links go out to the 2005 versions from there