I got some comments about the Last_Day-funktion. Mainly "Why all this stringconverts" and so on. My only answer for this is "Why not?" and the main reason for the post was to describe how easy it is creating functions, working with different datatypes and manipulate strings. Well, I guess it also showed how easy it is to get something wrong. There are many ways of doing this and I have a bunch of them. I'm sorry for publish "bad" code... We all know that if we read it on the net it's true... *smiles

Anyway, to reach the last date in current month you can do this:

SELECT getdate(), DATEADD(day, 0 - DATEPART(dd, DATEADD(month, 1, getdate())), DATEADD(month, 1, getdate()))

It's the bold part that's interesting. Lets break it up!

DATEADD(month, 1, getdate()) is just one month ahead. Let's call it {today_add_one_month}

DATEPART(dd, DATEADD(month, 1,  {today_add_one_month})) gives us the actual daynumber of next month. Could be called {daynumber_next_month}

Using 0 - {daynumber_next_month} as an offset, {date_offset}

DATEADD(day, {date_offset}, {today_add_one_month})

So, there you have it. I guess this is the fastest way of doing it. But with functioncalls you'll never know.

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...

It's been a while, working right now on a big, secure and innovative reporting solution for one of the biggest security firms in Europe. More of this when it's ready, just wanted to inform you all why I've been a non-blogger...

Anyway, TechEd EMEA. This year in Berlin, Germany, in the middle of november. Two years since the previous TechEd EMEA visit, last time in Barcelona, Spain. More tempting weather in Spain than Germany. But then again, even if I love the sun and the close-to-be summer in Spain my visit for TechEd is always to hear about new technologies. Or technologies, more like new SQL things. Last time wasn't all that enjoying new stuff. Well, I was looking for SQL Server 2008 after been looking at the pre-releases for about six months or more by then. Nothing new in those sessions, more like acknowledge of already gained experience. Well, that was the past. Let's talk about this trip to Berlin instead.

First of all I'm looking forward for this trip, haven't been abroad for about six months. I enjoy travelling. This trip will be adventurous. As part of the board of MCT Sweden we're planning a bus tour through Europe to Berlin. We'll start in Stockholm five days ahead of the event with a small event for techies, with a trip to Gothemburg and another small event, and with a final event in Malmoe/Copenhagen with sessions about certification, Windows 7, BizTalk and more. I'll be in Gothemburg and probably in Malmoe/Copenhagen too, not with a session. More like a rep of MCT Sweden. But then again, I'll probably preach some SQL too. At least on request.

This TechEd is just not for visit, it's for work aswell. I'll be situated at the HOLs as a Technical Learning Guide, formerly know as ATEs. Helping people out with the labs and questions about certification. I'll probably deliver som SQL exam prep sessions, not yet acknowledged but probably. I'll will try to attend as many SQL sessions as possible. If I'll learn something new I'll keep you all posted. If you're in Berlin, try to locate me. I'll stay at a cheap, possible sleazy hotel just 800 meters away. If you have a nice room, and want a roomy, contact me.

Ok, back to the bus tour. Have you heard about the "Get on the Bus, MCT Edition"? If not hurry up and visit the http://www.mctsweden.se site while the competition still is running. You can win a transport down to Berlin and back to Sweden and a pass to TechEd. You only need to win the competition we are running. Don't miss this exclusive opportunity, you'll meet me on the bus.

Please remember to join the forum, http://forum.mssqlserver.se. As long as they are available you'll get t-shirts, http://www.mssqlserver.se/t-shirts.html, if you register and message me your postal address and size; chose between L, XL and XXL.

Have a great summer full of SQL!

KKReg.zip (1,32 kb)

Have you heard about Kilimanjaro, not the mountain but the next version of Microsoft SQL Server? It recently got announced as Microsoft SQL Server 2008 R2 and are estimated to go RTM, Ready To Manufacture, in first half of 2010. We can expect a CTP, Community Technology Preview, to be availible this summer/autumn. No promises on my account, just estimated on timing. Ok?

Well, I guess some of you yet had the time to go 2008 from 2005, and probably some of you are still in 2000. And some of you is just about to getting used to intelli-sense, FILESTREAM and a bunch of new features in Microsoft SQL Server 2008, one and a half month after SP1. If you haven't, get to SQL2008 and SP1 now. All of you!!!

Anyway, we will get a new SQL in about a year and it's getting time to learn about what we're missing and will get in the new version. Unfortunally I haven't had the chance to see any previews yet, but I can promise you I'm working on that. Still I just wanted to do some names dropping about new, cool features on the product. Remember, this is hearsay and I truely hope someone isn't pulling me now.

So here it goes:

• Master Data Services, which briefly can be explained as a meta data catalog. It's based on Stratature which Microsoft aquired in june 2007.
• Self-managed business intelligence (BI) for analysis and reporting, that finally might put MS BI on every single desktop.
• Support for more than 64 logical processors, even the techie-hard-core-extreme-environment-performance-guy has to have something.

As time goes and news get to me, I'll try to keep you all posted.

I'd like to share some samples from the class "T618 Informationsbehandling med SQL". This is the design we discussed the last day.

For all you others, this is an example on how to do it based on the techDays-session. There are some minor adjustments in the script to be fixed, but it works  good enough. For example the fillfactor settings need to be calculated based on actual load and so on.

 Anyway, enjoy!

OrdersDB_PART1_V1.sql (9,20 kb)

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