I posted a few hours ago about a reflection of the Summit, but I wanted to write another one for this month’s T-SQL Tuesday, hosted by Chris Yates.

In January of this year, Adam Jorgensen and I joked around in a video that was used for the SQL Server 2012 launch. We were asked about SQLFamily, and we said how we were like brothers – how we could drive each other crazy (the look he gave me as I patted his stomach was priceless), but that we’d still look out for each other, just like in a real family.

And this is really true.

Last week at the PASS Summit, there was a lot going on. I was busy as always, as were many others. People told me their good news, their awful news, and some whinged to me about other people who were driving them crazy. But throughout this, people in the SQL Server community genuinely want the best for each other. I’m sure there are exceptions, but I don’t see much of this.

Australians aren’t big on cheering for each other. Neither are the English. I think we see it as an American thing. It could be easy for me to consider that the SQL Community that I see at the PASS Summit is mainly there because it’s a primarily American organisation. But when you speak to people like sponsors, or people involved in several types of communities, you quickly hear that it’s not just about that – that PASS has something special. It goes beyond cheering, it’s a strong desire to see each other succeed.

I see MVPs feel disappointed for those people who don’t get awarded. I see Summit speakers concerned for those who missed out on the chance to speak. I see chapter leaders excited about the opportunity to help other chapters. And throughout, I see a gentleness and love for people that you rarely see outside the church (and sadly, many churches don’t have it either).

Chris points out that the M-W dictionary defined community as “a unified body of individuals”, and I feel like this is true of the SQL Server community. It goes deeper though. It’s not just unity – and we’re most definitely different to each other – it’s more than that. We all want to see each other grow. We all want to pull ourselves up, to serve each other, and to grow PASS into something more than it is today.

In that other post of mine I wrote a bit about Paul White’s experience at his first Summit. His missus wrote to me on Facebook saying that she welled up over it. But that emotion was nothing about what I wrote – it was about the reaction that the SQL Community had had to Paul. Be proud of it, my SQL brothers and sisters, and never lose it.

PASS Summit is coming up, and I thought I’d post a few things.

Summit Old...

At the PASS Summit, you will get the chance to hear presentations by the SQL Server establishment. Just about every big name in the SQL Server world is a regular at the PASS Summit, so you will get to hear and meet people like Kalen Delaney (@sqlqueen) (who just recently got awarded MVP status for the 20th year running), and from all around the world such as the UK’s Chris Webb (@technitrain) or Pinal Dave (@pinaldave) from India. Almost all the household names in SQL Server will be there, including a large contingent from Microsoft. The PASS Summit is by far the best place to meet the legends of SQL Server. And they’re not all old. Some are, but most of them are younger than you might think.

...Summit New...

The hottest topics are often about the newest technologies (such as SQL Server 2012). But you will almost certainly learn new stuff about older versions too. But that’s not what I wanted to pick on for this point.

There are many new speakers at every PASS Summit, and content that has not been covered in other places. This year, for example, LobsterPot’s Roger Noble (@roger_noble) is giving a presentation for the first time. He’s a regular around the Australian circuit, but this is his first time presenting to a US audience. New Zealand’s Paul White (@sql_kiwi) is attending his first PASS Summit, and will be giving over four hours of incredibly deep stuff that has never been presented anywhere in the US before (I can’t say the world, because he did present similar material in Adelaide earlier in the year).

...Summit Borrowed...

No, I’m not talking about plagiarism – the talks you’ll hear are all their own work.

But you will get a lot of stuff you’ll be able to take back and apply at work. The PASS Summit sessions are not full of sales-pitches, telling you about how great things could be if only you’d buy some third-party vendor product. It’s simply not that kind of conference, and PASS doesn’t allow that kind of talk to take place. Instead, you’ll be taught techniques, and be able to download scripts and slides to let you perform that magic back at work when you get home. You will definitely find plenty of ideas to borrow at the PASS Summit.

...Summit Blue

Yeah – and there’s karaoke. Blue - Jason - SQL Karaoke - YouTube

SQL Server Reporting Services plays nicely. You can have things in the catalogue that get shared. You can have Reports that have Links, Datasets that can be used across different reports, and Data Sources that can be used in a variety of ways too.

So if you find that someone has deleted a shared data source, you potentially have a bit of a horror story going on. And this works for this month’s T-SQL Tuesday theme, hosted by Nick Haslam, who wants to hear about horror stories. I don’t write about LobsterPot client horror stories, so I’m writing about a situation that a fellow MVP friend asked me about recently instead.

The best thing to do is to grab a recent backup of the ReportServer database, restore it somewhere, and figure out what’s changed. But of course, this isn’t always possible.

And it’s much nicer to help someone with this kind of thing, rather than to be trying to fix it yourself when you’ve just deleted the wrong data source. Unfortunately, it lets you delete data sources, without trying to scream that the data source is shared across over 400 reports in over 100 folders, as was the case for my friend’s colleague.

So, suddenly there’s a big problem – lots of reports are failing, and the time to turn it around is small. You probably know which data source has been deleted, but getting the shared data source back isn’t the hard part (that’s just a connection string really). The nasty bit is all the re-mapping, to get those 400 reports working again.

I know from exploring this kind of stuff in the past that the ReportServer database (using its default name) has a table called dbo.Catalog to represent the catalogue, and that Reports are stored here. However, the information about what data sources these deployed reports are configured to use is stored in a different table, dbo.DataSource. You could be forgiven for thinking that shared data sources would live in this table, but they don’t – they’re catalogue items just like the reports. Let’s have a look at the structure of these two tables (although if you’re reading this because you have a disaster, feel free to skim past).

Frustratingly, there doesn’t seem to be a Books Online page for this information, sorry about that. I’m also not going to look at all the columns, just ones that I find interesting enough to mention, and that are related to the problem at hand. These fields are consistent all the way through to SQL Server 2012 – there doesn’t seem to have been any changes here for quite a while.

dbo.Catalog

The Primary Key is ItemID. It’s a uniqueidentifier. I’m not going to comment any more on that. A minor nice point about using GUIDs in unfamiliar databases is that you can more easily figure out what’s what. But foreign keys are for that too…

Path, Name and ParentID tell you where in the folder structure the item lives. Path isn’t actually required – you could’ve done recursive queries to get there. But as that would be quite painful, I’m more than happy for the Path column to be there. Path contains the Name as well, incidentally.

Type tells you what kind of item it is. Some examples are 1 for a folder and 2 a report. 4 is linked reports, 5 is a data source, 6 is a report model. I forget the others for now (but feel free to put a comment giving the full list if you know it).

Content is an image field, remembering that image doesn’t necessarily store images – these days we’d rather use varbinary(max), but even in SQL Server 2012, this field is still image. It stores the actual item definition in binary form, whether it’s actually an image, a report, whatever.

LinkSourceID is used for Linked Reports, and has a self-referencing foreign key (allowing NULL, of course) back to ItemID.

Parameter is an ntext field containing XML for the parameters of the report. Not sure why this couldn’t be a separate table, but I guess that’s just the way it goes. This field gets changed when the default parameters get changed in Report Manager.

There is nothing in dbo.Catalog that describes the actual data sources that the report uses. The default data sources would be part of the Content field, as they are defined in the RDL, but when you deploy reports, you typically choose to NOT replace the data sources. Anyway, they’re not in this table. Maybe it was already considered a bit wide to throw in another ntext field, I’m not sure. They’re in dbo.DataSource instead.

dbo.DataSource

The Primary key is DSID. Yes it’s a uniqueidentifier...

ItemID is a foreign key reference back to dbo.Catalog

Fields such as ConnectionString, Prompt, UserName and Password do what they say on the tin, storing information about how to connect to the particular source in question.

Link is a uniqueidentifier, which refers back to dbo.Catalog. This is used when a data source within a report refers back to a shared data source, rather than embedding the connection information itself. You’d think this should be enforced by foreign key, but it’s not. It does allow NULLs though.

Flags this is an int, and I’ll come back to this.

When a Data Source gets deleted out of dbo.Catalog, you might assume that it would be disallowed if there are references to it from dbo.DataSource. Well, you’d be wrong. And not because of the lack of a foreign key either.

Deleting anything from the catalogue is done by calling a stored procedure called dbo.DeleteObject. You can look at the definition in there – it feels very much like the kind of Delete stored procedures that many people write, the kind of thing that means they don’t need to worry about allowing cascading deletes with foreign keys – because the stored procedure does the lot.

Except that it doesn’t quite do that.

If it deleted everything on a cascading delete, we’d’ve lost all the data sources as configured in dbo.DataSource, and that would be bad. This is fine if the ItemID from dbo.DataSource hooks in – if the report is being deleted. But if a shared data source is being deleted, you don’t want to lose the existence of the data source from the report.

So it sets it to NULL, and it marks it as invalid.

We see this code in that stored procedure.

UPDATE [DataSource]
   SET
      [Flags] = [Flags] & 0x7FFFFFFD, -- broken link
      [Link] = NULL
FROM
   [Catalog] AS C
   INNER JOIN [DataSource] AS DS ON C.[ItemID] = DS.[Link]
WHERE
   (C.Path = @Path OR C.Path LIKE @Prefix ESCAPE '*')

Unfortunately there’s no semi-colon on the end (but I’d rather they fix the ntext and image types first), and don’t get me started about using the table name in the UPDATE clause (it should use the alias DS). But there is a nice comment about what’s going on with the Flags field.

What I’d LIKE it to do would be to set the connection information to a report-embedded copy of the connection information that’s in the shared data source, the one that’s about to be deleted. I understand that this would cause someone to lose the benefit of having the data sources configured in a central point, but I’d say that’s probably still slightly better than LOSING THE INFORMATION COMPLETELY. Sorry, rant over. I should log a Connect item – I’ll put that on my todo list.

So it sets the Link field to NULL, and marks the Flags to tell you they’re broken. So this is your clue to fixing it.

A bitwise AND with 0x7FFFFFFD is basically stripping out the ‘2’ bit from a number. So numbers like 2, 3, 6, 7, 10, 11, etc, whose binary representation ends in either 11 or 10 get turned into 0, 1, 4, 5, 8, 9, etc. We can test for it using a WHERE clause that matches the SET clause we’ve just used. I’d also recommend checking for Link being NULL and also having no ConnectionString. And join back to dbo.Catalog to get the path (including the name) of broken reports are – in case you get a surprise from a different data source being broken in the past.

SELECT c.Path, ds.Name
FROM dbo.[DataSource] AS ds
JOIN dbo.[Catalog] AS c ON c.ItemID = ds.ItemID
WHERE ds.[Flags] = ds.[Flags] & 0x7FFFFFFD
AND ds.[Link] IS NULL
AND ds.[ConnectionString] IS NULL;

When I just ran this on my own machine, having deleted a data source to check my code, I noticed a Report Model in the list as well – so if you had thought it was just going to be reports that were broken, you’d be forgetting something.

So to fix those reports, get your new data source created in the catalogue, and then find its ItemID by querying Catalog, using Path and Name to find it.

And then use this value to fix them up. To fix the Flags field, just add 2. I prefer to use bitwise OR which should do the same. Use the OUTPUT clause to get a copy of the DSIDs of the ones you’re changing, just in case you need to revert something later after testing (doing it all in a transaction won’t help, because you’ll just lock out the table, stopping you from testing anything).

UPDATE ds SET [Flags] = [Flags] | 2, [Link] = '3AE31CBA-BDB4-4FD1-94F4-580B7FAB939D' /*Insert your own GUID*/
OUTPUT deleted.Name, deleted.DSID, deleted.ItemID, deleted.Flags
FROM dbo.[DataSource] AS ds
JOIN dbo.[Catalog] AS c ON c.ItemID = ds.ItemID
WHERE ds.[Flags] = ds.[Flags] & 0x7FFFFFFD
AND ds.[Link] IS NULL
AND ds.[ConnectionString] IS NULL;

But please be careful. Your mileage may vary. And there’s no reason why 400-odd broken reports needs to be quite the nightmare that it could be. Really, it should be less than five minutes.

@rob_farley

Massive thanks to all the people that have been shouting about this event already. I’ve seen quite a number of blog posts about it, and rather than listing some and missing others, please assume I’ve noticed your blog and accept my thanks.

But in case this is all news to you – the next 24 Hours of PASS event is less than a fortnight away (Sep 20/21)! And there’s lots of info about it at http://www.sqlpass.org/24hours/fall2012/ 

(Don’t ask why it’s “Fall 2012”. Apparently that’s what this time of year is called in at least two countries. I would call it “Spring”, personally, but do appreciate that it’s “Autumn” in the Northern Hemisphere...)

Yes, I blogged about it on the PASS blog a few weeks ago, but haven’t got around to writing about it here yet.

As always, 24HOP is going to have some amazing content. But it’s going to be pointing at the larger event, which now less than two months away. That’s right, this 24HOP is the Summit 2012 Preview event. Most of the precon speakers are going to be represented, as are half-day session presenters, quite a few of the Spotlight presenters and some of the Microsoft speakers too. When you look down the list of sessions at http://www.sqlpass.org/24hours/fall2012/SessionsbySchedule.aspx, you’ll find yourself wondering how you can fit them all in. Luckily, that’s not my problem. For me, it’s just about making sure that you can get to hear these people present, and get a taste for the amazing time that you’ll have if you can come to the Summit.

I see this 24HOP as the kind of thing that will just drive you crazy if you can’t get to the Summit. There will be so much great content, and every one of these presenters will be delivering even more than this at the Summit itself. If you tune into Jason Strate’s 24HOP session on the Plan Cache and are impressed – well, you can get to a longer session by him on that same topic at the Summit. And the same goes for all of them.

If you’re anything like me, you’ll find yourself looking at the Summit schedule, wishing you could get to several presentations for every time slot. So get yourself registered for 24HOP and help yourself make that decision.

And if you can’t go to the Summit, tune in anyway. You’ll still learn a lot, and you might just be able to help persuade someone to send you to the Summit after all (before the price goes up after Sep 30).

This isn’t a typical post for me. Regular readers will know that I tend to write about technical things, or things that are coming up, or something about how we do things around my company, LobsterPot Solutions – whereas this post is a response to the latest T-SQL Tuesday. I haven’t missed one yet, so I’m moving a little out of my comfort zone and posting anyway. I’m also feeling short on time at the moment, but I digress...

The challenge this time wasn’t just blogging, it was to make notes about what you did on a particular day, and then blog about that. The day was July 11, which wasn’t really a typical day (my oldest son’s birthday), but I figure that the way my work life fitted around everything else is quite typical, and worth writing about.

First, some information about me, which is probably needed as a premise.

I’m married with three great kids – boys who are 15 (now) and 12, and a girl who is 4. I live in Adelaide, Australia, where I run a business (LobsterPot Solutions) and the local SQL Server User Group. I’m a PASS Director, and also very involved in my local church. I’ve also been annoyingly sick recently, which sees me playing catch-up on a lot of things. This is why I don’t feel like I have time for things right now, so it’s a matter of working out priorities and focussing on things that need my attention.

My portfolio on the PASS board is organising the 24HOP events, which are semi-annual virtual conferences, the next one being in September. This one is traditionally a Summit Preview event, showcasing many of the great speakers that will be presenting at the main Summit in November. I’m not speaking this year, but presented at 24HOP last year, and it’s a really good event, building interest and excitement for the content that will be heard.

So at the moment, there is a bunch of organising for that to be done, and with the PASS HQ staff being based in Canada, I was responding to emails and in discussions around that pretty much as soon as I was awake (along with responding to a couple of people from the SQL community who had technical questions they’d sent my way). It was school holidays, so despite it being my son’s birthday, he wasn’t up yet (he’s 15 – sleeping-in rates quite high on his personal todo list, as it did for me when I was his age). Once he was awake we gave him presents, and I got on with some more things. I’d taken the boys out the afternoon before to celebrate, because I was expecting to spend the Wednesday with a particular client on the far side of town. That trip got postponed, but didn’t reduce the list of things that needed to get done (although it did mean I didn’t have any meetings with clients arranged).

Of course, there are those things that tend to pop up on todo lists, like getting to the Post Office. I’d done enough stuff at home before heading into the city so that the Post Office was open and I could tick that off. Eventually I got into the city, where I was then spreading my time between staff and clients.

My role in the company is such that I don’t tend to get heavily involved in many projects. I support projects, and I try to be available for my staff, but there are too many things going to have me dedicated to one or two projects. I went through quotes and requirements specifications, reviewed other documentation, discussed some client relationships, and spent time with one of my guys looking at the priorities of both myself and some of my staff. I had a support call with Microsoft to follow up on, went through upcoming dates with the user group venue, signed an updated procurement document for one of our clients, arranged some meetings, fielded a call from a partner company asking about licensing, and an older client asking about upgrade paths...

...all done mostly from a cafe.

Being in the cafe brought a couple of interruptions, but welcome ones. When there are things that need doing, it can be really beneficial to be working from a cafe, because people I know will go past. This particular day, a guy from a client we’ve recently engaged waved hello to me, but was clearly in a rush, and another client who’s keen for us to help up-skill their people said hello and gave an update on things. This type of distraction is welcome because even when I’m busy and feeling like I don’t want distractions, this type of thing helps move some things along. (As I write this, I’m working from my office in town. Today, the distractions are slightly less welcome.) I know that I spend all day moving from one thing to another, but this seems to work for me. I have a todo list in Trello that seems to mostly work, so that when each thing (be it on the list or not) I can pick up whatever is next. I’m not scared of distractions, because I figure that the time someone calls is probably best for them. (Making time for people is really high on my priority list.)

By the end of the day, I’d also spent time at a client where I’m filling in for one of my guys who’s on leave at the moment, and had got updates on things from each of my staff members. On the way home, I stopped by one of our local pasta restaurants to get the birthday dinner that Sam wanted. Later that night I got some more emails sent. Having a smart phone is both a blessing and a curse for running a business. Despite the fact that it’s often an interruption, it does mean I’m more available to my staff and clients, and anyone else who needs me – and I wonder how I’d do without it. I read my bible on it, I listen to music on it, it tracks my walking/running. I take pictures of my family with it, read the news (often via Twitter), and even use it to play games when I choose to be distracted.

So that was my day. Fairly typical in many ways.

MERGE is very cool. There are a ton of useful things about it – mostly around the fact that you can implement a ton of change against a table all at once. This is great for data warehousing, handling changes made to relational databases by applications, all kinds of things.

One of the more subtle things about MERGE is the power of the OUTPUT clause. Useful for logging.

If you’re not familiar with the OUTPUT clause, you really should be – it basically makes your DML (INSERT/DELETE/UPDATE/MERGE) statement return data back to you. This is a great way of returning identity values from INSERT commands (so much better than SCOPE_IDENTITY() or the older (and worse) @@IDENTITY, because you can get lots of rows back). You can even use it to grab default values that are set using non-deterministic functions like NEWID() – things you couldn’t normally get back without running another query (or with a trigger, I guess, but that’s not pretty).

That inserted table I referenced – that’s part of the ‘behind-the-scenes’ work that goes on with all DML changes. When you insert data, this internal table called inserted gets populated with rows, and then used to inflict the appropriate inserts on the various structures that store data (HoBTs – the Heaps or B-Trees used to store data as tables and indexes). When deleting, the deleted table gets populated. Updates get a matching row in both tables (although this doesn’t mean that an update is a delete followed by an inserted, it’s just the way it’s handled with these tables). These tables can be referenced by the OUTPUT clause, which can show you the before and after for any DML statement. Useful stuff.

MERGE is slightly different though.

With MERGE, you get a mix of entries. Your MERGE statement might be doing some INSERTs, some UPDATEs and some DELETEs. One of the most common examples of MERGE is to perform an UPSERT command, where data is updated if it already exists, or inserted if it’s new. And in a single operation too. Here, you can see the usefulness of the deleted and inserted tables, which clearly reflect the type of operation (but then again, MERGE lets you use an extra column called $action to show this).

(Don’t worry about the fact that I turned on IDENTITY_INSERT, that’s just so that I could insert the values)

One of the things I love about MERGE is that it feels almost cursor-like – the UPDATE bit feels like “WHERE CURRENT OF …”, and the INSERT bit feels like a single-row insert. And it is – but into the inserted and deleted tables. The operations to maintain the HoBTs are still done using the whole set of changes, which is very cool.

And $action – very convenient.

But as cool as $action is, that’s not the point of my post. If it were, I hope you’d all be disappointed, as you can’t really go near the MERGE statement without learning about it.

The subtle thing that I love about MERGE with OUTPUT is that you can hook into more than just inserted and deleted.

Did you notice in my earlier query that my source table had a ‘src’ field, that wasn’t used in the insert? Normally, this would be somewhat pointless to include in my source query. But with MERGE, I can put that in the OUTPUT clause.

This is useful stuff, particularly when you’re needing to audit the changes. Suppose your query involved consolidating data from a number of sources, but you didn’t need to insert that into the actual table, just into a table for audit. This is now very doable, either using the INTO clause of OUTPUT, or surrounding the whole MERGE statement in brackets (parentheses if you’re American) and using a regular INSERT statement.

This is also doable if you’re using MERGE to just do INSERTs.

In case you hadn’t realised, you can use MERGE in place of an INSERT statement. It’s just like the UPSERT-style statement we’ve just seen, except that we want nothing to match. That’s easy to do, we just use ON 1=2.

This is obviously more convoluted than a straight INSERT. And it’s slightly more effort for the database engine too. But, if you want the extra audit capabilities, the ability to hook into the other source columns is definitely useful.

Oh, and before people ask if you can also hook into the target table’s columns... Yes, of course. That’s what deleted and inserted give you.

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?

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.

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:

SELECT
    YEAR(OrderDate),
    FIRST_VALUE(TotalDue)
        OVER (PARTITION BY YEAR(OrderDate)
              ORDER BY OrderDate, SalesOrderID
              RANGE BETWEEN UNBOUNDED PRECEDING
                        AND UNBOUNDED FOLLOWING),
    LAST_VALUE(TotalDue)
        OVER (PARTITION BY YEAR(OrderDate)
              ORDER BY OrderDate, SalesOrderID
              RANGE BETWEEN UNBOUNDED PRECEDING
                        AND UNBOUNDED FOLLOWING),
    PERCENTILE_CONT(0.95)
        WITHIN GROUP (ORDER BY TotalDue)
        OVER (PARTITION BY YEAR(OrderDate)),
    PERCENTILE_DISC(0.95)
        WITHIN GROUP (ORDER BY TotalDue)
        OVER (PARTITION BY YEAR(OrderDate))
FROM Sales.SalesOrderHeader
;

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:

SELECT
    YEAR(OrderDate),
    FIRST_VALUE(TotalDue)
        OVER (PARTITION BY YEAR(OrderDate)
              ORDER BY OrderDate, SalesOrderID
              RANGE BETWEEN UNBOUNDED PRECEDING
                        AND UNBOUNDED FOLLOWING)
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate)
;

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

Msg 8120, Level 16, State 1, Line 5
Column 'Sales.SalesOrderHeader.OrderDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 5
Column 'Sales.SalesOrderHeader.SalesOrderID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Hmm.

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.