Rob Farley : sql, fun

T-SQL Tuesday - HAVING Puzzle answer

Rob Farley — Tue, 12 Jan 2010 13:59:00 GMT

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

Rob Farley — Tue, 12 Jan 2010 00:01:00 GMT

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;

Things You Know Now

Rob Farley — Mon, 16 Mar 2009 23:44:00 GMT

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