Howdy ya'll!

It's just another cowboy post on the blog. Right now I'm travelling in high speed on the X2000 from Stockholm to Gotenburg. For one more week of Maintaining SQL Server 2008. As usual I have some new cool ways of doing stuff. This specific class is rather big, heard the number thirteen. Well, it's my lucky number, and it's just a number anyway so I'm feeling comfortable even if it's a big class. Hopefully I get the time needed for the extra, cool stuff I usually do. At least some indexing, bits and bytes of the datafiles and some optimizing SQL Server.

Thursday is the next special day. The Get on the bus, MCT Edition arrivies to Gothenburg. Meet our friends, and start building networks. As part of the team, member of the board and Partner Liasion at MCT Sweden, I welcome you to the event at IHM in Gårda. Check out the http://www.mctsweden.se for further information, register for the event and enter the competition. We have some nice sessions, you'll probably see Mikael Nyström, Alan Smith, Magnus Björk, me, Tibi Covaci among others speaking and socializing at the event.

While the bus head down to Copenhagen for a similar event on friday evening I'll stay in Gotenburg for the last day of training. With some luck, great effort and mutual understanding with the class I might get on another train to Copenhagen by lunch, and be at the evening event. If not I hope you're gonna enjoy it anyway.

Late friday night we're taking the bus to the ferry in Rodeby and get on the boat to Rostoc for further transport to Berlin and Tech Ed 2009 EMEA.

I finally got my session, Preparing for the exam 70-432 SQL Server Administration, ready. It's one of a series of prep-session at the HOL-area in Berlin. It's scheduled for Thursday, so if you're down in Berlin, and aiming for the certification MCTS: SQL Server 2008 Administration don't miss the session. I'll give you all the check this out's needed to pass the test. Close enough at least. I'll be working in the HOL-area too, as a TLG. TLGs was previously know as the ATEs if you remember them from earlier Tech Ed's. There will be twentytwo of us, aiding you people in working through the labs. Don't miss this opportunity, it's a nice break in attending the usual sessions at the event.

My goal with Tech Ed is of course gain new and cool stuff about SQL Server. Some of it I allready know, but not everything. One thing about new products, new features and the future, is that even if you get ahead through TAP-programs, BETAs and CTPs. And read all the blogs there are, and the first looks and so on, you only get your view of things. By attending other presentations and listening to other presenters as well as the product teams, you'll get the others opinions as well. Everyone who knows me understand that I also take the opportunity to discuss with other SQL-geeks too. By doing this you'll probably get a more accurate and wider opinion on the new product.

Another reason of attending is to meet with both old and new friends, collaborate and network. I especially looking forward of meeting a new, old friend that I missed while he was in Stockholm. Matt, we need to have some fancy, spancy drinks and talk some metal. And I promise I'll keep business on office hours, if you have some. One thingy I want to know is if there will be any MCITP-level SQL Courses, I've been awaiting them for some time know. If still uncertain, may I help out delivering them?

I'll try to keep you all updated through the blog...

Auf wiederseen or how it's proununced,

Mattias

PS: If you guys wonder how I ended in the Nordic Championship of Cowboy Action Shooting? I got one nice, shiny 1st place plaquet for winning the Duelist Category. Close to 300 shots fired, and only six misses. It's a new record for me...

As promised! Here is it, the session called "Från 30 sekunder till ett ögonblick - Data Driven Database Design". For those who don't recall reading swedish there might be a translated version in the future. Scripts for illustrating the slidedeck are under production and will be available later. For those not attending the session, briefly it's about how to gain high performance in the data layer by using normalization, generalization and de-normalization to structure the data based on the data itself instead of the straight forward application driven database design, how to get the data more symmetric and distributed, and how to optimize data access by chosing the right indexing strategy.

The slidedeck do not utilize animations, that's why there are so many slides. The content is owned by me and may not be reproduced without my permission. All you need to do is just ask.

And remember what I said on stage: "Normalization rocks, but you need to put some common sence into it."

TD09-DAT-001.pdf (472,48 kb)

To extract hierarcies based on parent-child relationships you can try with Common Table Expressions. This is a new feature from MS SQL Server 2005 and it can do some magic. And for retrieving hierarchies it's by far more effective than using a CURSOR.

WITH cte (LVL, EMPID, MGRID )
AS (
    SELECT
        1 AS LVL , EmployeeID AS EMPID, ManagerID AS MGRID
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT
        cte.LVL + 1, emp.EmployeeID, emp.ManagerID
    FROM HumanResources.Employee emp
    INNER JOIN cte ON emp.ManagerID = cte.EMPID
)
SELECT * FROM cte
ORDER BY LVL ASC

To explain how this works I'll simplify the CTE as a named and defined resultset. By naming it, it'll become addressable and this makes the magic. Now you can address previously retrieved rows. If we break down the query in the main parts it'll be easier to explain.

First the declarative part, WITH cte(LVL, EMPID, MGRID). This names the resultset to cte with the columns. Secondly we have the AS(...) that is the logic and will do the retrieval of rows. Third and finally the extraction of the data, SELECT * FROM cte ORDER BY LVL. This is the easy part.

If we look at the AS(...)-part. It consists of two SELECT-statements combined with a UNION ALL. The first SELECT-statement retrieves the top level of the hierarchy by retrieving the members that do not have a ManagerID, the 1 AS LVL symbolizes the top level. The second SELECT-statement joins the table against the resultset of the CTE based on cte.EMPID = emp.ManagerID, and in the same time we add 1 to cte.LVL to symbolize the level of the current member. The UNION ALL adds the rows retrieved in the second SELECT-statement with the resultset of the CTE. The second SELECT-statement is then repeated until there are no further rows to retrieve or if the MAXRECURSION-level is reached, by default 100. 
 
This second CTE retrieves the full hierarchy down to the level where a certain member resides.

WITH cte (LVL, EMPID, MGRID )
AS (
    SELECT
        1 AS LVL , EmployeeID AS EMPID, ManagerID AS MGRID
        FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT
        cte.LVL + 1, emp.EmployeeID, emp.ManagerID
    FROM HumanResources.Employee emp
    INNER JOIN cte ON emp.ManagerID = cte.EMPID
)
SELECT * FROM cte
WHERE LVL <= ( SELECT LVL FROM cte WHERE EMPID = 279 )
ORDER BY LVL ASC

The third example retrieves the direct chain of parents for a certain member. A higher LVL means in this example higher up in the hierarchy.

WITH cte (LVL, EMPID, MGRID )
AS (
    SELECT
        1 AS LVL, EmployeeID AS EMPID, ManagerID AS MGRID
    FROM HumanResources.Employee
    WHERE EmployeeID = 279
    UNION ALL
    SELECT
        cte.LVL + 1, emp.EmployeeID, emp.ManagerID
    FROM HumanResources.Employee emp
    INNER JOIN cte ON cte.MGRID = emp.EmployeeID
)
SELECT * FROM cte
ORDER BY LVL DESC