Good Reads

Thanks to Tekzilla, I found an interesting website called Good Reads. Good Reads allows you to quickly and easily enter the books you own into the site so others can see what you are reading, want to read, etc. It also makes it handy when you are a book lover like me, and have a hard time remembering which books you own and don’t own.

You can organize your books into bookshelves. It comes with three built in shelves: read, currently-reading, and to-read. You can create your own shelves, and a book can be on multiple shelves. I’ve organized my bookshelves by topic, so far I have shelves like sql-server, powershell, c-sharp, f-sharp, to name a few. You can find my list at:

I like this concept, in the past I’ve always used an on-line bookseller when suggesting a book. Now this gives me a way of sharing my book list without looking like I’m a marketer. Another valuable feature, the site lets you create reviews for each book and give it a one to five star rating.

Right now I have added my most recent books. The ones on the bookshelf in my office in easy reach (or that have spilled onto a stack on my desk due to lack of bookshelf space) that get used a lot. I love books as a learning medium, so wind up buying a lot. Eventually I will add more of my current computer book collection. Also right now I’m focusing on my computer books, but will eventually add some of my recreational, self improvement, and business books.

Check out Good Reads and my list when you can. It’s free, and pretty quick to add your book list to. If you create a list feel free to leave a comment with a link so we can see what you are reading!

Productivity and Toilet Seats

Recently I bought a new toilet seat. It’s freed up my time to do more productive things. Now, before you hit the speed dial to have the old Arcane Coder hauled off in a pretty white jacket with straps and shiny buckles on the back, bear with me.

You average toilet seat takes about 4 seconds to put down, assuming you do it gently and don’t slam it. I recently spent a few extra dollars to get a spring loaded toilet seat. All I have to do is nudge it and it gently lowers itself to the lowered position. 1 second of my time to nudge, versus 4 seconds. Not to get too personal, but we’ll say I need to lower the seat 4 times a day on average. That means I’ve saved 12 seconds a day. Doesn’t sound like much, but count that up over a year, it winds up saving me 73 minutes a year. What can I do with that 73 minutes a year I’m not spending on doing something as mundane as lowering toilet seat? Play XBox with my kids, read a book, listen to a podcast, or even write blog posts.

I’m always looking for ways to save time, as time is the one thing we all have the same amount of. Do you have a laptop you regularly take between home and work? Get a second power supply and mouse, leave one at home and one at work. You save a lot of time and effort by just unhooking your laptop and dropping it in your bag versus having to fiddle with all the cords, plugging and unplugging.

My Zune is another productivity gadget. Using it I can keep up my education via podcasts while doing things like driving, cutting the grass, or even shopping. Likewise my PDA keeps a lot of information at my fingertips, which saves me from running back and forth to my PC several times a day.

Time is the one equalizer, the one thing we all have the same amount of. In my mind then, any sort of gadget which gives us back time to do other things is a worthwhile investment.

So what kind of tools do you use to gain productive time in your life?

TechEd 2008 Wrap Up

Whew. I’m finally home from TechEd 2008. It was an incredible week. First off, it was quite lucky for me in terms of swag. I came home with: XBox 360 Elite; Office 2007 Ultimate; Book on Sharepoint Web Part Programming; 2 games for the XBox; 8 Gig Zune; and more extra large t-shirts than I could carry.

I also got to meet a lot of great people, and find some speakers to add to my favorites list. Having heard them on Dot Net Rocks I knew Kimberly Trip and Paul Randall were good speakers, and just as friendly off stage as on. Matthew Roche, Buck Woody, Dan Jones, Donald Farmer, Peter Ward, Allen White, Maciej Pilecki, well I could go on with the list of great speakers but these were among the ones that stand out.

The InBetween weekend event was outstanding. The user groups really did a great job in a short amount of time putting together an awesome weekend. In addition to presenting my own session I also enjoyed attending ones by Barry Ralston, Andy Warren, Brian Knight, well there I go again. It is well worth staying over, or arriving early and getting in on the weekend event if you are going to TechEd.

By far though, the most important thing I came home with was knowledge. I learned an incredible amount. That knowledge will make me more valuable to my employer, who invested their money to send me. It will also make me more valuable to my coworkers, as I share the knowledge and have more answers to their questions. I’m also more valuable to the community, as I go back to user groups and share what I’ve learned there in presentations.

All in all I’d say TechEd was a great investment of time, money, and brain power.

Interview with Andy Warren on his Birmingham Visit

After the SQL Saturday event Inbetween the TechEds I had a moment to interview SQL Server guru Andy Warren about his upcoming trip to Birmingham Alabama. Andy will be speaking at the Steel City SQL group on June 17th and holding an all day class on on Performance Tuning on June 18th. You can find my interview with him, in MP3 format, here:

Andy Warren Interview

Note the audio is raw and uncut, but will give you all of the information you need.

The TechEd Day 1 Report

Wow, what a day. It was go go go all day long. I even attended sessions during lunch, so as to get every last bit of knowledge I could. I went to a lot of good sessions today, but by far my favorite was the session Kimberly Tripp did on Indexing Strategies. She was able to take a boring subject like indexing and not only make it understandable but entertaining. When you find yourself excited about the thought of spending time looking at your indexes, you know you’ve been trained by a master!

After the session she stayed and answered questions, and again from the floor she and her husband Paul were there again sharing their brain power. I enjoyed the session so much I’ve just spent 20 minutes juggling my schedule for the rest of the week so I can squeeze in another session they are doing later in the week. If you ever get a chance to see them speak, I would highly recommend it. If you cannot in person, at least check out their blogs at SQL Skills. She also did a series of webcasts, you can find links at

Enough fun for today, need some rest for tomorrow is another day of geeking out.

I went to the Inbetween TechEd Conference and All I Got Was This Lousy XBox 360 Elite!

As you know from my previous entries, I am in Orlando attending TechEd, at the IT Pro week. In the weekend between the Dev and Pro conferences Microsoft turned over the Orange County Convention Center to the Florida User Groups. Spearheaded by the ONETUG group, this event was made possible through the cooperation of many user groups. I attended the SQL Saturday sessions that were held on both Saturday and Sunday, and they even let me hog the stage for an hour on Saturday and give my presentation on Full Text Searching in SQL Server 2005 and 2008.

I had a blast, and met a lot of great people. I have to admit I was surprised at how many folks were there just for the Inbetween conference and not TechEd. Out of the 45 folks who were in my session, all but 2 said they were local folks there for just the Inbetween conference.

As you can tell from the title of this post, I did indeed win something. Bear with me, as it’s a great story. At the Saturday night party they were giving away 3 XBoxes, two of which were randomly drawn for. Well the young lady who won one of them did not wish to keep it for whatever reason, and asked if she could draw another name, which she did.

Well the guy who won that night had his name drawn during Sunday’s giveaway of 7 XBoxes. He came up and told the story, and said he wanted to do the same thing, even though no one would have said a word had he decided to keep it. Well he drew and it was my name he pulled out of the box! I wish I could remember his name so I could say thanks again.

I’ve always wanted an XBox, but could never quite justify the expenditure. Now I have a new toy, and my kids are very excited over the prospect of playing some games with daddy. I think this will be a great gizmo for some daddy/kids together time.

Let me get serious for one moment, and give a big thanks to all the groups who came together at the last minute, and to Joe Healy, Florida Developer Evangelist, for giving the user groups the chance to show what they can do. It seems almost fashionable to bash Microsoft these days, but how many other companies would bear the expense of something as costly as the convention center and give it away to the user community?

Thanks to all for a great time, I hope the Inbetween conference becomes a regular feature at all future TechEd.

Presenting SQL Server 2005 2008 Full Text Searching at the TechEd 2008 InBetween conference

On June 7th, 2008 I am presenting “Getting Started with SQL Server 2005/2008” at the InBetween SQL Saturday conference that is taking place between the two weeks of Microsoft TechEd. This post will have all the links relevant to my talk.

Detailed descriptive material can be found on my blog,, with links to specific posts below.

First off, the slides and sample code can be located at the Code Gallery site I setup specifically for Full Text Searching with SQL Server:

Look on the downloads page to see various projects around SQL Server Full Text Searching. I’ve created one “release” for each of the projects around FTS. Be sure to look on the right side at the various releases in order to see the various projects.

Next, you can get started with the basics by reading these entries on my blog:

Lesson 0 – Getting the Bits to do Full Text Searching in SQL Server 2005
Lesson 1 – The Catalog
Lesson 2 – The Indexes
Lesson 3 – Using SQL
Lesson 4 – Valid Data Types
Lesson 5 – Advanced Searching

After that you’ll be ready for some advanced topics.

Can you hear me now? Checking to see if FTS is installed.
Exploring SQL Servers FullTextCatalogProperty Function
Using the ObjectPropertyEx Function
Using FORMSOF in SQL Server Full Text Searching
Creating Custom Thesaurus Entries in SQL Server 2005 and 2008 Full Text Search
Creating and Customizing Noise Words in SQL Server 2005 Full Text Search
Creating and Customizing Noise Words / StopWords in SQL Server 2008 Full Text Search
Advanced Queries for Using SQL Server 2008 Full Text Search StopWords / StopLists

Advanced Queries for Using SQL Server 2008 Full Text Search StopWords / StopLists

Yesterday I covered the basics around StopWords/StopLists in SQL Server 2008. Today we’ll look at some advanced queries you can use to get more information back about FullText Indexes, StopWords, and StopLists.

The first query simply returns a list of all of the catalogs in the system.

— Get current list of full text catalogs

select [name] as CatalogName

    , path

    , is_default

  from sys.fulltext_catalogs

 order by [name];


The next query returns a list of all the StopLists.

 — Get the list of StopLists

 select stoplist_id

      , name

   from sys.fulltext_stoplists;


This query returns a list of StopWords in the database. Note the linking to get the associated StopList name and language.

 — Get list of StopWords

 select as StopListName

      , sw.stopword as StopWord

      , lg.alias as LanguageAlias

      ,  as LanguageName

      , lg.lcid  as LanguageLCID

   from sys.fulltext_stopwords sw

   join sys.fulltext_stoplists sl

    on sl.stoplist_id = sw.stoplist_id

   join master.sys.syslanguages lg

    on lg.lcid = sw.language_id;


This next query gets a list of all of the stopwords that ship with SQL Server 2008. This is a nice improvement, you can not do this in SQL Server 2005.

— Get a list of the System provided stopwords  

select ssw.stopword


  from sys.fulltext_system_stopwords ssw

  join sys.fulltext_languages slg

    on slg.lcid = ssw.language_id;


My next query returns a list of all the Full Text Indexes in the database.

— List full text indexes

select as CatalogName

    , as TableName

    , as UniqueIndexName

    , case i.is_enabled

        when 1 then ‘Enabled’

        else ‘Not Enabled’

       end as IsEnabled

    , i.change_tracking_state_desc

    , as StopListName

  from sys.fulltext_indexes i

  join sys.fulltext_catalogs c

    on i.fulltext_catalog_id = c.fulltext_catalog_id

  join sys.tables t

    on i.object_id = t.object_id

  join sys.indexes idx

    on i.unique_index_id = idx.index_id

       and i.object_id = idx.object_id

  left join sys.fulltext_stoplists sl

    on sl.stoplist_id = i.stoplist_id



This query returns a list of all the document types SQL Server 2008 understands when they are placed in a varbinary(max) field.

— List all of the document types SQL Server 2008 will understand in varbinary(max) field

select document_type

    , path

    , [version]

    , manufacturer

  from sys.fulltext_document_types;


If your full text performance begins to suffer over time, you might want to check and see how many fragments exist. If you have multiple closed fragments, you should consider doing a REORGANIZE on the index (using alter fulltext index). This query will tell you how many fragments exist for your full text index.

— See how many fragments exist for each full text index.

— If multiple closed fragments exist for a table do a REORGANIZE to help performance

select as TableName

    , f.data_size

    , f.row_count

    , case f.status

        when 0 then ‘Newly created and not yet used’

        when 1 then ‘Being used for insert’

        when 4 then ‘Closed ready for query’

        when 6 then ‘Being used for merge inpurt and ready for query’

        when 8 then ‘Marked for deletion. Will not be used for query and merge source’

        else ‘Unknown status code’


  from sys.fulltext_index_fragments f

  join sys.tables t on f.table_id = t.object_id;


There you go, a handful of powerful queries to help you query and maintain the state of your full text indexes.

Creating and Customizing Noise Words / StopWords in SQL Server 2008 Full Text Search

While SQL Server 2005 had Noise Words for its full text search, SQL Server 2008 has moved to StopWords. The good news is the change is much deeper than just a rebranding. I say good news, because with the change comes a lot more flexibility and functionality.

There are actually two new tools introduced with SQL Server 2008, StopWords and StopList. A StopList acts as a named container for a group of StopWords. You can then associate a StopList with one or more tables. This is a great enhancement over Noise Words, which applied to the entire server. Now you can associate a group of StopWords, in a StopList, with specific tables without affecting the rest of the tables on the database or server.

First, let’s run a query to demonstrate. I’ll be doing all my testing against the AdventureWorks2008 database. If you don’t have it, you can download it at the CodePlex site. I’ve also used the standard Create Catalog command to create a full text catalog named AdvWorksCatalog. I then put a full text index on the Production.ProductDescription field. That done, here is a query I’ve used for examples in the past.

–Demo 1 – Query the Index

use AdventureWorks2008;


select [Name], ProductNumber, [Description]

from [Production].[Product] p

   , [Production].[ProductDescription] pd

   , [Production].[ProductModelProductDescriptionCulture] pmpdc

where p.ProductModelID = pmpdc.ProductModelID

  and pmpdc.ProductDescriptionID = pd.ProductDescriptionID

  and CONTAINS(pd.[Description], ‘shifting’)


Now we need to create the stoplist. Here’s we’ll use the first of the new SQL Server 2008 commands, create fulltext stoplist.

— Demo 2 – Create Stoplist

use AdventureWorks2008;


create fulltext stoplist ArcanesStopList;

The StopList will act as a holder for a specific set of words that we want to ignore. We refer to that group of words by the name we gave it, ArcanesStopList. Now we need to add some words to the list. Here are two ways to do so, both use the alter fulltext stoplist command.

— Demo 3

— Add StopWord


use AdventureWorks2008;


alter fulltext stoplist ArcanesStopList

  add ‘shifting’ language 1033;


alter fulltext stoplist ArcanesStopList

  add ‘light’ language ‘English’;


The command is straight forward, use the alter fulltext stoplist, give it the name of the list you want to add a word to. Then comes the word add, followed by the word you want to add. Next you have to specify the language. There are two ways to specify the language, either by using the language ID (in my case 1033) or the name for the language. If you don’t know your language, just use this query to find it:

— Here’s how to find your language  

select [name], alias, lcid from master.sys.syslanguages;  

If you were to jump the gun and rerun the query from Demo 1, you’d think it would now ignore the word shifting since we just added it as a StopWord to our StopList. But there’s still one more step.

You need to attach your StopList to a table that has a full text index on it. This is a major improvement over 2005. SQL Server 2008 now allows you to get quite granular with the application of custom groups of words. You are limited to one StopList per table, however. One StopList can be applied to multiple tables. Here’s the code to associate our StopList with a table:

— Demo 4 – Add the StopList to the Full Text Index

use AdventureWorks2008;


alter fulltext index on [Production].[ProductDescription]

  set stoplist ArcanesStopList;

The alter fulltext command has been modified to add a set stoplist set of keywords. All we need to do is specify the tablename and the StopList to associate with that table. Now go run the query from Demo 1. You should get back zero rows.

Congratulations! You’ve now associated your StopList with the full text index..

I’m sure however you don’t want to leave it this way, so let’s look at what it will take to clean up the mess. First, you can decide you no longer want the StopList associated with the full text index. Time to use the alter command again.

— Demo 4-2 – Remove the StopList to the Full Text Index

use AdventureWorks2008;


alter fulltext index on [Production].[ProductDescription]

  set stoplist system;

Setting the stoplist to the keyword system will change from your custom stoplist to the system stoplist. You can also use the word off instead of system to turn off StopWords all together for the specified table. Now would be a good time to mention, if you want to use the standard system set of StopWords, instead using a custom set, use the system keyword, as you see above.

There may be times when you want to remove just a word or two from a StopList, but not disassociate the entire list. Its possible to easily remove individual words from the list.

— Demo 4-3 – Remove single word from Stoplist

use AdventureWorks2008;


alter fulltext stoplist ArcanesStopList

  drop ‘shifting’ language 1033;


alter fulltext stoplist ArcanesStopList

  drop ‘light’ language ‘English’

The syntax for the drop is identical to the add, except of course for using the word drop instead of add.

Finally, you may want to drop the StopList all together. There’s a drop command for it as well.

— Demp 5-3 – Drop StopList

use AdventureWorks2008;


drop fulltext stoplist ArcanesStopList;

This covers the basic use of StopWords / StopLists in 2008. I believe the ability to associate a specific set of words with one or more specific tables will give your searches greater power and flexibility.