I don't tend to find that anything about me gets described as "Fabulous". It's not a word I ever use myself, so I was slightly amused to see it here.


It's going to be an interesting week (in May, Monday 14th to Saturday 19th), in which I have two stints in classrooms (the three day course in downtown Chicago and this pre-con), plus two presentations at the main SQLSaturday event. I'll be in a room freshly vacated by Jes Borland (I suspect you'll probably be still able to smell the 'squee'), and from the look of things, I'll be delivering a solid 2.5 hours of material, with an intermission of 15 minutes. Mind you, with people in the other rooms like Argenis, Ted and Erin, I'm not sure I'll have much of a crowd. It might be more like "An Intimate Afternoon with Rob Farley" with whoever's left.

The course will be the highlight of my week. I love teaching this course – it's a great time to be able to get people in a room for a few days and go through ways to make queries better. More effective. It has "Advanced T-SQL" in the title, but I really try to focus on the "Effectiveness" aspect. Yes, we'll look at a bunch of advanced features, and your T-SQL arsenal will grow, but the idea is to arm you with the information you need to be able to have more effective T-SQL. Advanced is only better when it's more effective. It's going to be a really fun few days, as I stretch your thinking and make you look at T-SQL in a new way.


The pre-con is going to be very different. We'll be going through the syllabus of the new 70-461 exam, teaching you about all the ins and outs of the various features, leaving you in a position to be able to confidently take the exam. I've sat this exam in beta, but of course I can't use any inside knowledge I gained from that to teach this. There's a lot of stuff to get through. Each of the four sections has four or five bullet points underneath, and even more sub-points under that. We'll be pushing through a lot of things, and a lot of the more basic stuff will certainly be skimmed through – but we'll be looking deeper into a lot of the new things, and making sure that you get all the concepts on the exam. I can't offer a proper guarantee that you'll pass – some people just take exams badly. But as we'll also be looking at a bunch of exam technique aspects, I think you'll be fine.

The two sessions that I'm doing at the SQLSaturday #119 are two of my favourite talks.

One of them is on SARGability. I remember doing this talk with a bunch of MVPs in the room (and a certain cloudy Microsoft employee), and even they said "Ooh – I didn't know that" afterwards. SARGability – the ability to use indexes effectively – is such a significant aspect of querying, and a pet topic of mine (as regular readers will realise). SQL Server even provides a bunch of methods you can use to improve the SARGability, even if you can't tweak the queries themselves. Very cool stuff. And did I mention I won't have slides?

The other is on Analytic Functions (a talk which I'm currently doing around Australia and New Zealand at SQLSaturday 135, 136, 138, 139 and 140). There are eight new Analytic Functions in SQL 2012, plus some new enhancements to the OVER clause. I'll be running through these, and I'm sure you'll leave the room with new ideas to try to enhance your reporting and data analysis.

See you there?

Adelaide's SQL Tuesday

This coming Tuesday sees a midweek SQL Saturday hit Adelaide. LobsterPot's a sponsor, as are a bunch of other companies.

An all day event, with two tracks featuring some of the best material you've ever seen presented. I'm presenting too.

The thing I really want to draw your attention to is that we have two sessions from Paul White. If you haven't heard of Paul, click the link to have a look at his blog. When you've picked your jaw up and some of the mind-blowing information he likes to write about, imagine yourself sitting in sessions by him. I've just got back from Wellington where I heard him for an hour, and knew that giving him two sessions was completely the right choice. Everyone left the room wishing that he could've gone on longer, and I suspect Paul will be somewhat caught up for questions for the rest of the day, as people try to pick his brain about some of the Query Optimizer things he can teach. He's been scheduled for the morning so that attendees can have plenty of opportunity to see him around for the rest of the day. It's his first time ever to Australia, so it's completely brilliant to have him come to Adelaide for this event.

I shouldn't suggest that the other sessions won't be excellent though. Peter Ward's session about the $10000 question has been very well received at SQLSaturday events so far, as have the sessions by Roger Noble and Paul te Braak. Combining these with Microsoft's Raja N presenting about the Database Consolidation Appliance, some excellent local speakers, a terrific sponsor-session from Fusion-io (and a couple of spots from me), and I'm sure you'll agree that this event is definitely worth getting to.

Numbers are limited and being a free event it may well sell out. So get yourself registered (but I'd recommend joining PASS first, which gives you a bunch of extra benefits and there's no extra effort involved), and I'll see you there.

It's in less than a week!

Analytic functions – they're not aggregates

SQL 2012 brings us a bunch of new analytic functions, together with enhancements to the OVER clause. People who have known me over the years will remember that I'm a big fan of the OVER clause and the types of things that it brings us when applied to aggregate functions, as well as the ranking functions that it enables.

The OVER clause was introduced in SQL Server 2005, and remained frustratingly unchanged until SQL Server 2012.TSQL2sDay150x150

This post is going to look at a particular aspect of the analytic functions though (not the enhancements to the OVER clause). When I give presentations about the analytic functions around Australia as part of the tour of SQL Saturdays (starting in Brisbane this Thursday), and in Chicago next month, I'll make sure it's sufficiently well described. But for this post – I'm going to skip that and assume you get it.

The analytic functions introduced in SQL 2012 seem to come in pairs – FIRST_VALUE and LAST_VALUE, LAG and LEAD, CUME_DIST and PERCENT_RANK, PERCENTILE_CONT and PERCENTILE_DISC. Perhaps frustratingly, they take slightly different forms as well. The ones I want to look at now are FIRST_VALUE and LAST_VALUE, and PERCENTILE_CONT and PERCENTILE_DISC.

The reason I'm pulling this ones out is that they always produce the same result within their partitions (if you're applying them to the whole partition).

Consider the following query:

This is designed to get the TotalDue for the first order of the year, the last order of the year, and also the 95% percentile, using both the continuous and discrete methods ('discrete' means it picks the closest one from the values available – 'continuous' means it will happily use something between, similar to what you would do for a traditional median of four values). I'm sure you can imagine the results – a different value for each field, but within each year, all the rows the same.

Notice that I'm not grouping by the year. Nor am I filtering. This query gives us a result for every row in the SalesOrderHeader table – 31465 in this case (using the original AdventureWorks that dates back to the SQL 2005 days).

The RANGE BETWEEN bit in FIRST_VALUE and LAST_VALUE is needed to make sure that we're considering all the rows available. If we don't specify that, it assumes we only mean "RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW", which means that LAST_VALUE ends up being the row we're looking at.

At this point you might think about other environments such as Access or Reporting Services, and remember aggregate functions like FIRST. We really should be able to do something like:

But you can't. You get that age-old error:


You see, FIRST_VALUE isn't an aggregate function. None of these analytic functions are. There are too many things involved for SQL to realise that the values produced might be identical within the group.

Furthermore, you can't even surround it in a MAX. Then you get a different error, telling you that you can't use windowed functions in the context of an aggregate.

And so we end up grouping by doing a DISTINCT.


I'm sorry. It's just the way it goes. Hopefully it'll change the future, but for now, it's what you'll have to do.

If we look in the execution plan, we see that it's incredibly ugly, and actually works out the results of these analytic functions for all 31465 rows, finally performing the distinct operation to convert it into the four rows we get in the results.


You might be able to achieve a better plan using things like TOP, or the kind of calculation that I used in http://sqlblog.com/blogs/rob_farley/archive/2011/08/23/t-sql-thoughts-about-the-95th-percentile.aspx (which is how PERCENTILE_CONT works), but it's definitely convenient to use these functions, and in time, I'm sure we'll see good improvements in the way that they are implemented.

Oh, and this post should be good for fellow SQL Server MVP Nigel Sammy's T-SQL Tuesday this month.

How views are changing in future versions of SQL

April is here, and this weekend, SQL v11.0 (previous known as Denali, now known as SQL Server 2012) reaches general availability. And so I thought I'd share some news about what's coming next. I didn't hear this at the MVP Summit earlier this year (where there was lots of NDA information given, but I didn't go), so I think I'm free to share it.

I've written before about CTEs being query-scoped views. Well, the actual story goes a bit further, and will continue to develop in future versions.

A CTE is a like a "temporary temporary view", scoped to a single query. Due to globally-scoped temporary objects using a two-hashes naming style, and session-scoped (or 'local') temporary objects a one-hash naming style, this query-scoped temporary object uses a cunning zero-hash naming style. We see this implied in Books Online in the CREATE TABLE page, but as we know, temporary views are not yet supported in the SQL Server.


However, in a breakaway from ANSI-SQL, Microsoft is moving towards consistency with their naming.

We know that a CTE is a "common table expression" – this is proving to be a more strategic than you may have appreciated.

Within the Microsoft product group, the term "Table Expression" is far more widely used than just CTEs. Anything that can be used in a FROM clause is referred to as a Table Expression, so long as it doesn't actually store data (which would make it a Table, rather than a Table Expression). You can see this is not just restricted to the product group by doing an internet search for how the term is used without 'common'.

In the past, Books Online has referred to a view as a "virtual table" (but notice that there is no SQL 2012 version of this page). However, it was generally decided that "virtual table" was a poor name because it wasn't completely accurate, and it's typically accepted that virtualisation and SQL is frowned upon. That page I linked to says "or stored query", which is slightly better, but when the SQL 2012 version of that page is actually published, the line will be changed to read: "A view is a stored table expression (STE)".

This change will be the first of many. During the SQL 2012 R2 release, the keyword VIEW will become deprecated (this will be SQL v11 SP1.5). Three versions later, in SQL 14.5, you will need to be in compatibility mode 140 to allow "CREATE VIEW" to work. Also consistent with Microsoft's deprecation policy, the execution of any query that refers to an object created as a view (rather than the new "CREATE STE"), will cause a Deprecation Event to fire. This will all be in preparation for the introduction of Single-Column Table Expressions (to be introduced in SQL 17.3 SP6) which will finally shut up those people waiting for a decent implementation of Inline Scalar Functions.


And of course, CTEs are "Common" because the Table Expression definition needs to be repeated over and over throughout a stored procedure.

…or so I think I heard at some point. Oh, and congratulations to all the new MVPs on this April 1st.