Category Archives: SQL Server Full Text Searching

Presenting SQL Server 2005 2008 Full Text Searching at Alabama Code Camp

On January 31st, 2009 I am presenting “Getting Started with SQL Server 2005/2008” at the Alabama Code Camp that is taking placin Montgomery, Alabama. This post has all the links relevant to my talk.

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:

http://code.msdn.microsoft.com/SqlServerFTS

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

Advertisements

Presenting SQL Server Full Text Searching at Alabama Code Camp

This Saturday, January 31st is the Alabama Code Camp in Montgomery AL. I’ll have the privilege of once again presenting one of my favorite subjects, SQL Server Full Text Searching. Don’t think this session is just for DBAs, I think developers will find it useful as well since I will include a demo code sample that calls a full text search query from a C# application. My session is currently scheduled last session of the day, and rumor has it attendees might have an extra chance at a give away at the end of my talk.

Speaking of give a ways, don’t forget the Speaker Idol contest. Still no entries, they are due to my in-box by noon this Friday the 30th! Five minute talk gets you a shot at a 1 year Premium MSDN Subscription.

See ya’ll there!

Gentleman, JumpstartTV Your Engines

Thought I’d spread a little link love today, and to start with I will point you to the http://jumpstarttv.com website. JumpstartTV hosts short training videos with one very specific, focused topic per video. When I say short, I mean short. Three to five minutes is the goal for each video. I was honored recently when asked to participate in the site, and have created a series for them on SQL Server Full Text Searching. The first video on installing was featured yesterday, but you don’t have to wait for the videos to be featured, you can see all of them by jumping to my JumpstartTV profile.

One thing to note, you will be asked to create an online profile. This is free, and it turns out very useful. You can use it to track all of the videos you watched. This makes it very convenient to come back later and refresh yourself on something you learned. In addition, the site has a “watch it later” feature. You can go all over the site picking out videos you think would be interesting and clicking the “watch it later” link. Then when you go to your profile, you’ll be able watch the selected videos one after the other. JumpstartTV has videos on both SQL Server and .Net, as well as some interesting ones in the “Misc” category, including bartending, self defense, and more.

The second link for the day is an interesting article on the simple-talk website, “Taking Back Control of your IT Career”. It was written by a friend of mine, Stephan Onisick and chronicles his ordeal of getting laid off from his company of seven years, through a period of retraing himself and ultimately landing a new job that met the needs he set out. Even if your company is nice and stable, you will find good advice for keeping your skills up in this article. Disclaimer, he does mention a presentation I did in the article, but in spite of that it’s still a good read. 😉

Next is a new SQL Server resource brought to us by the fine folks at Quest Software, it’s the new SQLServerPedia. The site is both a wiki and a series of podcast like videos you can subscribe to from your Zune or other music player. I have my Zune setup to automagically download new episodes as they come out. I believe it was @BrentO himself who clued me in on the site.

I’ve written in the past about CodeRush, the tool I refuse to code without. Well the wonderful folks at Devexpress have created a free version called CodeRush Xpress for Visual Studio. Now if you need to code on a budget, you can still enjoy CodeRushy goodness in your 2008 IDE! And it’s not even Christmas yet!

Many of you follow me on Twitter, if you don’t I’d love to invite you, I”m on as @arcanecode . Guy Kawasaki has a great article on How To Pick Up Followers on Twitter. Good article that shows some of the strengths of Twitter, and how to use them to everyone’s advantage.

Speaking of Twitter, thanks to @theronkelso I found a new service called TweetLater. This service lets you schedule a tweet to be delivered to Twitter at a later time. For example, I would like to be able to tweet that our BSDA meeting is about to begin. But as the current President I’m usually up front introducing the guest speaker, and thus not at a keyboard. TweetLater to the rescue, I can set it to auto post the meeting is starting and be in two places at once.

It’s also great as a reminder tool, I can queue up meeting reminder tweets for the entire year ahead of time and forget all about it. Another feature, you can set it to auto reply with a message to new followers, and it can even be setup to automatically follow anyone who is following you. I believe this is a resource I’ll be using a lot.

The next to final link is a reminder really, to the Alabama Tech Events site. This is a community site for posting technical events of interest to folks in the state of Alabama. Please note that the event doesn’t have to be in Alabama, just of reasonable interest to folks in the state. We’ve posted events in Tennesee, Mississippi, Florida and Georgia. If you have a technical event contact me or one of the other user group leaders to get it added.

I’ll wrap up today’s link lovefest with the site analogous to the Alabama Tech Event site, but for the entire country: Community Megaphone. This site lists events from all over the United States. You can filter by state or event type.

SQL Saturday Orlando Full Text Searching Session

On October 25th, 2008 I presented “Getting Started with SQL Server 2008 Full Text Searching” at SQL Saturday in Orlando. First off, I want to apologize to the attendees for taking so long to get this material posted. But I can finally reveal what has been sucking up my spare time lately!

I, along with other MVP’s have been working on something called the SQL Server 2008 MVP book project. Many SQL MVP’s, myself included, volunteered to write one or more chapters for the book. Once published, all proceeds will go to charity. I’m very excited at the opportunity to contribute the chapter on Full Text Searching, which I submitted today. I’ll keep you posted as things progress.

We’re not the first ones to go down this road, Sara Ford is donating the proceeds from her Visual Studio Tips book to supply scholarships for the young folks in the hurricane ravaged town of Waveland MS. I hope you’ll support her efforts as well.

Meanwhile, what you really came for was the links relevant to my talk.

Detailed descriptive material can be found on my blog, ArcaneCode.com, 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:

http://code.msdn.microsoft.com/SqlServerFTS

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

SQL Saturday – Orlando – October 25th

Just a note for my friends in and near Orlando, I have agreed to come down and speak at the SQL Saturday event on October 25th. Just follow the link and pick SQL Saturday #8 for more details. My presentation will be on Full Text Searching. A big thanks to Andy Warren of End To End Training for inviting me and helping to make this possible.

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, ArcaneCode.com, 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:

http://code.msdn.microsoft.com/SqlServerFTS

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 sl.name as StopListName

      , sw.stopword as StopWord

      , lg.alias as LanguageAlias

      , lg.name  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

    , slg.name

  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 c.name as CatalogName

    , t.name as TableName

    , idx.name as UniqueIndexName

    , case i.is_enabled

        when 1 then ‘Enabled’

        else ‘Not Enabled’

       end as IsEnabled

    , i.change_tracking_state_desc

    , sl.name 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 t.name 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’

       end

  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.