<p>I have an MS SQL database and have a <code>varchar</code> field that I would like to do queries like  <code>where name like '%searchTerm%'</code>.  But right now it is too slow, even with SQL enterprise's full text indexing.  </p>

<p>Can someone explain how <code>Lucene .Net</code> might help my situation?   How does the indexer work? How do queries work?</p>

<p>What is done for me, and what do I have to do?</p>

This
article
(strangely enough it's on the top of the Google search results :) has a fairly good description of how the Lucene search could be optimised.
Properly configured Lucene should easily beat SQL (pre 2005) full-text indexing search. If you on MS SQL 2005 and your search performance is still too slow you might consider checking your DB setup.