Ok, over the past two posts I’ve been discussing some SQL Server Components and what they’re good for.  I’ve already spent some time with the SQL Server Agent and others.  In this article I want to dig into some scenarios you can solve using Full-Text searches.

Let’s dive right in.

Scenario 1

You have a table with 30 million rows.  You want to be able to

• Query all string values within the table.
• Order the results based on “proximity” of the search (how close is the result to the requested string?)
• Minimize the amount of time required to execute queries.

All of these are problems that can be solved by creating a full-text index on your table.  Full-text indexes can be applied to any character datatypes stored in your table: CHAR, NCHAR, VARCHAR, NVARCHAR, XML, VARBINARY, TEXT, and NTEXT, but you wouldn’t still be implementing a TEXT OR NTEXT, right?  You know those data types have been replaced by VARCHAR(MAX) and NVARCHAR(MAX), right?

Of course you have! 

Now, you can create a full-text index on any number of columns (so long as they are all character based columns).  They can also support any language, whether the column contains english or not.  Just check sys.fulltext_languages for the complete list supported by your server. 

You’ve got four  basic predicates in Full-Text queries: CONTAINS, FREETEXT, CONTAINSTABLE, and FREETEXTTABLE.  The two TABLE predicates are the ones that allow you to return results with a proximity or rank on how close each row is to matching the criteria you pass in your queries.

In later posts I’ll dig into how you can set up full text indexes, how you can maintain them (you have to keep them up to date in order for them to be useful), and how you can use each of the four predicates to solve different business requirements.  For now, You simply need to know that when you’re asked to come up with a solution that will allow users to search through character data and get results quickly and reliably, you’re looking at a situation where full-text indexes and full-text queries will be useful.

Scenario 2

You’re developing an application to manage a hierarchical directory.  Each entry can have a parent entry, each entry can have one or more children entries.  When a user adds an entry, all the existing records must be searched to determine if a change is needed in their “genealogy”  (is the new entry a parent of an existing entry, or a child of an existing entry?)

• This processing doesn’t have to be computed real-time.
• Processing new records can take up to 60 seconds to complete.

I’ve actually faced this issue in a previous job.  I had to programatically build descriptions of items being shipped internationally.  The descriptions were built by performing lookups of the US Harmonized Trade Schedule.  At the time my company was on SQL 2000, and I didn’t have access to Service Broker.  If I had, this would have been one of the most common uses for Service Broker, the ability to implement Asynchronous Triggers.

Using Service Broker, I could have implemented a trigger that would have sent a message requesting the descriptions get updated later.  That way the end users could have carried on their other work, knowing the descriptions would be updated as soon as the processor could get to them. 

That’s the great strength of the Service Broker.  Client applications can issue requests, and those requests can be completed later.  No waiting for the results to be processed right then.  This is different from an Agent Job, where once you start the agent, especially if you call it from a T-SQL batch, you’re locked in until that request completes.

Of course you could simply schedule the job be run on a schedule, but what if you needed that job to start each time a certain table has a record added?  You can’t do that with SQL Agent, but you could do that with Service Broker.  Simply issue a message to the Broker, and it will be processed as soon as the processor gets to that request.

In later posts, I’ll cover how we can set up a Broker system.  I’ll even cover how to manage the service.  For now, I just want you to realize what the Service Broker can do, and what sorts of business requirements you can meet with this component.  We’ll work on how to implement those components later.

We have two more components yet to cover in this overview of SQL Server Components: Distributed Transaction Coordinator (DTC) and linked servers.  I’m willing to bet you’ve already used linked servers, though you might not realize it.  The DTC, however, might be a bit new to you.  We’ll cover those next time.

If you have any questions about when you would want to use full-text searches or Service Broker, let me know.  I’ll do my best to discuss your scenario, and offer my thoughts on the situation.  I’m here to help!