Category Archives: 2656

Dangers of BEGIN and END

I’ve presented this material at three conferences recently, so it’s about time I wrote a blog post on it…

As programmers, we love modularisation – even in the SQL space. We make stored procedures, views, and functions to encapsulate our code. This improves maintainability, simplifies the development experience, and is generally useful.

But there’s a time when it’s a bad thing for SQL Server.

There’s an amazing component of SQL Server called the Query Optimizer (I always want to write Optimiser, but I’m assuming it’s a proper noun and putting up with the US spelling). When we write queries in T-SQL, it’s the Query Optimizer that works out how to actually run the query. It works out what indexes can be used to improve performance, what order tables (well, indexes and heaps) should be accessed, how to perform the joins, and so on. I find that a rough appreciation of the power of the Query Optimizer can really help query writers.

For example, the Query Optimizer will translate a correlated sub-query in the SELECT clause into a LEFT OUTER JOIN, so that you don’t have to. It will also work out when joins can be rendered pointless and thereby removed from the plan altogether. If you let these principles help you in your query design, you can see significant benefits. It also helps you write queries that are easier to maintain, as there’s little point in trying to be clever by writing a query in a different way if the Query Optimizer will handle it in the same way as before.

If you use a view in another query, the definition of the view is used in the query as if you had written it with a sub-query. A view is simply that – a stored sub-query. They are sometimes referred to as ‘virtual tables’, but I disagree. They are stored sub-queries. Sure, the analogy falls down when you start considering indexed views, but on the whole, a view should be seen as a stored sub-query. The Query Optimizer takes the view definition, applies it in the second query, simplifies it where possible, and works out the best way of executing it. If you’re only interested in a couple of columns out of the view, the Query Optimizer has an opportunity to take that into consideration.

Stored procedures are different. You can’t use a stored procedure in an outer query. The closest you can get to this is to use OPENROWSET to consume the results of a stored procedure in an outer query, but still the whole procedure runs. After all, it’s a procedure. A set of T-SQL commands, not a set of queries. I see the clue to this as the BEGIN and END that stored procedures generally use. I like stored procedures, but I do get frustrated if they’re returning more information than I need, since I have no way of letting the system know that maybe it doesn’t need to do as much work.

Functions are in between, and come in two varieties. A function can be inline, or it can be procedural. I don’t think you find this differentiation in many places – and normally people talk about this particular drawback as being associated with Scalar Functions as compared to Table-Valued Functions, but the problem is actually one of simplification.

An inline function must be a table-valued function at this point in time. It takes the form:

CREATE FUNCTION dbo.fnFunctionName(<paramlist>) RETURNS TABLE AS
RETURN
( SELECT …. );

It is always this form, with a sub-query enclosed in a RETURN statement. It can return many columns and many rows, but the definition of the table is implied by the SELECT clause. This is essentially a view that can take parameters.

The other form is one that involves BEGIN and END. Scalar functions (unfortunately) require this (but hopefully one day will not).

CREATE FUNCTION dbo.fnFunctionName(<paramlist>) RETURNS int AS
BEGIN
RETURN ( … )
END;

As the RETURN statement is enclosed between a BEGIN and END, it can be preceded by other statements, used in working out what value should be returned.

Table-valued functions can use BEGIN and END, when multiple lines are required to calculate the rows in the table being returned.

CREATE FUNCTION dbo.fnFunctionName(<paramlist>) RETURNS @table TABLE (<fields>) AS
BEGIN

RETURN
END;

In this kind of function, the table variable is populated with data, and returned to the outer query when the RETURN command is reached.

But when the Query Optimizer comes across a procedural function, it cannot simplify it out and executes the function in a different context.

The execution plan will report that the cost of running the function is zero. But it’s lying. The way to see the impact of the function is to look in SQL Profiler, where you’ll see potentially many calls to the function, as it needs to work out the result for each different set of parameters it’s passed. The pain can be quite great, and you will never have noticed if you just look at the Execution Plans.

The moral of the story is to make sure that your functions are able to be simplified out by the Query Optimizer. Use inline table-valued functions even in place of scalar functions. You can always hook into them using CROSS/OUTER APPLY in your FROM clause, or even use them in your SELECT clause (not “SELECT Claws” – that would make it related to my company LobsterPot Solutions, and “SELECT Claus” is just a bit Christmassy) using a construct like SELECT (SELECT field FROM dbo.fnMyTVF(someParam)) …

Consider the Query Optimizer your friend. Study Execution Plans well to look at how the Query Optimizer is simplifying your query. And stay away from BEGIN and END if possible.

More SQL Conferences coming up, including SQL Bits and SQL Down Under

I know I won’t be there, as I’m a million miles away in Australia, but being from the UK myself, I always have an interest in the UK SQL community and in particular, events like SQL Bits.

This is the fifth SQL Bits conference, and they keep getting larger and larger. I’ve heard it’s now the largest SQL-focussed event in Europe. It’s going to be in South Wales (that’s OLD South Wales, not New South Wales), in November. I’m sure the area is lovely, good beaches ‘n all that… but considering it’s late November in Wales, I think you’ll be going for the SQL content, not the scenery.

Of course, if you are in New South Wales, then you ought to be thinking slightly earlier, in particular, the second weekend in October. The third SQL Code Camp is being held in Wagga, with many regular speakers (like myself) and quite a few new ones too.

These two events are clearly the significant SQL events in the last quarter of the year. I’m sure no-one cares about SQL PASS, after all. (I do wish I was going to this one, but I won’t be. I plan to go one year, but I was in the US that week last year, and I don’t plan to be away from home for two birthdays in a row. Maybe next year. It is the biggest SQL event in the world, with great speakers from everywhere, including many good friends of mine.)

No matter where you are in the world, there are SQL events that you should be going to. Professional development is really important for your career, and you shouldn’t neglect it. That being said, make sure you find me at TechEd Australia.

SQL Down Under talks, and England wins

This weekend is getting better and better. With England winning in both the football and the rugby, my mood is pretty good anyway – despite having to give the first presentation of Sunday morning at the SQL Down Under Code Camp. The local TV channel sent cameras just after I finished, so I think I managed to avoid having my presentation on the local news. At least, I hope they came just after I finished… otherwise I didn’t notice them lurking in the corner.

I taught the crowd about MERGE & Table-Valued Parameters, and got good feedback from various members of the audience. Hopefully people will be able to take the content and use it to try things out in SQL2008 when they get back to the real world.

Now, Grant Paisley is presenting about Analysis Services Best Practices. I’ve heard Grant give this talk before, but it’s still good. With Darren Gosbell, Greg Linwood and Kevin Kline still to come today, it’s going to be good. Poor France though… with Scotland beating the Ukraine in their Euro 2008 qualifiers, Les Blues need to win both their remaining matches to qualify for the tournament, and Scotland probably only need to a draw against Italy to go through.

SQL Down Under Code Camp begins

Two days of intensive SQL Server training, thanks to experts from around Australia and Kevin Kline from the US. All the Australian SQL Server MVPs are coming (five here so far, two more coming soon).

The coffee is great (thanks Peter), the company is great, and there are so many more people here (than last year) because there is also a Security Camp on at the same time. All the user-group leads are here, including Perth and the new Hobart group (Jason Cook‘s getting this going), which means every Australian state is represented.

I’m sure other people will be blogging about this too, so keep your eye out for them. I’ll try to blog more later on today.

Free SQL training at events in the UK and Australia

…and I’m not even referring to the User Groups which run regularly. The ones I’m referring to are SQLBits and the SQL Down Under Code Camp.

SQLBits was in the UK last weekend, and was a massive success. I would’ve loved to have been able to attend, but it’s a bit far to travel (I guess about 12000 miles). They had over three hundred attend, which is fantastic! Adelaide User Group regular Martin Cairney was there, and presented the talk he gave in Adelaide earlier this year. It seems to have been received well.

And this coming weekend is the SQL Down Under Code Camp in Wagga Wagga. It should be a great event, with well over a hundred people there. If you’re able to get to Wagga, I thoroughly recommend it.

Code Camps galore

We all know that Adelaide hosted Code Camp SA recently – it was a great success, and some people even wished I was there!

TechEd is coming up of course, but now there are two code camps scheduled for October, on the same weekend (13-14) and at the same venue! Yes, that place is Wagga Wagga – one Wagga for each event.

Firstly, and most importantly I’m sure, is the second SQL Down Under Code Camp. But the other one is the Security Camp Oz. With me doing the SQL Security talk at TechEd this year, I’m sure I’ll have a good reason to attend both!

Also in October, but the weekend before, and in the UK, the SQL community is hosting SQLBits. These guys have three streams (Dev, DBA, BI), and promises to be a fantastic event. I only wish I could be there. I’m sure Tony, Simon, Jamie, Jasper and Chris will do a fantastic job.

Seems wherever you are, October will be a big month for training.

On learning (Calvin and Hobbes, the ACS and digital natives)

Digital natives learn differently. How do we take advantage of that?

The funny thing is that right away, I’ve written this from an external perspective, when I’m probably in a mixture of both camps. Feel free to consider me in either camp as you read this.

At TechEd Australia this year, the keynote was from Anne Kirah. She talked about the concept of the digital native. That’s someone who has grown up in a technology culture, and therefore thinks differently to someone who has grown up in a non-technology culture and come into it. I was born in late 1974, and I grew up without being surrounded by technology, although at the age of 8 or 9 I got a C64 and started writing code, doing my homework on a computer, thinking about ways to use a computer in better ways, for better purposes. I’ve never really considered myself a geek, because I’m actually far more interested in people (although not how to use people in better ways for better purposes, <insert evil laugh> well, not really). But I do find the cross-over between technology and the rest of the world fascinating. I certainly enjoyed Anne’s talk a lot.

I’m also a fan of learning. If you have read my blog in the past, then you will appreciate that I have done a lot of Microsoft Certifications, I run a user-group, I have a few degrees, I’m generally a big fan of the whole learning experience.

But more than being addicted to learning, I’m very interested in the study of learning. My wife did part of a Bachelor of Teaching, and I really enjoyed having conversations with her about the different things that enabled or hindered a child’s ability to learn. Now, several years later, we have our two sons at a boys’ school, because we appreciate that boys learn very differently to girls, and that teachers seem to be far more able to cater for the boys in the class if there aren’t a bunch of girls in the class, accentuating the differences between the two.

Just as there is a difference between the way that boys learn compared with the way that girls learn, there is a difference between the way that ‘kids these days’ learn. And when I say ‘kids these days’, I largely mean ‘digital natives’.

On Thursday morning, I saw this Calvin and Hobbes cartoon strip. I apologise if I’m breaking copyright by posting this here – but hopefully you’ll all be inspired by this go and buy a book of C&H – it’s well worth the investment.

ch951025

Just like Calvin, digital natives hate the idea of sitting in school. I’m the same, with some differences. I love being in a learning environment. I’m very happy to go to a lecture. But I need to be able to ask questions. If I can’t have the learning experience be more like a conversation, I’m frustrated. People who were at the SQL Code Camp in Wagga Wagga earlier this month will have seen evidence of this.

At the ACS SA Branch AGM on Wednesday night, we heard Dr David Lindley talk about some of what he does in the Professional Development programs that the ACS run. He talked about the fact that the system they find very effective is to have people post opinions on matters, and then once everyone has submitted their ideas, the ideas become public (within the group) and people discuss them. Sounded like a blog to me, but David suggested that the differences are massive because the initial feedback on the post is from an appointed mentor, not the wide community. I really liked his opinions on learning through discussion though. I think the opportunity to learn is primarily through the discussion, rather than through submitting thoughts on a matter and then having them ‘marked’ by a ‘mentor’. I think a mentor should guide learning, but not necessarily teach. But more on this later.

A quick point about blogging. I think there’s a massive benefit to blogging from a learning perspective. When you write your ideas down, they solidify much more in your head. This is partly down to the principle that when you teach someone something, you have to know it so much better, but it’s more just that in writing it down, you see things from a different part of your head to when it was just a thought. But there’s more opportunity to learn from blogs, as I’ll write later.

Many digital natives are finding themselves getting into the IT space without first going through the university system. They grok computers already, and can’t see the relevance of sitting in lectures to learn things that may not be relevant to their careers. Their opinions about learning is based on what they know from school, and it’s just not cutting it (I can make similar arguments for God and the church – you don’t need to get me started on that to be able to quickly see the parallels). If you were to suggest that they enter any kind of formalised learning program, they’d laugh. These people are even against Microsoft Certifications, because they have become so prejudiced against learning because of school.

Let’s address Calvin’s problem first. He wants an environment where he doesn’t have to learn anything. Where there’s no teacher and no other kids. I’m sure when Bill Watterson wrote this strip, he wanted to list every aspect of school. His point would have been “Calvin just doesn’t want to go to school”. But we see elsewhere that Calvin is interested in learning. He asks his dad questions (although his dad doesn’t give him the right answers), and talks about quite deep things with Hobbes (who of course is his imaginary friend).

Perhaps the fact that Calvin’s dad gives him the wrong answers is part of the reasoning behind David’s consideration that the primary feedback should be from a trusted mentor. I offered to be a mentor in David’s program, but apparently I’m too young (I’ll be 32 in early November). More on this later too.

As someone interested in making sure that Calvin is able to learn effectively, we need to find a way of having him learn without being at school. I’m not saying that home-schooling is the answer for kids, I’m just saying that learning cultures are changing and this needs to be addressed.

Paul Stovell is a good friend of mine. In some ways, I mentor him. I learn a lot from him too. He has just turned 20. He will never go to university (he’s actually not opposed to the idea, he just can’t see the relevance). But he’s starting to realise the power of blogging, as he writes in his article at http://www.paulstovell.net/Posts/Post.aspx?postId=b07d1424-ae9a-40e6-881a-d22fc28de646

Paul has found that if he writes on a topic, the community of his peers who read his blog comment on it, tell him where he’s wrong, expand on his ideas, and together, they all learn something. Naturally, this being open to the entire internet, there is a risk of people writing rubbish. But the opinions that Paul values more than the others are the ones to which he pays the most attention (and typically, these people are slightly more experienced than him, but within a similar culture, rather than being people who are necessarily older and wiser – useful mentors, but perhaps not the types of people who would be a traditional choice of mentor). Of course, by writing in the public domain, you also have the opportunity to release your thoughts to the people who are the experts in the field, and this then present an even bigger opportunity for mentoring.

So Paul has a way of learning without going to school. Of course, it’s a learning environment that he’s driving himself, but Paul could just as easily become part of a learning environment that was slightly more structured, in that it suggested discussing particular points.

This is more like what David is doing. He facilitates discussions about the topics, guiding people in what they need to be learning.

You see, IT present the opportunity to allow people to learn in a manner which suits them. I think David could take it much further again, but there is a risk that fall into the trap that many home-schooling parents find themselves in – that much of the syllabus can get missed.

Developing a learning culture for digital natives (which would include many of the highly skilled people in IT) is a massive challenge. I love that the ACS is trying to find ways to address this, and if I can help them develop their ideas, then I will do so. Microsoft Learning are also trying to address it, with a move towards e-learning, away from instructor-led courses.

The biggest opportunity here is that the IT Industry is full of people who have been digital natives longer than anyone else. I don’t mean people in their 50s, I mean people in their 20s and 30s. If we can work out how to teach these people (including myself), then perhaps the rest of the education industry can see what we are doing and apply the same to non-IT learning. Kids learn history by playing computer games already, but there needs to be more to it than that, so that they realise they are learning and can start to love the learning process.

The fact is that digital natives won’t do school. But they still want to learn. If we want to be a part of that, we need to reinvent school. The burden is on us, because traditional learning cultures have hurt education significantly.

PS: This doesn’t cover anything about assessment, such as the concept of MS Certification exams – that’s a whole nother topic as well.

SQL Code Camp

Well, here I am at Wagga Wagga. It's Sunday morning, and Itzik Ben-Gan is speaking. He's just shown us slides of the SQLHike trip, which looks like a really cool idea.

Being here in the middle of nowhere talking about SQL with people is really fun. My talk (on the OVER() clause) went okay yesterday. I was last up and had to keep it short, but I got good feedback from people. People liked it when I jokingly told Itzik to just put his hand up if he had any questions. I must check out Itzik's talk on row_number() some time – I'm sure I'd learn plenty, and be able to make my talk even better.