Latest news on 70-431 simulation questions

I got some news this week about the core SQL Server 2005 exam. A bit of an update from someone who has recently tried 70-431. He sent it to me having read my previous post on the matter.

"I took the exam last week and got all the M/C questions right, but did horribly on the simulations. I figured out couple of them, but I couldn't do the others. Ended up with a score of 220. This is the first certification test I've encountered that used such a large set of simulations (15 on the test I took).

"Good job! I had no business passing this test, since I know nothing about SQL Server. This will add a degree of difficulty to the certs, but not insurmountable."

I wish this guy a bit of luck with the exam, but I'm very pleased that despite his knowledge of the multiple-choice questions (he must've had inside knowledge to get 100% right) didn't help him pass.

My tip for you (reader) is: Use the product! And if you have been using the product for a while, just try the exam. Worst case, you fail and can try it again once you've used the product some more. Don't bother cheating – if you're worth your stuff, you should be able to pass the exam easily.

My favourite thing about SQL Server 2008

A few people have asked me what my favourite thing is in SQL Server 2008 ("Katmai"). But my favourite thing isn't actually a feature at all, it's a mindset that Microsoft are taking with it.

This mindset is "We will only put features into the CTPs once they are basically complete – including the documentation." And this makes me say "Wow!".

One of the problems with all beta software is the stuff that's in there that just isn't finished yet. You try to use some feature, and it doesn't work. Or worse, something dies because you tried it. It's these scenarios that stop people trying out betas, and seeing people using the previous version still nearly two years after release (It's now over 21 months since SQL 2005 was released).

Not the case with SQL Server 2008 though. Functionality may be limited, but hopefully new features should be both stable and documented when they appear. So now because this is the case, people should be able to port their systems over before release, confident that features won't be changing significantly between that time and RTM.

This week at my user-group I'm giving a talk on MERGE & TVPs. Two essentially different topics, but ones that compliment each other nicely. I think people will be leaving this meeting picturing places in their code where they want to refactor it to take advantage of these new features.

Web 2.0 definition

At the Bloggers' Lunch at TechEd, the panel was asked how they would define "Web 2.0". They largely talked about new tools such as Silverlight and AJAX, and about the interaction of consumers and sites (particularly in relation to blogs). But none of them touched on what I think Web 2.0 is about.

I think Web 2.0 is simply the second dot-com bubble. In the late 1990s, companies threw massive amounts of money into web-based ventures. Confidence in the internet was very high. Of course, this all changed in late 2000 – the bubble burst, and people saw the web (and IT in general) as an area with low return on investment. In the past few years, this has turned around again. Companies have seen the internet become more and more accessible, and the public have come to expect business to be done online. Consumer confidence in the web has continued to grow (it never really stopped), and in the Web 2.0 days, business confidence in the web has recovered.

For me, Web 2.0 is defined as "the second wave of confidence in the idea of doing business online". Of course it has been helped by an improvement in tools, by improved security, by broadband, and by the interaction that we see in myspace-style sites and rest of the blogosphere, by mash-ups, by Google Maps and Google Earth, by Silverlight, AJAX, and everything else people in the IT space love. But Web 2.0 is really just about businesses being confident about the web again – about business investing in internet-based technology.

Virtual Server 2005 R2 giving 500.0 errors under Vista

I finally got it sorted. I had all the IIS6-compatibility options enabled under 'Programs and Features'. I was running Internet Explorer as an administrator. But still I was getting a 500.0 error when I tried to go to the Virtual Server 2005 R2 admin page. Tonight I decided I wasn't going to let this one rest.

Eventually I got it fixed by setting the virtual directory to use Basic Authentication instead of Windows Authentication. Go figure.

Indentation in a SQL query

I was asked about providing some results in a format that would suit a particular custom grid control. A Report Viewer control could achieve very similar functionality with an appropriate report, but I think there were other factors involved. The control allowed a form of drill-down, but required a field to be provided giving the amount of indentation. The idea being that if the indent amount on a particular row was 2, then this could be expanded to reveal all the rows that had an indent of 3. The person asking wanted a query that would provide all the information in one hit, including aggregated data at appropriate levels, and a field indicating the indent amount.

The ROLLUP operator went half way to achieve this. Using ROLLUP, you get additional rows in your resultset providing aggregated values. The example in SQL Books Online demonstrates this nicely.

But what many people miss is the GROUPING function. This is a function which takes a column and returns either 0 or 1, according to whether the row being returned is aggregating across that column or not. The example of ROLLUP above demonstrates it being used in a CASE statement.

Even fewer people realise that the GROUPING function actually returns a tinyint type, rather a bit type, and you can add tinyints easily. This gives a big clue to my indentation column. By adding the GROUPING functions together like this:

GROUPING(col1) + GROUPING(col2) + GROUPING(col3)

–you can tell how summarised the data is. Subtract this from a number like 3 (for 3 grouping columns), and it becomes the amount of indentation. Then in the ORDER BY clause, use the GROUPING functions again, and you can have the aggregate row, followed by the detail rows. Your query and results then look something like (altering the example in SQL Books Online):

            ELSE ISNULL(Item, 'UNKNOWN')
       END AS Item,
       CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
            ELSE ISNULL(Color, 'UNKNOWN')
       END AS Color,
       SUM(Quantity) AS QtySum,
       2 – (GROUPING(Item) + GROUPING(Color)) as Indt
FROM Inventory

Item                 Color                QtySum                       Indt
——————– ——————– ————————–   ——–
ALL                  ALL                  658.00                       0
Chair                ALL                  311.00                       1
Chair                Blue                 101.00                       2
Chair                Red                  210.00                       2
Table                ALL                  347.00                       1
Table                Blue                 124.00                       2
Table                Red                  223.00                       2

–And this happened to suit the control perfectly! Simple I know – but easy to overlook.

Other uses could easily be to use the SPACE() function with your Indt column, to provide a number of spaces for a flat text file.

Sidebar monitoring of SQL databases

A while back I discussed using the Windows Vista Sidebar to display useful information about applications. This led to other conversations, including one with fellow SQL MVP (and Leeds United fan – sorry to hear about the 15 points, mate) Jamie Thomson about monitoring important SQL database statistics using sidebar gadgets. I didn't give it much thought, because I still had my PowerGadgets solution in place, monitoring all kinds of things based on my own queries. (Personally, I like using 'union all' queries. This lets me get a nice collection of numbers for showing in a graph. But I also find that I can easily have a number produced for displaying in a gauge.)

But Jamie has taken this further, and produced a Sidebar gadget to monitor various database statistics. It doesn't use PowerGadgets, so it's completely free. You can grab it from his blog at, or the original version from Great work, Jamie!

Thoughts on TechEd

I enjoy TechEd. It's always good fun. Last week was my fourth TechEd, having attended for the past three years, plus in '99. The last three years have seen my involvement increase each time. Last year I proctored with the Hands-On Labs, and this year I presented both a session and an Instructor-Led Lab. I'm a big believer in giving back to the community, and I see this as part of it.

I do find that the more I'm involved, the fewer sessions I actually get to attend. But I don't feel like I'm missing out, because it's not why I go to TechEd. I go to meet people, to help people with questions, see old friends, and to be a part of the big event. In '99, I went because my employer sent me. I went to learn things, and I did. In the last few years though, my approach has been quite different, and I think I've got more from the event. This year, as a speaker, I was able to mix with a slightly different crowd, but also had an entirely different Ask The Experts experience to previous years.

There's something about community – the more you give to it, the more you feel a part of it, and the more you want to give. If you're reading this, you should get involved in your local user-groups (or even think about starting one, like the Hobart-based Datawise guys).

Only 71 people have MCITP:BI

Mitch Wheat sent me this. According to, there are only 71 people in the world (well, at the end of June) who have achieved MCITP:BI. This means passing the two exams 70-445 and 70-446. You could be one if you use SSIS, SSAS, SSRS and do a bit of Data Mining. Doing some of the courses like the ones offered by Solid Quality Learning might help too, of course.

Bloggers' Lunch at TechEd AU

TechEd is always a great event. Today I've helped with an Instructor-Led Lab on Report Builder, attended a few sessions, helped plenty of people with questions, and participated in the Bloggers' Lunch.

This was a panel that Frank Arrigo hosted, involving five people from the blogging space. It was interesting, but I had to leave early.

I did get to ask a question about what they saw as the difference between 'proper' journalism and blogging. The panelists generally agreed with my thoughts that the main difference was the responsibility that journalists have. Bloggers (including me) have no responsibility over what they write. They can write whatever they want, flaming people as much or as little as they like. On the other hand, journalists tend to represent their newspapers and their professional reputation, and therefore need to be more careful about what they say.

But then someone who blogs on behalf of their organisation would seem to be somewhere in between. And then I think the difference between bloggers and journalists is the skill. Journalists tend to study journalism, and make a career of it. So I think you're going to find that in general, journalists write better posts than non-journalists. But perhaps things are changing.

July CTP for SQL Server 2008 – includes date & time

This is excellent news. I've been looking forward to these features for ages. Because I live in a place that's on the half hour (Adelaide is GMT+0930) and has daylight savings opposite to the northern hemisphere (spending Christmas ahead an hour, not July), I've been particularly keen on the new datetimeoffset type.

There is a great page showing which features have been added to this CTP, at And you can download the latest CTP from

Considering that the product release date is set to February 27, 2008 – less than 7 months away, I want all the features to appear as soon as possible, so I can present on them to my user-group, at Code Camps, etc.