This past Tuesday, Brian Knight visited the St. Louis SQL Server User Group to present on SSIS. The day ended up being one of the best meetings our group has ever had with a record turnout of 65 in person and 21 online. We really appreciated the wonderful tips that Brian showed us, and I'm sure everyone enjoyed the presentations.

It was a really fun day for me since I got to pick up Brian and Rob Peters from the airport a few hours before the meeting and take them back at the end of the day. I'll say it was fun for me, but Brian and Rob now know what a ditz I can be when trying to find my way or where I parked my car. Luckily, we ended up everywhere we needed to be on time. It was great to spend some time just hanging out with Brian before the meeting talking about MVP stuff like writing books, attending conferences, and eating crab wontons.

Even though I have contributed to one of his books and JumpstartTV.com, we really have not spent much time hanging out. Back during the book, I remember several panicky phone calls to Brian. Luckily, he always had great ideas and was very encouragng. Other than that and a few minutes at several PASS Summits, we have never had much time to talk. Now I know what corny jokes he comes up with!

So, thanks again, Brian, for coming to St. Louis to speak at our meeting and for putting up with my driving!

Hey, there are probably some other blogs here letting you know that the PASS call for speakers is open right now, but another reminder can't hurt. Here is the link to the new Speaker Resource page that will give you all the information you need and the link to the site to submit the abstract:

http://www.sqlpass.org/Community/SpeakerResource.aspx

We are looking for MVPs, people who have spoken at PASS or other conferences before, and community speakers who are ready to speak at the next level. The call for speakers closes at midnight on April 10th.

Thanks,

Kathi

Once again I was tagged to answer a question. This time by Chris Shaw http://chrisshaw.wordpress.com .

Here is the question: Do you feel like you are being treated fairly at your current or past employers? The question stems from the fact that very few people today stay at a company 20 to 30 years like they did when I was growing up. Do you feel like the company feels a loyalty toward the employee or do you think that they look at you just as head count? No reason to get yourself in trouble, so you can refer back to past employers.

I have to say that my current job at Bryan Cave is the best job I have ever had. I’m not saying that I never get frustrated or aggravated or stressed, but overall it is a great place to work. Unfortunately, it seems that “The Cave” is not immune to the downfall in the economy, so they are cutting back on things that have been important to me like training and conferences. For example, I attended the PASS Summit from 2003 to 2007 on their nickel. Last year I felt like I really needed to attend PASS even though my request was turned down, so I paid my own way and burned some vacation days to do it. I feel very grateful for those previous four trips when the firm picked up the bill and understand that times are tough. Not sure that I am willing to use vacation days to go again, though.

Honestly, I don’t know if the firm looks at me as an FTE or feels any real loyalty towards me. I think it is smart business to make employees feel that they are appreciated and part of the company. You can probably do that with either intention. 

 So, I guess I need to tag some more people. How about Tom LaRock, Tim Ford and Allen Kinsel.

I have been tagged by Tom LaRock to write about “what was your first computer and what were some of your favorite games”. 

The first computer I owned was a Texas Instruments 994A purchased in 1981. It was actually my husband's idea, but I ended up being the one who used it the most and learned the most from it. I remember writing two programs for it myself in TI Extended BASIC that I was pretty proud of -- a game that simulated playing Yatzee and an amortization schedule for our mortgage. It was some nasty spaghetti code with lots of GOTO commands, but I dreamed that one day I would be able to earn a living working with computers. We didn't buy our first PC until 1992.

We purchased some adventure games that were fun, but I don't remember the names. There were no graphics to the games. You just typed in things like "Look left" and the screen would display something like "You see a rock and a key."

So, I guess I need to tag someone else. How about Brian Knight, Andy Warren and Steve Jones. 

Yesterday I spoke on 2008 T-SQL and Data Types at the St. Louis Day of .Net event. This was an all day event featuring about 30 speakers, most of them local, and over 50 sessions. When I was contacted by the organizers recently, I agreed to participate even though I had vowed take a break from speaking and writing in December. For one thing, the event was to be held on a Saturday, and I wouldn’t have to figure out how to get the day off work. They also let me speak on a topic I had already presented four times earlier in the year so I didn’t have to do much to prepare.

Yesterday’s event turned out to be very high energy and well organized. The only noticeable glitch was running out of coffee mid-morning. They also had some nice refreshments and great prizes including dozens of books and a couple of X-Boxes. The attendees I spoke with were very excited about the presentations they attended.

At one point while sitting in the speaker room, I realized that I was in the presence of some true geeks. Many of the speakers were well beyond the bleeding edge, pushing the technologies they work with past known limits or working with pre-released software.  

In 2009 I am planning to do much less speaking and more writing. I would be glad to speak at this event again next year and am already thinking about possible topics.

I have given a talk about new data types and T-SQL features to three local groups.  I am presenting it again at the St. Louis Day of .Net on December 13th (http://stlouisdayofdotnet.com/).  Since Itzik Ben-Gan was giving a similar talk at PASS last week, I decided to attend his talk to get some additional insight on the topic.

Itzik’s favorite new T-SQL feature is the grouping sets. Before his talk, I didn’t really see any benefit over using UNION queries (examples below) which will give the identical results.  Itzik said that the new grouping sets performed better than UNION queries. When I had experimented with the new feature, I had found identical performance.  Rather than embarrass myself by questioning the T-SQL guru, I made a note to revisit this later.

Today I decided to play with the new feature to see for myself.  What I found was that if no WHERE clause was used, the performance was the same between the two techniques. The execution plans were almost identical with both queries performing two clustered index scans.  Adding a WHERE clause with a filter on the clustered indexed column changed things considerably, and now the grouping set query performed better. It had one clustered index seek while the UNION query had two.  I found that adding any WHERE clause, even one that resulted in a scan, improved the performance of the grouping set query.  

My examples had only had two grouping levels, and I suspected that adding more grouping levels would show an even more dramatic difference in performance. My hunch was correct. The performance was always better with the grouping set query as long as a WHERE clause was included.  The UNION queries had to access the table once for each level while the grouping set query would access the table just once.

Here are the queries I experimented with using the SQL Server 2008 AdventureWorks sample database.

TEST ONE, identical performance, each has a clustered index scan:

select salesorderid,SUM(unitPrice),ProductID
from Sales.SalesOrderDetail
group by grouping sets(SalesOrderID,ProductID)
go
select null as salesorderid,SUM(unitprice),productid
from Sales.SalesOrderDetail
group by ProductID
union
select salesorderid,SUM(unitprice),null
from Sales.SalesOrderDetail
group by SalesOrderID
-----------------------------------------------------------------------

TEST Two: Add a WHERE clause. Now the grouping set query has one clustered index seek while the UNION query has two:

select salesorderid,SUM(unitPrice),ProductID
from Sales.SalesOrderDetail 
where SalesOrderID between 40000 and 50000
group by grouping sets(SalesOrderID,ProductID)
go
select null as salesorderid,SUM(unitprice),productid
from Sales.SalesOrderDetail
where SalesOrderID between 40000 and 50000
group by ProductID 
union
select salesorderid,SUM(unitprice),null
from Sales.SalesOrderDetail
where SalesOrderID between 40000 and 50000
group by SalesOrderID  
-------------------------------------------------------------------------------

TEST Three: Better performance even when filtering on a non-indexed column. Still one clustered index scan instead of two

select salesorderid,SUM(unitPrice),ProductID
from Sales.SalesOrderDetail 
where UnitPrice between 10 and 20
group by grouping sets(SalesOrderID,ProductID)
go
select null as salesorderid,SUM(unitprice),productid
from Sales.SalesOrderDetail
where UnitPrice between 10 and 20
group by ProductID 
union
select salesorderid,SUM(unitprice),null
from Sales.SalesOrderDetail
where UnitPrice between 10 and 20
group by SalesOrderID
-----------------------------------------------------------------------------

TEST Four: The performance difference increases with each grouping level added.

select salesorderid,SUM(unitPrice),ProductID,SpecialOfferID
from Sales.SalesOrderDetail 
where SalesOrderID between 40000 and 50000
group by grouping sets(SalesOrderID,ProductID,SpecialOfferID)
go
select null as salesorderid,SUM(unitprice),productid, null as SpecialOfferID
from Sales.SalesOrderDetail
where SalesOrderID between 40000 and 50000
group by ProductID 
union
select salesorderid,SUM(unitprice),null,null 
from Sales.SalesOrderDetail
where SalesOrderID between 40000 and 50000
group by SalesOrderID 
Union
select null,SUM(unitprice),null,SpecialOfferID
from Sales.SalesOrderDetail 
where SalesOrderID between 40000 and 50000 
group by SpecialOfferID 

I arrived home this morning after another PASS Summit. This is my 6th in a row. Each year I have more fun, get more excited about SQL Server and meet more people.

This year had a different dimension. I had additional options because of being an MVP. There were MVP sessions as well as a reception one night and dinner last night. I also wanted to focus on networking and met with a publisher about a potential book deal. If you were there and spoke with me, you probably know I was sick all week and lost my voice. I didn't get to party with friends as much as normal and sadly didn't get to go out for Karaoke.

As a key volunteer, I know about the challenges PASS has faced the past couple of years. We are working very hard to become more transparent and streamline our processes. We realize that we can never please everybody and must focus on our core values.  Congratulations to Andy Warren. He is one of our new board members. Andy has been a very outspoken critic of PASS, and I expect to see him make a big difference.

Being a key volunteer, as I mentioned, many attendees seek me out to praise the Summit or to pass on their complaints or ideas. One recurring theme I heard is that PASS is different than any other conference. At PASS, there is a sense of community, of family. Not only is it a great technical conference, it is a way to network and meet other people facing the same challenges that you do every day. It is a great way to interact with Microsoft, MVPs, and authors. You may end up at the same table at breakfast with the person who wrote the book that is sitting on your desk at work.

This year I was very honored to be acknowledged by PASS for the work I have done for them. I received the PASSion award given each year to the volunteer who has make the biggest difference to the organization. I've been a volunteer for four years and have enjoyed being part of such a wonderful organization. I encourage you to get involved with PASS.

Some news: Membership is now free! Our print magazine has been discontinued. We will still be publishing great content online and are looking for writers and other content. We have also started a social networking site called PASSPort. Go to SQLPass.org to check out PASS! 

In less than two days I'll be in Seattle for my 6th PASS Summit. I am trying to put all the important events in Outlook, but I am finding that there is a lot of overlap. Now that I am an MVP, there is even more to consider. I will be speaking on how to get started writing technical articles on Friday, so be sure to check out my session if you are interested in that topic. Somehow, I have managed to get overly involved with PASS lately and find that I have several other responsiblities this week as well. 

This year we are offering both online and paper speaker evals -- so you don't have an excuse to not fill out your evals!  We are also looking for  volunteers to spend an hour or so keying in the paper evals. Please send an email to kkellenbe@hotmail.com if you are interested in helping out.

One other important note. It is once again time to vote for the Board of DIrectors. I have heard that there are three slots open and that six candidates will be running. It is going to be a tough choice, and I am already trying to figure out if I can vote twice so that I can vote for all six. All kidding aside, I am endorsing Lynda Rab, Tom "SQLBatman" LaRock and Andy Warren.

Andy Warren made a trip to St. Louis this past weekend to put on a speaker workshop for our user group.  Several times a year he offers the free workshop at End to End Training in Orlando. We had a pretty good turn out. Hopefully, we will get a few new speakers for our group.

Andy had lots of good advice for beginner speakers as well as those more experienced. One interesting tip is to have a helper in the audience, especially at a conference. The helper can gather up the laptop, power cord and other equipment at the end of the presentation so the speaker can guide audience members with questions away from the front of the room. The speaker doesn’t end up leaving anything behind and can get out of the way so the next speaker can set up.

We also spent some time at the end of the day talking about SQL Saturday. We are considering having a SQL Saturday in St. Louis next year.  Andy has put a lot of work and resources into helping user groups host their own events. Be sure to send him a note if you are considering something like SQL Saturday for your own SQL user group.

Thanks again, Andy! We really appreciate you sharing your time and the benefit of your experience.

There are many technical conferences available for SQL Server professionals, such as PASS.  I have been lucky enough to go to the last five PASS conferences, spoke at a couple of them, and am the current Program Manager. The energy, educational sessions and networking opportunities found at PASS are just incredible.

I few months ago, I was approached by Chris Shaw to speak at a new type of conference, a virtual conference, held in June. This particular conference was hosted by SSWUG. The sessions are taped about a month before the conference is held.  Recording sessions instead of presenting to a group of people is definitely a different experience, but still a lot of fun.

The benefit of this type of conference is that there are no travel expenses for the attendees. The cost of the conference is also very inexpensive. Since the sessions are prerecorded, they are played three times, and you have a chance to pick up session later that you miss. The sessions are also available for download for two weeks after the conference is over.

Originally, I thought that there would be no chance for networking with this type of conference, but I found I was wrong. During the sessions, the speakers are available for chatting to answer questions.  I ended up chatting with some of the same people during multiple sessions and have exchanged email messages with a few attendees after the conference. 

Work is now underway for the next conference to be held October 1-3, 2008. http://www.vconferenceonline.com/sswug/sqlf/ .  I will be back again and am busy getting my sessions ready.  So, if you are not able to attend an in person conference this year, or would just like to view some great educations sessions from your desk, be sure to sign up.

Probably the least enjoyable thing about being a DBA is patching servers. We received the bad news in the July MS security bulletin that a security patch MS08-040 needed to be installed on SQL Server 7 - 2005. http://www.microsoft.com/technet/security/bulletin/ms08-040.mspx

I spent some time with our SMS guy to see if we could push this patch out. What I found was that because I often remove builtin\administrators, SMS wasn't going to work.  I decided to go ahead and start patching manually.  The really confusing thing about this is that there are 5 different patch versions depending on which version (release + QFE vs GDR).  If you look at the article, be sure to expand the FAQ to see a nice chart.  Once you get to the correct download page, there are also different versions depending on the processor.

After installing on a bunch of dev/test servers earlier in the week, I started patching production servers last night starting with my European cluster. This morning at 3:00 am, I started on the US cluster.  The European cluster is just a 2-node cluster with one instance. My US cluster has 4 nodes and 4 instances. I had allocated an hour to get this done, but because the Citrix server I was on had its weekly scheduled reboot at 3:30, it ended up taking and extra 15 minutes.

After the cluster was done, I had a handfull of standalone servers to start on. I was surprised that one of them already had the patch installed. In fact, I found that two of the servers on my list for tonight were already patched as well. All three of these are default instances with builtin\administrators still in place. So, it is possible that our SQL Servers for SMS, MOM, etc., will get this patch automatically as long as they have the latest service pack. I think that the patch must have been pushed by WSUS.

Anyway, I am glad that I manually installed it on my clusters; they deserve a little extra TLC. 

Today is day one of the SSWUG SQL Server Virtual Conference II. This has been really fun so far. Our sessions were pre-recorded last month and each one is played three times.  The speakers are usually available during the sessions to answer questions by chatting.  After the conference the sessions are available for on demand viewing for two weeks. They even have prizes!

At first I wondered how creepy it would be to see and hear my session playing while I answered questions. Maybe because of the jumpstarttv.com videos I have done, it actually is not so bad. 

This has been a really fun experience and I can't wait to view some of the other sessions. They are having several other conferences coming up, including the SQL Server III conference and a BI conference.  It is a good inexpensive way to get gain some knowledge without leaving your desk!

http://www.vconferenceonline.com/ 

Check out the SSWUG Virtual Conference next week. Here is a link with more information: http://www.vconferenceonline.com/sswug/demo.asp .  The first 200 people who use this code, VIPKK2008DIS, get a discount off the low $100 conference fee.

I gave a presentation Tuesday to my local SQL user group on 2008 T-SQL and data types. One of the items covered was HierarchyID. I had a simple but thorough code sample that demonstrated most of the functions. I guess I have been out of the loop lately, because at some point in the presentation, fellow MVP Dan Guzman mentioned that RC0 had been available for a week.  I was using the Feb CTP thinking that was the latest available.

Next week I am giving the same presentation to the VB group in town and will make sure I am using RC0. I decided that I should take a look at the release notes before getting it installed. I found out that two of the functions used with HiearchyID have changed names. Instead of Reparent(), now you must use GetReparentedValue(). Instead of IsDescendant() you must use IsDecendantOf().  The first change is just a matter of changing the function name. In the second case, the function is used in a converse manner.

HierarchyID is used to represent a node in a hierarchy such as a family tree, an org chart or a bill of materials. It is more difficult to insert and update the rows but much easier and more efficient to query them. This is a CLR-based data type with methods and properties much like a class. When adding a new row, you have to get the node of the parent and figure out the last child added before the row can be inserted. It took me a bit of time to figure out what was going on.

This new data type is pretty interesting, but the group decided that it might not be worth using. For example, I found when playing around with it that it let me delete an intermediate level node. When you move a node the children of that node get left behind. You would have to make sure that you account for those issues in stored procedures or the application logic.

I have been thinking about getting the MCT credential for about five years.  During the MVP Summit I attended a session titled "So, you want to be an MCT".  They convinced me that this might be a good idea, and I began looking at the requirments. One of the requirments is showing that you have training skills. This can be met in several ways one of which is getting the CTT+ certification first.

This week I attended a CTT+ bootcamp. This is the first time I have ever attended a bootcamp course. I didn't realize that, except for eating and sleeping, you spend every moment in class or doing homework. It was a pretty intense week.

The class was a great experience. The instructor was extremely confident that we would all pass the computer based test taken at the end (we all passed) and that our video submissions would also pass. He gave us lots of coaching and said he would work with us till midnight last night if that is what it took. Luckily, we were all done with the videos five minutes before time to take the computer based test.

There were 7 students in the class and we all became friends by lunch the first day. It was great because one really needs the cooperation of the other students to get the videos made. We were all students in each other's videos.

Not only am I sure that I will soon be an MCT, I learned a lot about how to be a better trainer. Just like being competent in SQL Server or .Net, training is another discipline that I will continue to learn about and constantly improve my skills. So, once again I find out that the more I know, the more I need to learn.