Can you tell me how an SQL Search is performed ?

When you search WSS sites with SQL Searching, the search is performed by using a FREETEXT
statement.
Using FREETEXT allows searching by intent; all terms are ‘stemmed’ or in other words the query
looks for all inflectional forms of each query term.
For instance, if you query for “swim”, the query also returns results including “swam”; “swimming”,
and so on.
If you want more information on what is possible using FREETEXT look at SQL Books Online.