Category Archives: 2745

T-SQL Tuesday – HAVING Puzzle answer

Earlier today you may have seen a blog post of mine about a puzzle involving HAVING. You should read that post before this one. It was part of Adam Machanic’s T-SQL Tuesday meme.

The question was about the query:

SELECT ‘No Rows’
WHERE 1=2
HAVING 1=1;

And here’s the explanation.

Start by making yourself a "dual table", like what you’d use in Oracle, and use this instead of having no FROM clause. Put a row in it.

CREATE TABLE dual (dummy bit);
INSERT dual VALUES (1);

–Now count the rows in it
SELECT COUNT(*)
FROM dual;

–Now count how many rows don’t match 1=2 (of course, the answer is zero)
SELECT COUNT(*)
FROM dual
WHERE 1=2;

–Naturally we’d get nothing back if we weren’t grouping
SELECT ‘Something’
FROM dual
WHERE 1=2;

–But HAVING forces the grouping functionality as well (like using COUNT(*))
SELECT ‘Something’
FROM dual
WHERE 1=2
HAVING 1=1;

–So in this query, we couldn’t put any of our real columns in, only aggregate functions and constants
SELECT *
–Errors
FROM dual
WHERE 1=2
HAVING 1=1;

–And leaving out the FROM clause implies that we’re asking all this of a secret internal table with a single row. All these queries work just the same without the FROM clause at all.

–Count the rows in our pretend table (one)
SELECT COUNT(*)

–Now count how many rows don’t match 1=2 (zero)
SELECT COUNT(*)
WHERE 1=2;

–Naturally we’d get nothing back if we weren’t grouping
SELECT ‘Something’
WHERE 1=2;

–But HAVING forces the grouping functionality as well
SELECT ‘Something’
WHERE 1=2
HAVING 1=1;

So the answer to the question posed is that you get a single row, containing the text provided. The fact that I used the text ‘No Rows’ was just a bit of fun.

Now, to remove the trivia a little…

When would you ever use HAVING without GROUP BY in a practical situation?

How about this:

Using sp_MSforeachdb, find the number of objects in non-system databases. It’s an undocumented system stored procedure which runs a query on each database, replacing a question mark in the query with the name of the database. It can be quite handy, just don’t look at how it’s implemented.

EXEC sp_MSforeachdb ‘SELECT ”?”, COUNT(*) FROM ?.sys.objects WHERE ”?” NOT IN (”master”,”tempdb”,”model”,”msdb”);’;

But this won’t do it. It will still return the entries for the system databases, but with zeroes (because none of the objects satisfied the WHERE clause). Replace WHERE with HAVING and it’s just fine – the rows get eliminated from the resultset.

EXEC sp_MSforeachdb ‘SELECT ”?”, COUNT(*) FROM ?.sys.objects HAVING ”?” NOT IN (”master”,”tempdb”,”model”,”msdb”);’;

Honestly, HAVING doesn’t require a GROUP BY clause. It doesn’t require anything. It filters based on groups, and if there are no groups yet, it makes some – like how using an aggregate will count the rows in an empty set and return one row representing that group.

It’s generally taught as "HAVING is for filtering based on aggregates", and that’s true, but only half the story. And I find that if I’m teaching people to write better queries, I want them to have a thorough understanding of what each construct is really doing.

T-SQL Tuesday – T-SQL Puzzle with HAVING

Adam’s hosting another T-SQL Tuesday, for which this post is jumping in. He’s themed it around T-SQL Puzzles, which I found quite interesting, because the world is full of them.

Most of the questions that I answer on forums, help sites, and so on, are puzzles. I guess there’s the difference between “Problem” and “Puzzle”, but I prefer to think of thing as puzzles.

For Adam’s meme though, I thought I’d share a Puzzle that I ask students who take my Advanced T-SQL course. The idea is to have them start thinking about what each component of T-SQL is actually doing, so that they can better address problems they face. If you have a rifle, it’s nice to actually know what the various components of it are for, so that you can use it more effectively.

I actually ask them a large number of things, but the one that I thought I’d pose for you all today is about the results of this. The answer will be in the next blog post, which hopefully you haven’t read yet. I will have them both published on Tuesday 12th, this one at the start of the day, and the answer towards the end of the day.

The question is simply a query. Can you predict the output, and explain why? Feel free to comment to your heart’s content, as I will moderate them and only publish them afterwards. In fact, I’ll probably take a few days to get to them (being holiday period), so I apologise if you’re wanting to read what other people thought too.

Naturally, you can check your answer by actually running the query, but please provide your thoughts before you do. The query is below. There is no FROM clause. There is no GROUP BY clause. Does it error, do you get an empty resultset, do you get a single row containing NULL, do you get a single row with data, do you get multiple rows, or something else I haven’t suggested? Enjoy.

SELECT ‘No rows’
WHERE 1=2
HAVING 1=1;

DeepZoom fixoutlook – and a tip on hosting SilverLight

I haven’t done much SilverLight development – I’ve dabbled at best. And I don’t really count using DeepZoom Composer as proper SilverLight, but it makes a SilverLight application.

So I’ve got one at http://www.lobsterpot.com.au/fixoutlook

The background is that there’s a group of people who feel very strongly that Outlook should use proper standards for rendering emails. I understand that Microsoft have made a conscious decision to use Word for rendering emails, but I’m certainly part of the crowd who agree that Microsoft may have got this one wrong. They made a large image, and I thought this image would made a great candidate for DeepZoom. I dropped Dave a line, and he gave me the go-ahead.

So have a look around my DeepZoom application, and in particular, zoom in at the top of the “L’’ in the word outlook, to find how much you can zoom in. Look for the white lines, and you’ll see some more zooming opportunity.

And a big thanks to John O’Brien, for reminding me that when you host a SilverLight application, you need make sure the mime types are set appropriately.

Things You Know Now

This blog meme is doing the rounds… I’ve been tagged at least twice now (Jason Strate and Greg Linwood), so I suppose subconsciously I’ve been thinking about this stuff for a few weeks already.

Since I do a lot of training, I tend to explain these things to my students anyway. I have a lot of opportunity to stand up in front of people and tell them important stuff – so this kind of thing definitely comes up now and then.

Things I wish I had known years ago (career-wise that I would teach new people in the SQL field)

The Importance of technical communities

I remember when Craig Bailey wrote about his ideal role. It wasn’t new stuff – I had heard it all before, but it certainly got me thinking about how people can influence where they are in that Venn diagram. For Craig, he wanted his ideal role to be a job that he was good at and that he enjoyed. Obviously to be a job, someone has to be prepared to pay him sufficiently too.

Being good at something you enjoy isn’t hard, and you can invest your own time (outside of the job that you don’t enjoy) developing your skills. For people in IT, I suggest they pick a particular area they find interesting, and start getting their skills up. If they can become expert-level in that area, then great.

The next problem though, is moving that skill into something that lets you can earn money. Community can help that. Community can help you develop your skills, because you’re spending time with other people in your field. But as you become an expert, presenting at community events, developing a profile, you find yourself being differentiated from the rest. If nothing else, people know you have presentation skills. Every presentation can become like a job interview – showing your skills and ability to communicate information to clients, colleagues, whoever.

Presenting isn’t easy, but there are plenty of other communities that can help develop those skills. You can get along to a ToastMasters group, or offer to do presentations in a group to which you already belong.

You might be the best in the world at what you do – but you need to get out there. I enjoy the technical communities, and run the Adelaide SQL Server User Group because I enjoy it. But I can’t deny that it’s been useful for my career. Now, I’m wishing that I had got involved many years ago.

Enjoy public speaking

According to the old saying, more people are afraid of public speaking than death (so at a funeral, they’d rather be in the coffin than giving the eulogy). But it’s a useful skill to have, so learn to enjoy it.

Keep in touch with old friends

This isn’t quite so career-related, but is actually very important for your career nonetheless.

There are people that I haven’t seen in years, who I have no idea how to contact. Facebook (and the internet in general) has proved very useful for that, but still there are many people that I wish I could find. Most of them are just people I would like to spend time with now and then, but some are people that I’d happily offer to do some work for. And perhaps some of them would contact me to do some consulting if they knew how to reach me (clue, there’s contact information to the side of my blog site!).

Far too many people fall out our lives, and it’s sad. I’m still not great at it, but I do think I should take the time to write people letters now and then (emails, Facebook comments, Instant Messages are all fine too – I’m just talking about touching base to keep the contact there).

Certifications aren’t worth studying for (but they are worth taking)

I used to study for exams. I first became a Microsoft Certified Professional back in 1998, passing an exam called “Architecture I”. Since then I’ve passed over 30 exams, and earned plenty of certifications. But a few years ago I worked out at that it’s just not worth studying for these things.

A MCP exam is not like high school or university. If you fail, you can just try again. Fails don’t appear on your transcript, only the passes do. It’s like your driving test – if you fail, you just try again. Once you pass, you get access to the roads like everyone else.

If you spend weeks studying for a MCP exam, you probably won’t even improve your chances of passing – you’ll just be spending precious family time trying to learn those things to get you past the line. You might even start losing sleep over it.

Nowadays, I tell my students (and myself) to care less. Plenty of people say “No, you don’t understand – I can’t fail at anything.”, and I understand that. I’m not particularly good with failure either. But I’ve learned to not care so much. I don’t want to waste time sitting an exam only find that I fail (or spend $180 on the privilege), but I also don’t want to waste time studying for an exam that I could probably pass anyway. With the Second Shot offer that is often around you’ve paid for two attempts, so go into the first one blind.

The amount of time you invest in getting a certification is largely the study time. So if you can reduce that, the certification becomes a lot cheaper – in which case, it’s probably worth taking the few hours to give it a try. If you do fail, you know you have a weak area, so you can improve that with study – just don’t bother studying before the first try.

[Edited: I should make it very clear that I definitely approve of learning new skills, and preparation for an exam is a great prompt for this learning. Better still is learning for the sake of getting those new skills, with the focus being an upcoming project or new role. My advice above is focussed on people who have the skills necessary to pass an exam.]

Reading execution plans, and understanding indexes

I’ve always been good at solving problems with T-SQL (or PL/SQL for that matter) – I just took to it naturally when I got into databases. But it took me several years to actual venture into understanding what the query is actually doing when it runs. Now, I look at the execution plan for every query I write, as default behaviour, and I consider the indexes that I want up front.

Perhaps it’s because I was a programmer first, but I had always trusted the compiler to do things the right way. I had looked a bit past my code when studying Prolog at university, but it took me a long time to make that my default behaviour.

So when I find people who are just getting into T-SQL, I encourage them to look at the execution plans, and start getting a feel for what’s going on behind the scenes. You can often improve a query without looking at the execution plan, but if you want to write really good T-SQL and have well-performing queries, you need to make the execution plan part of the process.

The significance of BI to businesses

I was involved in data warehouses in some of my first projects when I left university, I just didn’t realise at the time. I first got involved in SQL Server in version 6.0, and quite early on I migrated a system to 6.5, and created a data warehouse to allow for various reports. In hindsight, I was making a data warehouse. I had an ETL process, calculated aggregates, considered the dimensions and granularity, all that. But it wasn’t called a data warehouse, and I only realised a few years later that it really was one.

If I had’ve realised, then I’m sure I would’ve jumped into the BI space much earlier. Companies love BI – it’s one of the most empowering areas of database technology for any business.

 

I’ve picked a few things here – and I hope people somehow get some benefit from reading it. I have put it in my ‘must read’ list to find other people’s responses, because I’m sure there are things that I’m still to learn.

Tagging some other people: Simon Sabin, Jamie Thomson, Deepak Kapoor, Grant Paisley

Meet the Robinsons in amazing 3D

Being a parent, I went to see the new Digital 3D movie Meet the Robinsons over the weekend. The film itself is a little predictable, and not much to really blog about, but the Digital 3D is really impressive. It uses Disney Digital 3D technology  (a rebranding of Real D) like Chicken Little and Monster House before it. Now I’m wishing I did though, because the 3D technology is really impressive!

In the olden days (read ‘1980s’), 3D meant wearing red and blue glasses, so that the stuff on the screen that was coloured red could only be seen by one eye, and similarly for blue. This meant that each eye would see something different, and hopefully you’d get the 3D effect happening. But of course, the colours were never done very well, and you’d see double images, have a hard time focussing, and generally get a sore head. But nevertheless it was a trend, and comic books, TV shows, and of course movies would all be shown in 3D. Even Hitchcock made a version of “Dial M for Murder” in 3D, which I had always hoped to see at Valhalla, when it wasn’t burning down.

Real D is done differently though and uses a similar technology to the the IMAX 3D experience. IMAX 3D was another thing that I had hoped to experience, but missed out on for one reason or another. The IMAX principle was that you would watch a higher-framerate movie, wearing special glasses which had LCD lenses. The lenses would blank over each eye at just the right time, so that your eyes only saw the frames they were meant to see. No funny colours here, your eye actually sees what the camera sees. Proper 3D.

Real D also works with a higher-framerate, but the LCD bit happens at the projector. It polarises the light in two directions, and then a cheap pair of glasses with polarised lenses will do the trick. The physics behind polarisation is that light which only has waves in a particular direction can be filtered out with a lense which is polarised in the opposite direction… ie, light-waves which only go up and down can be filtered out by a lense which only allows horizontal waves through. This is why polarised sunglasses reduce glare, because glare tends to be polarised light.

The effect is incredible. The whole movie is 3D, and I found myself noticing the distance in almost every scene. Some more than others, like when the hero is going through a tunnel. But of course, this is animation. I have read that U2’s recent tour was filmed in 3D as well, and I want to see how live-action goes in 3D.

So my next thought is… when will this become available on my PC? Surely a good quality monitor could be set up with a high-framerate, and whilst it would be hard to polarise the light coming off a screen, could I get some USB connected LCD glasses and have a 3D computing experience? It could be really good for visualising data.

Cricket PowerGadget

I was thinking about PowerShell and how you can get it to do fantastic things. And I wondered how easily it could be used for scraping cricket scores.

So I threw together four lines of code to grab the cricket scoreboard from cricinfo and rip out the title.

$ret = (new-object Net.WebClient).DownloadString(“http://content-aus.cricinfo.com/ausveng/engine/current/match/249226.html?view=live;wrappertype=mainframe”)
$titlestart = [Regex]::Matches($ret,”<title>”,”IgnoreCase”)[0].Index
$titleend = [Regex]::Matches($ret,”</title>”,”IgnoreCase”)[0].Index
$ret.Substring($titlestart+7,$titleend-$titlestart-7)

Edited: This can be done easily in one line – Lars pointed out the use of Regex to grab the section between the title tags, which then means we don’t need to store $ret at all. It can now be:

[Regex]::Match((new-object Net.WebClient).DownloadString(“http://content-aus.cricinfo.com/ausveng/engine/current/match/249226.html?view=live;wrappertype=mainframe”),”<title>(.*)</title>”,”IgnoreCase”).Groups[1].Value

It’s not particularly elegant, but it works nicely. I would’ve liked to have handled the HTML as XML instead, and just gone straight to the Title tag, but there’s stuff in there that won’t convert to XML, so I guess that option wasn’t available.

And the really nice thing about this is that I can put these four lines into PowerGadgets, and in all of 10 seconds have a floating gadget which I can use in XP as well as Vista, and (in Vista) put in the sidebar if I want. I’ve told it to refresh every minute, which won’t refresh as quick as some, but hopefully won’t stop working too quickly. It’s not quite as nifty as Darren Neimke’s gadget, but then again, this was really really quick to throw together.

And of course, I’ve left the advert for Cricinfo in there. I wouldn’t want to hide the source of the information. And if they ask me not to do this, then of course I’ll stop. Cricinfo have a great site, and I really don’t want to upset them.

cricinfo

Five things

Nick tagged me. Thanks Nick. Turns out you are still here. Hmm… Five things about me you might not know.

1/ I’m a Christian (and proud of it). I have been all my life, and don’t remember not ever being a Christian. I actually come from a long line of Christians, and ‘minister’ is one of the more common career choices in my ancestry.

2/ My grandfather is a personal hero of mine – he’s a past-president of the Royal Aeronautical Society, and was Engineer of the Year in 1984, I think partly because of a few helpful passing comments he made to NASA regarding some of the problems they were having with the shuttle.

3/ Most of my high-school years were spent at a school called Habs just north of London (I figure most people know I’m actually English and that doesn’t qualify as something you don’t know), where Matt Lucas was in my year and Sacha Baron-Cohen was a couple of years ahead. It was my father’s high-school too, but he died when I turned ten and never saw me go there. Neither of us appear on the Wikipedia entry about the school.

4/ I only have one GCSE, an A in Maths. Most people have lots, but I only have one, because of the timing of our emigration to Australia. But I do have three bachelor’s degrees, which mainly comes from starting with a double degree and then doing honours in something else.

5/ The last few years have been somewhat hospital-centric for my family. I had a back injury in 2003 and spent a while in hospital learning to walk again, and in 2004 my son Samuel (who was seven at the time) got Kawasaki Disease. In many ways, both experiences shaped us more than we would have liked, but we’re both still here and doing well.

Now I need to pick five other people to tag (who haven’t already been tagged by others). In no particular order…

Geoff Orr (good friend in Sydney, knows SQL), Brian Madsen (ASP.Net MVP in Perth, runs the SQL UG there), Glenn Block (works for Microsoft Learning in Redmond), David Gardiner (member of my SQL UG in Adelaide) and Joel K. Furr (the inventor of the word ‘spam’ – lives in the US state of Vermont).

An A2-sized rugged Tablet PC in the shower

I have lots of ideas in the shower. Perhaps it’s because I’m just waking up from sleeping on stuff, perhaps it’s because the water cleans impurities out of the air, meaning that my brain gets fed a better quality of oxygen (I’ve seriously heard this theory before). But whatever the reason, I often find myself having ideas when I’m in the shower.

The most annoying thing is when that idea is the repeat of a shower that I had a few days earlier. Then I’m kicking myself for not remembering it once the day was underway properly.

And that’s where I got an idea for having a whiteboard in the shower.

I used to scuba-dive. It’s lots of fun – but you try communicating with a fellow diver when you’re underwater… it can be a pain to say the least. To get around this, divers carry slates and pencils. Pencils can write underwater you know. Not on paper of course, but on slate it’s just fine.

So I figure that I should keep a few of these slates and a pencil in the shower, so that I can make a note of ideas.

But better still would be a whole whiteboard. Thinking standing up is good for all kinds of reasons – lots of people agree with me that when you’re thinking through a problem, standing next to a whiteboard really helps. But of course, a whiteboard marker doesn’t work in the shower, and besides, you’ve got the age-old problem of not being able to transfer your ideas off the whiteboard easily.

So that’s where a large Tablet PC would be great. It would have to be a rugged one (I saw a video ages ago about a Tablet PC that you could use underwater), and it would have to be firmly attached to the wall, and connected (wirelessly?) so that I could print my ideas, email them, check the stock-prices and weather, etc… But most importantly, I could get my ideas down when they occur, think through them, even collaborate with other people (that would seem wrong I’m sure), and really make good use of that time. Naturally (no pun intended until now that I read it back) there would have to be a timer on the shower so that water-conservation didn’t become too big an issue. But it would stop that ‘forgetting time’ that occurs between when I have the idea and when I have finished the ‘getting up’ process.

Obviously it wouldn’t have a webcam!

My twin’s birthday

Today is my twin brother's birthday. He's 32, poor guy. He'll probably call me later on today, which will give me the opportunity to wish him a happy birthday. But if he reads my blog, he'll see me wishing him happy birthday here. Happy Birthday, Andrew. You're getting old!

Oh, I get it. 🙁

So, the thirty-second year is over. Felt like it took longer than half-a-minute, although in some ways it didn't. Time to reflect perhaps – I'm sick today, so maybe there's a good opportunity.