Rob Farley : wagga

Dangers of BEGIN and END

Rob Farley — Sat, 05 Dec 2009 11:32:47 GMT

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

Rob Farley — Thu, 27 Aug 2009 12:06:33 GMT

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

Rob Farley — Sun, 14 Oct 2007 00:21:22 GMT

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

Rob Farley — Sat, 13 Oct 2007 00:27:06 GMT

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

Rob Farley — Wed, 10 Oct 2007 12:42:56 GMT

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

Rob Farley — Sat, 21 Jul 2007 10:32:28 GMT

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)

Rob Farley — Fri, 27 Oct 2006 01:52:00 GMT

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 more)

Slides and scripts on the OVER clause presentation

Rob Farley — Mon, 16 Oct 2006 01:54:00 GMT

I'll write more about this when I get the chance. But in the meantime, if you want the slides and scripts from my presentations in Wagga and at AdSSUG last week, then get them from http://msmvps.com/files/folders/robfarley/entry180018.aspx . It was on Ranking Functions and Windowing, or on the uses of the OVER clause, etc.

SQL Code Camp Talks

Rob Farley — Sun, 08 Oct 2006 02:13:00 GMT

All the talks this weekend have been great. Almost every talk has a bunch of things that I really hadn't appreciated before. All 5 SQL MVPs from Australia are here, plus Itzik (from Israel) and a few non-SQL MVPs from Australia.

I would've liked there to have been a few more people here from my user-group in Adelaide, but I do appreciate it's a long way to come. There are two, plus me.

SQL Code Camp

Rob Farley — Sat, 07 Oct 2006 23:29:00 GMT

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.