Category Archives: SQL Server 2008

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 Server 2008 Books On Line Update

The SQL Server Books On Line have been updated and are available for download. Having your local copy is important when you develop off-line, or if you have a slow connection. Just like your software should be kept up to date, so should your documentation. Click on the link below to be taken to the Microsoft site to download the books on line.

SQL Server 2008 Books on Line Update

Even though they haven’t been updated in a bit, if you have never updated your SQL Server 2005 Books you should do so from the link below.

SQL Server 2005 Books on Line Update

If you do prefer to read on-line, you can jump right to the MSDN site for SQL Server 2008 Books on Line at http://msdn.microsoft.com/en-us/library/ms130214.aspx. The 2005 version is at http://msdn.microsoft.com/en-us/library/bb418498.aspx

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.

My Dev Kit

There’s a new meme of sorts on the web, folks talking about the tools they use to develop with. I first saw it on Shawn Wildermuth’s blog. Shawn’s a great guy, he co-wrote most of those .Net MCTS/MCPD study guides from MS Press, and does a lot of training on Silverlight. So I thought I would keep the meme alive and talk about my own tools.

Hardware

I do a lot on the road, so a laptop is essential. Mine’s getting up there in age, it’s an HP Pavillion dv8000. 2 gig ram, two internal 160 gig hard disks, 17 inch wide screen, single core 64 bit processor. It’s OK, but will hopefully get replaced next year with something with more cores and horsepower. I don’t care much for the keyboard, so I bought an external keyboard from Lenovo. It’s got a trackpoint so I don’t have to take my hands off the keyboard very often, and I use it with both my laptop and the Dell that work supplies me.

At home I use a larger wireless Microsoft mouse, on the road I use one of the smaller Microsoft travel mice. Also in my hardware list is an external Seagate 1TB drive. It hooks up via either firewire or USB, which is nice when my USB ports are all full.

Also in my list is my Zune. Yes my Zune. Cubical farms can get noisy at times, some good tunes on my Zune really help me to zone out and ignore my surroundings, focusing on my code. It’s also nice on my commute or daily walk, I listen to podcasts to keep up my technical knowledge. At night I hook it to my TV via my X-Box 360 to watch video podcasts, or sometimes I lay in bed before going to sleep and watch.

My final piece of hardware is my iPaq, it helps keep my appointments in line and my contacts, plus I have lots of e-books loaded on it for reading. I also used to use it for podcasts prior to getting my Zune.

Operating System and Dev Tools

My laptop currently runs 32 bit Vista Ultimate with Service Pack 1. Since it maxes out at 2 gig, and some 64 bit drivers were not available when Vista first arrived, I saw no benefit to 64 bit and took the path of least resistance. I have quite a few virtual machines in a variety of OS (Server 2008, 2003, XP, Vista, and Ubuntu) for testing, development, and running Beta versions of programs. For a web browser, I bounce back and forth between FireFox and IE7. For a while I was using FF most of the time, but IE7 was a big improvement over 6, and I’m now using them about 50/50. I suspect when IE8 comes out I may be using it more, but will have to see.

Like Shawn I also use Outlook 2007 for my e-mail client. It’s so much easier to organize my mail in Outlook than the g-mail host. But I also use the other features, such as the calendar and task list to help manage my life. I also use the rest of the Office suite for my daily tasks.

I use SnagIt for grabbing still screen captures, awesome tool, and Camtasia for video screen captures. I’m working on several video tutorials now, which is fun but time consuming (which also explains while my blog posts have been off of late). I use Paint.Net for basic photo / image editing. For creating my blog posts, I write them originally in Word 2007, then use Windows Live Writer to post them to my blog.

For quick access to my daily programs, I use one of two things. I really like Bayden Systems SlickRun. I also create a shortcut menu using a technique I blogged about in February.

Developer Tools

As you might expect I use both SQL Server Management Studio and Visual Studio 2008 Team System for day to day development. My top add-ins are Red-Gates SQL Prompt bundle for SSMS and CodeRush for Visual Studio. For a text editor, I absolutely love UltraEdit. Since I have blogged a lot about my dev tools in the past, I will keep this section short.

The Cloud

I’m on a couple of social networking sites, in addition to this blog:

· Twitter

· Posterous

· LinkedIn

· MSDN Code Gallery – One site for SQL Server Full Text Searching and one for SQL Server Compact Edition.

Passing the Baton

OK, your turn, let’s see your blog with your tools!

Getting Started with SQL Server Compact Edition 3.5

Tonight I will be presenting “Getting Started with SQL Server Compact Edition 3.5” at the Bug.Net users group. If you go to my Arcane Lessons page and scroll down just a little, you will find a section called “Getting Started with SQL Server 2005 Compact Edition”. The subjects referenced there are still valid under Visual Studio 2008 / SQL Server Compact Edition 3.5.

However, there are some new features that make it worth our while to give SSCE 3.5 a second look. For example, did you know you can access Compact Edition using LINQ to SQL? My updated presentation and code demo will show you how.

Speaking of which, you can find my PowerPoint slides and Code Demos at the new Microsoft Code Gallery site I’ve setup:

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

Over the coming days I will also be adding blog posts to talk about the new features in version 3.5, and how to access SSCE from LINQ to SQL.

Presenting Getting Started with SQL Server Compact Edition 3.5 at BUG.NET Meeting

Just wanted to let everyone know I’ll be doing a presentation this coming Tuesday night, August the 12th for the Birmingham .Net Users Group (BUG.NET). My topic, as you may have guessed from the title, will be using SQL Server Compact Edition.

While I will be using Visual Studio 2008, I will point out which pieces are 2005 compatible. I will also cover the use of both traditional coding techniques as well as how to use LinqToSQL to talk to the Compact Edition.

The meeting takes place at 6:30 pm at New Horizons Training Center in Homewood.

I also plan a new series of blog posts to start later this week on the subject, and will be creating a new Code Gallery site to hold my examples.

Also, don’t forget the regular BSDA meeting this coming Thursday night, the 14th. Also starting at 6:30 pm at New Horizons, Shannon Brooks-Hamilton, a software usability expert, will be there to talk about user interface design. Lots of good thought material on how we can make better UIs for our users.

SQL Heroes and Data Dudes

In case you are wondering where I’ve been lately, it’s been a combination of issues at work plus putting the final nails in the schedule for TechMixer University. It’s been a big team effort, and as chair of the education committee I’ve had some great help gathering speakers for the event. We just published the first round of speakers and tracks so be sure to go take a look. And if you haven’t signed up yet, you better hurry registration is rapidly filling up!

On the interesting SQL News front, thanks to Jason over at StatisticsIO I just found out about the SQL Heroes site.  They are having a contest for the best SQL Server 2008 community project submitted to CodePlex. Here’s a listing of the current SQL projects currently there. Looks like some useful utilities, and the contest still has a month left in it so plenty of time to assemble your own project and submit to the contest.

I recently began diving deeper into Visual Studio Team System 2008 Database Edition (aka “Data Dude”) and wanted to learn more about it. Fellow SQL MVP Andy Leonard has come out with a new book, the first in a series on the subject. Volume 1, available from Solid Quality Mentors, takes you from the basics of creating your first database project, into versioning your database and scripts, right into building and deploying. There’s also some interesting and useful material in the appendixes, including installation of data dude and importing a database schema. I was also interested in getting a peak at his development environment. And at a mere 15 dollars I thought it was a bargain.

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.

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.