Readers will know I’m a big fan of digital photography, it’s a hobby of mine I like to relax with. For example, here’s a pic I took in downtown Birmingham (the one in Alabama, not England).

I’m also a big beliver in podcasts, I listen to many to educate myself in the .Net world. It occurred to me there’s probably some good photographic podcasts as well, and sure enough I found some good ones. I thought I’d pass along some of the one’s I’ve been listening to, for your listening delight.

The Candid Frame – The host interviews photographers to find out how they got started, their techniques, etc.

Photocast Network – This is a central site for many shows including the ones I’ve listed above. They also have a few other shows I want to check out but haven’t had the opportunity to as of yet.

You should definitely check out the “Focus Ring” episodes. These are shows where the hosts from several of the network’s shows get together on a single podcast to discuss various topics. By far these have been my favorite episodes so far.

OK, so you have this spiffy catalog, and you’ve populated it with full text searches for your favorite tables. Now you’d like to actually use those index from within your SQL. There are four new commands you can use in SQL to get to your data. All are used as part of the where clause, and have similar syntax but different results.

The first is the one you’ll probably use the most, it’s the contains command. You simply pass in the column name and what you want to search for.

For fts_column you can use the name of one of the columns that was indexed, or you can use * (and asterisk) to search in all of the columns that were full text search indexed. In the single quotes you put in the word or phrase you want to look for.

Contains searchs for an exact match. It either finds it or it doesn’t, and it has to be an independent word. For example, if your text field contained “I love the Mythbusters every week.” and you searched for ‘Mythbuster’, would NOT return a match.

If you want your text searching to be a little more open minded, use the freetext command instead. The syntax is identical to contains, including the ability to use an asterisk.

In this case, however, a search of our afore mentioned text field for ‘Mythbuster’ would return a match, as freetext understands that Mythbuster and Mythbusters are essentially the same word.

In your application, you might consider using a check box that says “exact match”. For exact match queires use the contains keyword, when the user does not check you can use the freetext command.

It’s also possible to return a list of results that are sorted by a rank. The rank indicates the strength of the match to the search phrase passed in. To get a list of ranks, use either the containstable or freetexttable commands. Their syntax is like their cousins, as is the method it uses for searching (containstable is exact, freetexttable is more liberal). The only addition is the first parameter must be the name of the table, then comes the column name and search condition.

Instead of rows, what is returned are two columns: key and rank. The rank is a relative score from 0 to 1000 that indicates the strength of the match. A higher value means it’s a better match.

The key is the primary key from the table you’re searching. You can then use this key to pull back the data from the main table. Let’s do a simple example: you want an exact match for all employees who live in Alabama. Unfortunately the DBA who created the table had just come off a three day drinking binge, and instead of separate street / city / state fields, just created a big text field called emp_address.

Yesterday I introduced you to full text searching, and covered the basics on creating catalogs to hold your full text indexes. A full text search index is a little different than a regular index. First, each table can only have one full text search index created for it. Next, the create syntax is slightly different. OK, in fact it’s a lot different. Let’s take a look:

The first thing is also the most obvious, you need to supply the name of the table in the first line. Note we’re not supplying a name for the full text search index. Since there’s only one per table, SQL Server takes care of creating the full text search index name for us.

Next we need to supply the name of the column or columns we want indexed. These can be any sort of text field. Just list them one after another, separated by commas.

The next item is also required, and sort of tricky. Each row in the table you are doing full text searching on must have a unique index. It makes sense when you think about it, for the text search to be efficient it must be able to quickly move to the row with the word you’re hunting for, and the way to do that is via the unique index.

So for this parameter you’ll need to supply a unique index name for “my_tables_unique_index_name”. Keep in mind this is not the name of the columns from the table. Instead this is the name of a “normal” index (not a full text search index) that is unique for the table.

The “on” parameter is optional, you only need it if you set up multiple catalogs and don’t have a default. If you omit it, it will simply put the new index in the default catalog.

Next you will need to tell SQL Server how often to update the index. You do this through the with change_tracking parameter. OFF turns it off entirely, no updates will be done until you issue a rebuild via the alter syntax I’ll cover momentarily. You might want to use OFF when you have a table that gets updated very rarely.

AUTO, on the other hand is for when you have a table that gets updated frequently. It will update the full text search index when the associated table is updated. The final option, MANUAL will flag changes to the underlying table, but it won’t update the full text search index until you tell it to.

The final parameter, no population, only applies when you use OFF. It tells SQL Server not to populate the index when it’s created. If you omit it, or use AUTO or MANUAL, SQL Server will populate the full text search index when the index is created.

OK, so you’ve got this index created and need to change it, or perhaps you need to work with one that’s already in existence. For this there’s the alter command:

alter fulltext index on my_table_name_here
parameters here

There’s quite a few parameters you can pass, so let’s look at them individually. Just know that when you see them below, they should go where you see “parameters here” above.

set change_tracking {off | auto | manual} – This works the same as with the create command, it lets you change the tracking mode.

disable – Disables the full text search index, it’s not used for searching nor is it updated. However the data is left intact, should you want to turn it back on.

enable – Enables the full text search index after a disable.

add ( column ) – Adds the passed in column to the full text search index.

drop ( column ) – Removes the passed in column from the full text search index.

start full population –This rebuilds the index from the ground up.

start incremental population –This will update the index since the last time it was updated. Note you must have a timestamp column on your table for this to work.

start update population –Remember a moment ago when I talked about the change_tracking manual option? Well this command is how you update an index with manual change tracking.

And finally, you may decide one day you no longer need the full text search index. Since the readers of this blog are the smartest, most intelligent readers on the planet you’ve already figured out we’ll need to use a variant of the drop command:

drop fulltext index on my_table_name_here

And there you go, you now know how to create, change, or remove a full text search index. Now there’s one more piece, you need to know how to use them from within your SQL. But we’ll save that for tomorrow.

One of the coolest features of SQL Server 2005 is the ease with which you can implement full text searching. True, it was available in previous versions but 2005 makes it very easy to implement and use.

Full Text Search is an offshoot of the Microsoft Index Server technology. It’s what you could call an “add-on”. By default it’s enabled for every database you create in 2005.

But just having it turned on is not enough, now you have to create a catalog to hold the data for your full text data. The catalog is a separate file from your database, and holds all the key words it finds. The syntax to create a catalog is pretty simple:

The ‘in path’ is optional, if you omit it your catalog is created in the same place as the data. For small databases this is fine, for large ones you might actually want to store the catalog on a separate hard disk in order to get a performance boost.

The ‘as default’ clause says this catalog will be the default one used for new full text search indexes, or for searching existing ones. Most times you’ll probably only need one catalog for a database, so you can add this and forget it.

Once you have a catalog created, you may need to tweak it. There’s not a lot of tweaking you can do, just three ways you can alter it, and all are implemented via the alter command.

alter fulltext catalog my_catalog_name_here rebuild

alter fulltext catalog my_catalog_name_here reorganize

alter fulltext catalog my_catalog_name_here as default

The first command, rebuild does just what it says. Your old catalog goes to the great bit bucket in the sky (i.e. it’s deleted) and SQL Server will recreate all of your full text search indexes. And it should be obvious, but remember during this time your full text search will not be available.

Reorganize is something like doing a disk defrag, it cleans up and reorganizes your full text search indexes. While it may not be as efficient as doing a complete rebuild, it does have the advantage of not taking the catalog offline while it does it’s work.

Finally ‘as default’ simply makes the catalog the default, in case you either forgot or were distracted by Mike Rowe doing something nauseating on “Dirty Jobs” (http://www.discovery.com/dirtyjobs) .

OK, you now have a catalog. But the catalog is simply a space to hold your full text search indexes, and those we’ll create in the next post.

In doing some reading I ran across a handy collection called the NameValueCollection. This collection, which resides in the System.Collections.Specialized namespace, allows you to use either a string or an integer index for the key. Further, it allows you to store more than one string value in a key.

Let’s start the code example by creating a simple Console application. I added using references to System.Collections and System.Collections.Specialized namespaces at the top. As a final bit of housekeeping, make sure to add a Console.ReadLine() as the last line of our code, so the console will wait on us to hit the enter key after we read the results. (If you don’t, the program will run so fast you won’t be able to appreciate your fine work.)

Now I’m going to load some data into a new collection called myCollection. For the data, I’ll use a website owner and the website or sites they own.

Next, I’d like to get some data back out. I mentioned you could cycle through the collection using an integer index, so let’s see how that’s done:

Console.WriteLine(“Key / Value Pairs by Integer Index”);

for (int i = 0; i < myCollection.Count; i++)

{

Console.WriteLine(i.ToString() + ” “

+ myCollection.GetKey(i) + “: “

+ myCollection.Get(i));

}

In the above output you can see how I use the GetKey and Get methods to retrieve the key name and value for that key using the loop’s index. Note that when multiple values are associated with a single key, they are returned as a list of comma separated values.

You can also use foreach logic to cycle through the collection. Here I am using the AllKeys property of our collection to get the list of keys. I can then print the key, and also use the key as the indexer into my collection as you can see below.

Console.WriteLine();

Console.WriteLine(“Keys / Value Pairs via AllKeys Collection”);

foreach (string myKey in myCollection.AllKeys)

{

Console.WriteLine(myKey + “: “ + myCollection[myKey]);

}

Now I, what? Yes, you in the back row, what was your question? Ah, you say lists of comma separated values are OK, but you want to be able to access individual values? Fortunately some nested looping and the GetValues method will satisfy you demanding types.

Console.WriteLine();

Console.WriteLine(“Keys / Individual Values”);

foreach (string myKey in myCollection.AllKeys)

{

foreach (string myValue in myCollection.GetValues(myKey))

{

Console.WriteLine(myKey + “: “ + myValue);

}

}

This also works great if your data has commas within it. Let’s add two lines back at the top of the program to the collection.

myCollection.Add(“CommaTest”, “Here is a , in a string”);

myCollection.Add(“CommaTest”, “Here is another , in a string”);

Now run the application again, and lets look at the results.

As you can see in the last area “Keys / Individual Values” the GetValues method correctly determined that the commas I had embedded were part of the data and not a delimiter between values.

Whenever you need a good string collection that has the ability to tie multiple values to a single key, the NameValueCollection would be a good class to take a look at.

Sorry for missing my usual Friday post, I was having ISP issues (which are still unresolved, but I’ve done a workaround for now).

This weekend I’ll be participating in something very geeky, it’s called Field Day. Each year on the fourth full weekend in June amateur radio operators (you may have heard them called “Hams”) get together to practice their emergency response preparedness, fellowship and have a good time.

The idea behind Field Day is for the hams in a community to gather at a single location, setup radios, equipment, run off of emergency power, and generally practice what we would do in case of an emergency. At the same time my local clubs are gathered, other clubs will be gathering in their communities as well. We’ll then get on the air and communicate with each other, exchanging brief messages similar to what we would do in the event of a real emergency.

This preparedness has already paid off, several times. In the days after 9/11 amateur radio was the chief form of communication. More recently, the hurricanes that devastated Louisiana, Mississippi, and parts of Alabama provided a wide scale communications effort. For months it was amateur radio that provided the communications links between emergency responders as well as relief agencies like the Red Cross and United Way.

In this day and age you might be thinking “is amateur radio still around? I thought cell phones and the internet got rid of it?” Not so. Most amateur radio equipment can be setup with a minimum of requirements. A decent 12 volt battery, the radio, and some wire in a tree and the radio operator is in business. The internet doesn’t work so well without power, and the cell phones don’t seem to work to well after a hurricane knocks the cell towers onto the ground.

Community education is the other component to Field Day. Often we gather in public places like parks so that we can be seen by folks driving or walking by. This year my clubs, the Shelby County Amateur Radio Club and the Birmingham Amateur Radio Club are joining forces and will be at Oak Mountain State Park near the fishing lake. I’m sure in your community hams will be gathering too.

If you happen to be out and about and see a bunch of guys bent over radios, wander up and say hello. They’ll be glad to show you around, maybe even let you get on the air. There’s nothing quite like the thrill of picking up a microphone and realizing the guy you are talking to is on the other side of the planet, then realizing the only thing making it happen is the little box in front of you and a piece of wire strung up in a tree! Who needs the internet anyway?

So far I haven’t had a lot of success getting USB devices working under VirtualBox with XP as the guest. Perhaps it has something to do with Vista being my host?

I’ve been testing using some USB keys, and while VirtualBox seems to know they are present, the message never seems to make it into my guest OS of XP. I intend to keep working with it, USB support would be one of the most compelling things to make me start using VirtualBox as my primary virtualization platform. However, as of right now USB support doesn’t seem quite up to prime time.