Okay, so I have been playing around with Full Text Search in SQL Server 2005.
I've worked with it from an administrator's point of view, which is certainly very
interesting, but I haven't really gotten to play around with it from a development
perspective. Basically, I stuck an index on the body column for this blog, and
did a simple FTS query.
Now, to do a query with the functionality of FTS you actually end up using some
new keywords:
...
WHERE CONTAINS (body,
@q) ... WHERE FREETEXT (body,
@q) ...
At first I worked with CONTAINS, which takes the same parameters for the most part.
While it worked, it would struggle with multiple words in a query. Second I tried
FREETEXT, which is more friendly to multiple search keywords.
This takes care of a simple search on the body of the blog article, but what about
if I'm searching for something in a title? There are lots of ways to do it, some much
better than others. Here is the simplest query I could come up with. It is neither
designed to be fast, nor as accurate is possible. It is designed for simplicity. It
could very easily be reworked, and if anyone has some ideas as to what could work,
let me know! I'd like to see what other people would do.
CREATE PROCEDURE [dbo].[DoSearch]
@q nvarchar(4000) AS BEGIN SET NOCOUNT ON; SELECT [PostID],
[Title], [Body] AS PostSnippet, [EntryDate] AS PostDate FROM [dbo].[sb_posts] WHERE FREETEXT (body,
@q) OR [Title] LIKE '%' +
@q + '%' ; END
Thoughts? Comments? Questions? Give'r!
