When is it usefull?

As you can tell by the name, this one is all about text and search in text. So if you are working on a database that is all about financial transactions and numbers, it isn't of much use.

Now if you have some tables that contain large text fields (varchar(max) or ntext), it becomes a really interesting option.

And if you do a lot of searches on the text, especially advanced filtering with multiple keywords? Then by all means, do keep reading this one!

Is Full Text Search Installed?

When you run a Default Installation of SQL Server, Full Text Search is not selected. To check if this feature was installed on the server you are working on, you can use this query:

select fulltextserviceproperty('IsFullTextInstalled')

If this one returns 1, Full Text Search is installed on your SQL Server and you are goed to go. If not, you need to run the installation again to add the feature.

For times sake, I skip the installation procedure since it really is selfexplanatory.

Create a FullText Catalog

The data for your Full Text Search must be stored somewhere. So the first step is to create a Catalog for that. In Management Studio you can expand the Database, then the Storage Folder and right-click the Full Text Catalogs Folder. Here you can add a new Catalog using a wizard.

But it really isn't that hard and you can do it easily using a Query:

create fulltext catalog FullTextCatalog as default

In this case I used the name FullTextCatalog, but you can use any name you like. Just pick one that makes sense and is easy to remember because you will need that one in the next step.

Enable Full Text Search for a Table

As with most things great and beautiful, it will cost you something. In this case, that is disk space. SQL Server must build the indexes for the Full Text Search and your database will grow. That's why it is not enabled on all tables and fields by Default.

You can build the Full Text Search on any table (one by one) but only for fields that are text related. And even then, think of your disk space and don't add every text related field. Just the ones that you plan to use for your search operations.

To build the Full Text Index for a Table, right-click the Table and expand the Full Text Index option where you can define this. But again, you can also do this using a Query:

create fulltext index on dbo.BacoDiscussions
(
  [Subject] Language 1033,
  Body Language 1033
)
key index PK_BacoDiscussions
  on FullTextCatalog

A few things with this one. As you can see need to specify the Primary Key to be used. When you do this using the right-click menu, the Primary Key will already be shown and selected. So if you don't know how to find out the name, just make it easy on yourself and choose this option. If you want to go all hardcore, use sp_Help [TableName] to find out what the Primary Key is.

You also need to tell on which Catalog SQL Server should built the Indexes. That's the name you used in the first step.

And inside the Full Text Index I specified two columns from the table that I will be using: Subject and Body. So, what is the number 1033 at the end?

Well, that is because Full Text Search can do some pretty cool things based on the language used. So if the text is your database is mainly in English, use 1033. Is it mainly in Dutch? Then use 1043. For a full list of available languages, click here for the documentation.

Now with all this set up, let's see what you can do with it!

The Power of Full Text Search

Let's start easy and just check if all works as expected using the Contains option. For this example I used an Exact Synergy Database where I enabled Full Text Search on the Workflow Table called Absences.

A Simple Contains

To check if all works as expected, here is one that uses a Contains:

select distinct(Description)
from Absences
where contains(description, 'start')

Advanced Contains

Yeah, yeah. You could do the previous also without the Full Text Search using Description like '%start%'. But what if you would want to search on 2 different keywords? With Contains you could do it like this:

select distinct(Description)
from Absences
where contains(description, 'start or stop')

And when your Full Text Index on a Table has multiple columns, wouldn't it be great if you could just as easily search in all these columns in one go? Well, don't specify a column name in the Contains and use a * instead.

select distinct(Description)
from Absences
where contains(*, 'start or stop')

Aha, this is getting to pretty awesome already. But wait! There is more!

How about searching for records that contain a specific word, but only if it is used in combination with another word nearby? For example, I want to find all records which contain the word Invoice, but only those records where Invoice is mentioned in combination with Website. Something like "Here is the Invoice for your Website". Well, here you go:

select distinct(Description)
from Absences
where contains(*, 'invoice near website')

Not yet convinced of the power of the Full Text Search? Let's take it up another notch using freetext!

Freetext Searches

Freetext and Contains are pretty similar at first glance. But a noticable difference is that Freetext uses something called Stemming. That means it also looks for deriviations of a word. So if you search for Invoice, it can also show where Invoicing or Invoiced are used. Or when you search for Mouse, it can show results for Mice as well.

The basic syntax is similar to the Contains:

select distinct(Description)
from Absences
where freetext(Description, 'Start')

And if you use multiple keywords, it will automatically use the Or function:

select distinct(Description)
from Absences
where freetext(Description, 'Start Stop Finish')

And finally the FreeTextTable

The FreeTextTable is kinda like a shorthand version of the basic Select Statement using freetext. It has 3 Parameters: Table, Columns and Keywords. So the previous Query could be something like this:

select *
from freetexttable(Absences, *, 'Start Stop Finish')

But wait! When you run this one, it doesn't return much. Just the Key and a Rank. That is not very impressive, is it?

But yes, it really is. You see, the ranking is done by the Full Text Search Engine. So the Record which contains all the keywords and with the most occurances is designated as the best match. Kindalike the Google Search Engine works!

And it all comes together now

The FreeTextTable function returns a Table with 2 columns. That doesn't look like much. But since it is a Table, you can join it with other tables. Just like you would with regular Tables.

So with the previous example, we could do a search on the real Absences Table, joined with the FreeTextTable. And then use the Rank from the FreeTextTable for the correct order or for filtering:

select top 10
HID, Description, ftt.Rank
from absences a
inner join freetexttable(Absences, *, 'Invoice') ftt on a.ID = ftt.[key]
order by ftt.Rank desc

See what you did there?

Do you see that? You just build a Google Search-like engine right there in your SQL Server Database. And with just a few lines of Code. You should be proud of yourself!

Of course, there is much more to it than these hard-coded examples. You should make it more dynamic by passing in parameters which can be used instead of the plain text keywords from this example. There are several ways to do that, but that's for one of my next Posts.

That's it for Today

I hope this introduction to the Full Text Search has given you inspiration on how you can use this. Especially if you have one or more Tables where text search is important, it can be really useful. So if you have something like a Forum database, or a Service Management Tool with lots of Tickets? This is something you really want to check out!

You can leave a comment or send a question to andre@andrespeek.com. Let me know if you got inspired and stay safe and healthy always!