In my last post, I showed a technique for dealing with working columns when writing T-SQL. The idea was around using APPLY to be able to push values from the existing set through calculations (but preferably not scalar functions, of course), producing new columns which can be used further down the query, even in the WHERE and GROUP BY clauses. Useful stuff indeed.

But there is a limitation which I didn’t cover, and I feel that this is worth writing about for this month’s T-SQL Tuesday. The theme allows people to write about previous Tuesday topics – so I’m going to revisit topics 17 and 25 as I go a little further into APPLY.

These working columns can only be applied if they are calculations on a single row of data. The resulting set might be bigger or smaller based on how many rows are affected, but the input (at least logically) should be considered on a row-by-row basis.

Consider the following query:

SELECT p.Name, r.RevName
FROM
Production.ProductSubcategory AS s
CROSS APPLY
(SELECT REVERSE(s.Name) AS RevName) AS r
CROSS APPLY
(
    SELECT TOP (1) *
    FROM Production.Product AS p
    WHERE p.ProductSubcategoryID = s.ProductSubcategoryID
    ORDER BY p.ListPrice DESC
) AS p
ORDER BY s.ProductSubcategoryID;

You see two CROSS APPLYs here – one creating a simple working column in REVERSE(s.Name), the other even involving another table to do a lookup. This second CROSS APPLY doesn’t necessarily produce any rows – if there is no matching Product, the resultset won’t contain any rows for that ProductSubcategory. I’ve shown some of its results below:

But what about the following query, which gives the same 37 rows on the AdventureWorks database:

SELECT
(
    SELECT TOP (1) p.Name
    FROM Production.Product AS p
    WHERE p.ProductSubcategoryID = s.ProductSubcategoryID
    ORDER BY p.ListPrice DESC
),
REVERSE(s.Name) AS RevName
FROM Production.ProductSubcategory AS s
ORDER BY s.ProductSubcategoryID;

This is very similar, but you’ll notice that instead of developing the result set in the FROM clause, I’ve used the calculations directly in the SELECT clause to produce the same result.

Except that it’s not the same.

Here, my TOP sub-query can only produce a single value. You’ll notice I change my query to fetch only a single column now – any more would give an error. And I’m lucky I’ve selected TOP(1), not TOP(2), or TOP (1) WITH TIES – which would both be legal in my APPLY system. You’ll see that APPLY provides additional flexibility here.

So now consider the following query:

SELECT
    p.Name,
    p.ProductSubcategoryID,
    COUNT(*) OVER (PARTITION BY p.ProductSubcategoryID) AS SubcatCnt
FROM Production.Product AS p
ORDER BY p.ProductSubcategoryID, p.Name;

You will see that both look at the list of Products, and produce a count of the number of products which are in the same Subcategory. I’m sure you’re all very familiar with the OVER clause and the use of the windowing technique provided by the PARTITION BY clause.

If we run this first query and look at some of the results, you’ll see the marvellous windowing technique, which I’ve highlighted using ZoomIt. You’ll see that the SubcatCnt column has the value 3 for the Subcategory with 3 items, and 6 for the one with 6, and so on.

Excellent stuff. But you’ll probably also be aware that you can’t use windowing functions like this in the WHERE clause – they’re applied only in the SELECT clause.

Having seen my working columns trick though, you might think otherwise.

Check this out – it’s perfectly legal!

SELECT
p.Name,
p.ProductSubcategoryID,
sc.SubcatCnt
FROM Production.Product AS p
CROSS APPLY
(
SELECT COUNT(*) OVER (PARTITION BY p.ProductSubcategoryID) AS SubcatCnt
) AS sc
ORDER BY p.ProductSubcategoryID, p.Name;

Amazing stuff. I can now use SubcatCnt in the WHERE clause. Job done!

But I’m having you on. It’s a trick. It’s rubbish.

You see, APPLY logically works on a single row at a time, as I said earlier. Look at the same block of results for this second query.

Loads of 1s. Not exactly helpful. It’s accurate though – there is a count of one row in each partition of the set that is logically passed in each time.

APPLY works out the result for each row individually. It doesn’t do it for the whole set. So if you’re wanting working columns that need to be applied to multiple rows at once, then you need to think of another solution.

APPLY is good, but not that good. Know its limitations and you’ll be better off.

Little Bobby Tables’ mother says you should always sanitise your data input. Except that I think she’s wrong. The SQL Injection aspect is for another post, where I’ll show you why I think SQL Injection is the same kind of attack as many other attacks, such as the old buffer overflow, but here I want to have a bit of a whinge about the way that some people sanitise data input, and even have a whinge about people who insist on using stored procedures for SSRS reports.

Let me say that again, in case you missed it the first time:

I want to have a whinge about people who insist on using stored procedures for SSRS reports.

Let’s look at the data input sanitisation aspect – except that I’m going to call it ‘parameter validation’. I’m talking about code that looks like this:

create procedure dbo.GetMonthSummaryPerSalesPerson(@eomdate datetime) as
begin
    /* First check that @eomdate is a valid date */
    if isdate(@eomdate) != 1
    begin
        select 'Please enter a valid date' as ErrorMessage;
        return;
    end

    /* Then check that time has passed since @eomdate */
    if datediff(day,@eomdate,sysdatetime()) < 5
    begin
        select 'Sorry - EOM is not complete yet' as ErrorMessage;
        return;
    end
   
    /* If those checks have succeeded, return the data */
    select SalesPersonID, count(*) as NumSales, sum(TotalDue) as TotalSales
    from Sales.SalesOrderHeader
    where OrderDate >= dateadd(month,-1,@eomdate)
        and OrderDate < @eomdate
    group by SalesPersonID
    order by SalesPersonID;
end

Notice that the code checks that a date has been entered. Seriously??!! This must only be to check for NULL values being passed in, because anything else would have to be a valid datetime to avoid an error.

The other check is maybe fair enough, but I still don’t like it.

The two problems I have with this stored procedure are the result sets and the small fact that the stored procedure even exists in the first place. But let’s consider the first one of these problems for starters. I’ll get to the second one in a moment.

If you read Jes Borland (@grrl_geek)’s recent post about returning multiple result sets in Reporting Services, you’ll be aware that Reporting Services doesn’t support multiple results sets from a single query. And when it says ‘single query’, it includes ‘stored procedure call’. It’ll only handle the first result set that comes back. But that’s okay – we have RETURN statements, so our stored procedure will only ever return a single result set.  Sometimes that result set might contain a single field called ErrorMessage, but it’s still only one result set.

Except that it’s not okay, because Reporting Services needs to know what fields to expect. Your report needs to hook into your fields, so SSRS needs to have a way to get that information. For stored procs, it uses an option called FMTONLY.

When Reporting Services tries to figure out what fields are going to be returned by a query (or stored procedure call), it doesn’t want to have to run the whole thing. That could take ages. (Maybe it’s seen some of the stored procedures I’ve had to deal with over the years!)

So it turns on FMTONLY before it makes the call (and turns it off again afterwards). FMTONLY is designed to be able to figure out the shape of the output, without actually running the contents. It’s very useful, you might think.

set fmtonly on
exec dbo.GetMonthSummaryPerSalesPerson '20030401';
set fmtonly off

Without the FMTONLY lines, this stored procedure returns a result set that has three columns and fourteen rows. But with FMTONLY turned on, those rows don’t come back.

But what I do get back hurts Reporting Services.

It doesn’t run the stored procedure at all. It just looks for anything that could be returned and pushes out a result set in that shape. Despite the fact that I’ve made sure that the logic will only ever return a single result set, the FMTONLY option kills me by returning three of them.

It would have been much better to push these checks down into the query itself.

alter procedure dbo.GetMonthSummaryPerSalesPerson(@eomdate datetime) as
begin
    select SalesPersonID, count(*) as NumSales, sum(TotalDue) as TotalSales
    from Sales.SalesOrderHeader
    where
    /* Make sure that @eomdate is valid */
        isdate(@eomdate) = 1
    /* And that it's sufficiently past */
    and datediff(day,@eomdate,sysdatetime()) >= 5
    /* And now use it in the filter as appropriate */
    and OrderDate >= dateadd(month,-1,@eomdate)
    and OrderDate < @eomdate
    group by SalesPersonID
    order by SalesPersonID;
end

Now if we run it with FMTONLY turned on, we get the single result set back. But let’s consider the execution plan when we pass in an invalid date.

First let’s look at one that returns data. I’ve got a semi-useful index in place on OrderDate, which includes the SalesPersonID and TotalDue fields. It does the job, despite a hefty Sort operation.

…compared to one that uses a future date:

You might notice that the estimated costs are similar – the Index Seek is still 28%, the Sort is still 71%. But the size of that arrow coming out of the Index Seek is a whole bunch smaller.

The coolest thing here is what’s going on with that Index Seek. Let’s look at some of the properties of it.

Glance down it with me… Estimated CPU cost of 0.0005728, 387 estimated rows, estimated subtree cost of 0.0044385, ForceSeek false, Number of Executions 0.

That’s right – it doesn’t run. So much for reading plans right-to-left...

The key is the Filter on the left of it. It has a Startup Expression Predicate in it, which means that it doesn’t call anything further down the plan (to the right) if the predicate evaluates to false.

Using this method, we can make sure that our stored procedure contains a single query, and therefore avoid any problems with multiple result sets. If we wanted, we could always use UNION ALL to make sure that we can return an appropriate error message.

alter procedure dbo.GetMonthSummaryPerSalesPerson(@eomdate datetime) as
begin
    select SalesPersonID, count(*) as NumSales, sum(TotalDue) as TotalSales, /*Placeholder: */ '' as ErrorMessage
    from Sales.SalesOrderHeader
    where
    /* Make sure that @eomdate is valid */
        isdate(@eomdate) = 1
    /* And that it's sufficiently past */
    and datediff(day,@eomdate,sysdatetime()) >= 5
    /* And now use it in the filter as appropriate */
    and OrderDate >= dateadd(month,-1,@eomdate)
    and OrderDate < @eomdate
    group by SalesPersonID

    /* Now include the error messages */
    union all
    select 0, 0, 0, 'Please enter a valid date' as ErrorMessage
    where isdate(@eomdate) != 1
    union all
    select 0, 0, 0, 'Sorry - EOM is not complete yet' as ErrorMessage
    where datediff(day,@eomdate,sysdatetime()) < 5

    order by SalesPersonID;
end

But still I don’t like it, because it’s now a stored procedure with a single query. And I don’t like stored procedures that should be functions.

That’s right – I think this should be a function, and SSRS should call the function. And I apologise to those of you who are now planning a bonfire for me. Guy Fawkes’ night has already passed this year, so I think you miss out. (And I’m not going to remind you about when the PASS Summit is in 2012.)

create function dbo.GetMonthSummaryPerSalesPerson(@eomdate datetime)
returns table as
return (
    select SalesPersonID, count(*) as NumSales, sum(TotalDue) as TotalSales, '' as ErrorMessage
    from Sales.SalesOrderHeader
    where
    /* Make sure that @eomdate is valid */
        isdate(@eomdate) = 1
    /* And that it's sufficiently past */
    and datediff(day,@eomdate,sysdatetime()) >= 5
    /* And now use it in the filter as appropriate */
    and OrderDate >= dateadd(month,-1,@eomdate)
    and OrderDate < @eomdate
    group by SalesPersonID
    union all
    select 0, 0, 0, 'Please enter a valid date' as ErrorMessage
    where isdate(@eomdate) != 1
    union all
    select 0, 0, 0, 'Sorry - EOM is not complete yet' as ErrorMessage
    where datediff(day,@eomdate,sysdatetime()) < 5
);

We’ve had to lose the ORDER BY – but that’s fine, as that’s a client thing anyway. We can have our reports leverage this stored query still, but we’re recognising that it’s a query, not a procedure. A procedure is designed to DO stuff, not just return data. We even get entries in sys.columns that confirm what the shape of the result set actually is, which makes sense, because a table-valued function is the right mechanism to return data.

And we get so much more flexibility with this.

If you haven’t seen the simplification stuff that I’ve preached on before, jump over to http://bit.ly/SimpleRob and watch the video of when I broke a microphone and nearly fell off the stage in Wales. You’ll see the impact of being able to have a simplifiable query. You can also read the procedural functions post I wrote recently, if you didn’t follow the link from a few paragraphs ago.

So if we want the list of SalesPeople that made any kind of sales in a given month, we can do something like:

select SalesPersonID
from dbo.GetMonthSummaryPerSalesPerson(@eomonth)
order by SalesPersonID;

This doesn’t need to look up the TotalDue field, which makes a simpler plan.

select *
from dbo.GetMonthSummaryPerSalesPerson(@eomonth)
where SalesPersonID is not null
order by SalesPersonID;

This one can avoid having to do the work on the rows that don’t have a SalesPersonID value, pushing the predicate into the Index Seek rather than filtering the results that come back to the report.

If we had joins involved, we might see some of those being simplified out. We also get the ability to include query hints in individual reports. We shift from having a single-use stored procedure to having a reusable stored query – and isn’t that one of the main points of modularisation?

Stored procedures in Reporting Services are just a bit limited for my liking. They’re useful in plenty of ways, but if you insist on using stored procedures all the time rather that queries that use functions – that’s rubbish.

@rob_farley

Should SQL Server even have functions? (Oh yeah – this is a T-SQL Tuesday post, hosted this month by Brad Schulz)

Functions serve an important part of programming, in almost any language. A function is a piece of code that is designed to return something, as opposed to a piece of code which isn’t designed to return anything (which is known as a procedure). SQL Server is no different. You can call stored procedures, even from within other stored procedures, and you can call functions and use these in other queries. Stored procedures might query something, and therefore ‘return data’, but a function in SQL is considered to have the type of the thing returned, and can be used accordingly in queries. Consider the internal GETDATE() function.

SELECT GETDATE(), SomeDatetimeColumn
FROM dbo.SomeTable;

There’s no logical difference between the field that is being returned by the function and the field that’s being returned by the table column. Both are the datetime field – if you didn’t have inside knowledge, you wouldn’t necessarily be able to tell which was which. And so as developers, we find ourselves wanting to create functions that return all kinds of things – functions which look up values based on codes, functions which do string manipulation, and so on.

But it’s rubbish.

Ok, it’s not all rubbish, but it mostly is. And this isn’t even considering the SARGability impact. It’s far more significant than that. (When I say the SARGability aspect, I mean “because you’re unlikely to have an index on the result of some function that’s applied to a column, so try to invert the function and query the column in an unchanged manner”)

I’m going to consider the three main types of user-defined functions in SQL Server:

• Scalar
• Inline Table-Valued
• Multi-statement Table-Valued

I could also look at user-defined CLR functions, including aggregate functions, but not today. I figure that most people don’t tend to get around to doing CLR functions, and I’m going to focus on the T-SQL-based user-defined functions.

Most people split these types of function up into two types. So do I. Except that most people pick them based on ‘scalar or table-valued’. I’d rather go with ‘inline or not’.

If it’s not inline, it’s rubbish. It really is.

Let’s start by considering the two kinds of table-valued function, and compare them.

These functions are going to return the sales for a particular salesperson in a particular year, from the AdventureWorks database.

CREATE FUNCTION dbo.FetchSales_inline(@salespersonid int, @orderyear int)
RETURNS TABLE AS 
RETURN (
    SELECT e.LoginID as EmployeeLogin, o.OrderDate, o.SalesOrderID
    FROM Sales.SalesOrderHeader AS o
    LEFT JOIN HumanResources.Employee AS e
    ON e.EmployeeID = o.SalesPersonID
    WHERE o.SalesPersonID = @salespersonid
    AND o.OrderDate >= DATEADD(year,@orderyear-2000,'20000101')
    AND o.OrderDate < DATEADD(year,@orderyear-2000+1,'20000101')
)
;
GO

CREATE FUNCTION dbo.FetchSales_multi(@salespersonid int, @orderyear int)
RETURNS @results TABLE (
    EmployeeLogin nvarchar(512),
    OrderDate datetime,
    SalesOrderID int
    )
AS
BEGIN
    INSERT @results (EmployeeLogin, OrderDate, SalesOrderID)
    SELECT e.LoginID, o.OrderDate, o.SalesOrderID
    FROM Sales.SalesOrderHeader AS o
    LEFT JOIN HumanResources.Employee AS e
    ON e.EmployeeID = o.SalesPersonID
    WHERE o.SalesPersonID = @salespersonid
    AND o.OrderDate >= DATEADD(year,@orderyear-2000,'20000101')
    AND o.OrderDate < DATEADD(year,@orderyear-2000+1,'20000101')
    ;
    RETURN
END
;
GO

You’ll notice that I’m being nice and responsible with the use of the DATEADD function, so that I have SARGability on the OrderDate filter.

Regular readers will be hoping I’ll show what’s going on in the execution plans here. Here I’ve run two SELECT * queries with the “Show Actual Execution Plan” option turned on.

Notice that the ‘Query cost’ of the multi-statement version is just 2% of the ‘Batch cost’. But also notice there’s trickery going on. And it’s nothing to do with that extra index that I have on the OrderDate column.

Trickery.

Look at it – clearly, the first plan is showing us what’s going on inside the function, but the second one isn’t. The second one is blindly running the function, and then scanning the results. There’s a Sequence operator which is calling the TVF operator, and then calling a Table Scan to get the results of that function for the SELECT operator. But surely it still has to do all the work that the first one is doing...

To see what’s actually going on, let’s look at the Estimated plan.

Now, we see the same plans (almost) that we saw in the Actuals, but we have an extra one – the one that was used for the TVF. Here’s where we see the inner workings of it. You’ll probably recognise the right-hand side of the TVF’s plan as looking very similar to the first plan – but it’s now being called by a stack of other operators, including an INSERT statement to be able to populate the table variable that the multi-statement TVF requires.

And the cost of the TVF is 57% of the batch!

But it gets worse.

Let’s consider what happens if we don’t need all the columns. We’ll leave out the EmployeeLogin column.

Here, we see that the inline function call has been simplified down. It doesn’t need the Employee table. The join is redundant and has been eliminated from the plan, making it even cheaper. But the multi-statement plan runs the whole thing as before, only removing the extra column when the Table Scan is performed.

A multi-statement function is a lot more powerful than an inline one. An inline function can only be the result of a single sub-query. It’s essentially the same as a parameterised view, because views demonstrate this same behaviour of extracting the definition of the view and using it in the outer query. A multi-statement function is clearly more powerful because it can contain far more complex logic.

But a multi-statement function isn’t really a function at all. It’s a stored procedure. It’s wrapped up like a function, but behaves like a stored procedure. It would be completely unreasonable to expect that a stored procedure could be simplified down to recognise that not all the columns might be needed, but yet this is part of the pain associated with this procedural function situation.

The biggest clue that a multi-statement function is more like a stored procedure than a function is the “BEGIN” and “END” statements that surround the code. If you try to create a multi-statement function without these statements, you’ll get an error – they are very much required. When I used to present on this kind of thing, I even used to call it “The Dangers of BEGIN and END”, and yes, I’ve written about this type of thing before in a similarly-named post over at my old blog.

Now how about scalar functions...

Suppose we wanted a scalar function to return the count of these.

CREATE FUNCTION dbo.FetchSales_scalar(@salespersonid int, @orderyear int)
RETURNS int
AS
BEGIN
    RETURN (
        SELECT COUNT(*)
        FROM Sales.SalesOrderHeader AS o
        LEFT JOIN HumanResources.Employee AS e
        ON e.EmployeeID = o.SalesPersonID
        WHERE o.SalesPersonID = @salespersonid
        AND o.OrderDate >= DATEADD(year,@orderyear-2000,'20000101')
        AND o.OrderDate < DATEADD(year,@orderyear-2000+1,'20000101')
    );
END
;
GO

Notice the evil words? They’re required. Try to remove them, you just get an error.

That’s right – any scalar function is procedural, despite the fact that you wrap up a sub-query inside that RETURN statement. It’s as ugly as anything. Hopefully this will change in future versions.

Let’s have a look at how this is reflected in an execution plan. Here’s a query, its Actual plan, and its Estimated plan:

SELECT e.LoginID, y.year, dbo.FetchSales_scalar(p.SalesPersonID, y.year) AS NumSales
FROM (VALUES (2001),(2002),(2003),(2004)) AS y (year)
CROSS JOIN Sales.SalesPerson AS p
LEFT JOIN HumanResources.Employee AS e
ON e.EmployeeID = p.SalesPersonID;

We see here that the cost of the scalar function is about twice that of the outer query. Nicely, the query optimizer has worked out that it doesn’t need the Employee table, but that’s a bit of a red herring here. There’s actually something way more significant going on.

If I look at the properties of that UDF operator, it tells me that the Estimated Subtree Cost is 0.337999.

If I just run the query SELECT dbo.FetchSales_scalar(281,2003); we see that the UDF cost is still unchanged.

You see, this 0.0337999 is the cost of running the scalar function ONCE.

But when we ran that query with the CROSS JOIN in it, we returned quite a few rows. 68 in fact. Could’ve been a lot more, if we’d had more salespeople or more years.

And so we come to the biggest problem. This procedure (I don’t want to call it a function) is getting called 68 times – each one between twice as expensive as the outer query. And because it’s calling it in a separate context, there is even more overhead that I haven’t considered here.

The cheek of it, to say that the Compute Scalar operator here costs 0%! I know a number of IT projects that could’ve used that kind of costing method, but that’s another story that I’m not going to go into here.

Let’s look at a better way.

Suppose our scalar function had been implemented as an inline one. Then it could have been expanded out like a sub-query. It could’ve run something like this:

SELECT e.LoginID, y.year,
(SELECT COUNT(*)
    FROM Sales.SalesOrderHeader AS o
    LEFT JOIN HumanResources.Employee AS e
    ON e.EmployeeID = o.SalesPersonID
    WHERE o.SalesPersonID = p.SalesPersonID
    AND o.OrderDate >= DATEADD(year,y.year-2000,'20000101')
    AND o.OrderDate < DATEADD(year,y.year-2000+1,'20000101')
    ) AS NumSales
FROM (VALUES (2001),(2002),(2003),(2004)) AS y (year)
CROSS JOIN Sales.SalesPerson AS p
LEFT JOIN HumanResources.Employee AS e
ON e.EmployeeID = p.SalesPersonID;

Don’t worry too much about the Scan of the SalesOrderHeader underneath a Nested Loop. If you remember from plenty of other posts on the matter, execution plans don’t push the data through. That Scan only runs once. The Index Spool sucks the data out of it and populates a structure that is used to feed the Stream Aggregate. The Index Spool operator gets called 68 times, but the Scan only once (the Number of Executions property demonstrates this).

Here, the Query Optimizer has a full picture of what’s being asked, and can make the appropriate decision about how it accesses the data. It can simplify it down properly.

To get this kind of behaviour from a function, we need it to be inline. But without inline scalar functions, we need to make our function be table-valued. Luckily, that’s ok.

CREATE FUNCTION dbo.FetchSales_inline2(@salespersonid int, @orderyear int)
RETURNS table
AS
RETURN (SELECT COUNT(*) as NumSales
    FROM Sales.SalesOrderHeader AS o
    LEFT JOIN HumanResources.Employee AS e
    ON e.EmployeeID = o.SalesPersonID
    WHERE o.SalesPersonID = @salespersonid
    AND o.OrderDate >= DATEADD(year,@orderyear-2000,'20000101')
    AND o.OrderDate < DATEADD(year,@orderyear-2000+1,'20000101')
);
GO

But we can’t use this as a scalar. Instead, we need to use it with the APPLY operator.

SELECT e.LoginID, y.year, n.NumSales
FROM (VALUES (2001),(2002),(2003),(2004)) AS y (year)
CROSS JOIN Sales.SalesPerson AS p
LEFT JOIN HumanResources.Employee AS e
ON e.EmployeeID = p.SalesPersonID
OUTER APPLY dbo.FetchSales_inline2(p.SalesPersonID, y.year) AS n;

And now, we get the plan that we want for this query.

All we’ve done is tell the function that it’s returning a table instead of a single value, and removed the BEGIN and END statements. We’ve had to name the column being returned, but what we’ve gained is an actual inline simplifiable function. And if we wanted it to return multiple columns, it could do that too. I really consider this function to be superior to the scalar function in every way.

It does need to be handled differently in the outer query, but in many ways it’s a more elegant method there too. The function calls can be put amongst the FROM clause, where they can then be used in the WHERE or GROUP BY clauses without fear of calling the function multiple times (another horrible side effect of functions).

So please. If you see BEGIN and END in a function, remember it’s not really a function, it’s a procedure. And then fix it.

@rob_farley

This was a proper big week.

The PASS Summit ran from Tuesday night to Friday, but I’d arrived in America the Friday before. So by the time it actually started, I had that strange feeling that things were wrapping up. My calendar was ridiculously full. The stuff that I was aware of ahead of time looked like this:

Friday 7th: Arrive in America. Travel to Portland. Speaker Dinner.

Saturday 8th: SQL Saturday #92 (two sessions to give, plus a song performance with Buck during the morning break). Charity dinner for The Leukemia & Lymphoma Society (whom LobsterPot sponsor).

Sunday 9th: Walk the Portland Half Marathon. Travel to Seattle. Collect my kilt. Register for the Summit.

Monday 10th: Early morning prayer meeting. Deliver a pre-conference seminar. Insiders Dinner in evening.

Tuesday 11th: Early morning prayer meeting. Meeting about SQL Saturday. PASS Board Meeting. Insiders Day at the Microsoft Campus. Opening Night Party, including being a Quiz Bowl contestant. Speaker/Volunteer Dinner.

Wednesday 12th: Early morning prayer meeting. Chapter leaders’ meeting. Chapter lunch. Book signing. Lightning Talk to deliver (my song). Global Growth meeting. Exhibitors’ party. Parties for SQL People, SQL Sentry, SolidQ and SQLKaraoke.

Thursday 13th: Early morning prayer meeting. WIT Lunch. Spotlight session to deliver. Redgate Dinner. Party at Gameworks.

Friday 14th: Early morning prayer meeting. More book signing. Board Q&A session. Board photos.

Saturday 15th: Sleep in and fly home.

That’s the short version. Really. There were a lot of other things that were squeezed in – in particular, the fact that I had promised Audrey Hammonds about six months ago that I would run through her presentation with her, and one other thing that became somewhat significant: THE FACT THAT ALLEN KINSEL ARRANGED FOR ME TO PERFORM MY SONG DURING THE FRIDAY KEYNOTE.

Readers of my blog (and plenty of other blogs) will know by now that I played my guitar and sang at the start of the Friday Keynote. Buck Woody accompanying me with his guitar and backing vocals. What you might not know is that this only got arranged late on Wednesday night. Allen had seen me perform it (again with Buck) during the Lightning Talks on Wednesday afternoon, and by the time the night was over, Buck and I were booked in to perform it in front of the three and a half thousand delegates at the biggest keynote of the week. I’ve played and sang before a couple of hundred before, but never even close to that many. To say I had mixed emotions would’ve been an understatement. I didn’t hesitate to say yes, and was excited, but was also phenomenally nervous.

As a late entry to a tight schedule, we got two minutes only and had to cut the song short. We didn’t sing the bridge section, so stopped after just Verse Chorus Verse Chorus. It was tremendous fun, and I loved seeing faint glows of phones being waved around in the crowd. I desperately wanted to look into the crowd as I was walking off stage, but my nerves and the fear of tripping on the guitar lead froze me completely and I didn’t turn my head at all. I was told later that there was a standing ovation – but I was just full of emotion, and so tired. So tired. It was my own fault – I’d gone out with Microsoft people after the various parties, and when they’d all gone to bed for a couple of hours around 5:30am, I’d got myself ready to lead a quick song at the 6am prayer meeting head off to my 6:15am sound check, prior to the 7:15am book signing. I got through the day (although I fell asleep for a few minutes during Audrey’s session, which I will need to watch to see how she did). I eventually got to bed around 10:30pm Friday night, and about twelve hours later managed to get up with just enough time to pack and check out of the hotel for noon.

But this wasn’t the highlight for me.

Nor was the highlight the fact that my level of involvement was so much greater, now that I’m a Board Member. I have to admit that I always get so much more out of events like this if I’m involved. I appreciate that I can get to more sessions if I’m not already laden with other entries on my calendar, but I would rather serve others wherever possible. I’m still new on the board, but as an advisor, I’m hoping to be able to influence things like the Summit more and more next year, and maybe there will be a few areas in which we can find opportunity to improve it still.

Serving is great – but wasn’t the highlight.

The highlight was the people. It always is, and it always will be. Right from the moment I arrived in America and tracked down John & Yanni Robel and Jes Borland. Arriving in Portland and seeing Jeremiah & Kendra, Buck Woody, Tim Ford, Erin Stellato and many more at the SQLSaturday events. Participating in the Portland Half Marathon with Erin, Jes, Yanni, Brent Ozar, Karen Lopez, Doug Lane (who did 10km) and Allen White (who did the full marathon), and a lots of people wearing the LobsterPot logo on their shirts. And despite not being able to run (I physically can’t run because of an old back injury, so I just walked it), I didn’t even finish last of the half marathoners! For all the extra pain I felt because my back isn’t really up to walking 21km in 3 hours, it was worth it. I’m especially proud of Yanni, who has achieved amazing things despite having a nasty blood cancer.

Despite Sunday being a ridiculously early start and a very long day, each day started with 6am prayer meetings. But this gave the opportunity to start each day on a good note, meet even more people (like Matt “Mrs” Slocum), and to figure out some of why I always click so well with friends like Stacia Misner.

Even the time I spent moving from one spot to another was a chance to see people and grow friendships deeper. I think of the night I was heading back to my hotel after the walk back from one of the parties had gone past the other hotels, and I bumped into the “Damn Strates” (Jason & Sarah), only to spend an hour talking with them about a number of different things. Or the time spent talking with one of the industry’s newest and brightest lights Jes Borland, who thinks she can out-talk me. Or the time with fellow board members JRJ, MarkS & Lara, which is time I can’t help but enjoy as the conversations switch over and over from flippant to serious and back again.

Oh, and the lowlight was losing my prescription sunglasses. That’s a real pain.

The SQL community has something very special, that other communities should be jealous of. These people genuinely love each other, and it’s really good to see and to be part of it. I’ve seen the same at SQLBits, but not in many other contexts.

@rob_farley

PS: Apologies to everyone for not having links on your names. I wrote this on a plane without connectivity.

The words for that song I did at the PASS Summit 2011 are as follows. On the Friday, I stopped where the bridge starts. Various recordings of it are making it to YouTube, such as here, where the song starts around 2:20 in.

I should've looked the other way

Verse 1:
My query sucks - it takes too long
So long I wrote this song
The plan's not big - it ain't a giant
And yet I have an angry client
Performance now has made her weary
So I've come in to fix her query
I promise I won't ever fail her
Say "Trust me, love, I'm from Australia!"

Chorus:
I need to find you
But I don't want to search every row
My predicate's residual
My seek just runs too slow
I thought I'd caught a glimpse of you
Been searching for all day
But all along, I'd done it wrong
I should've looked the other way

Verse 2:
A trace is on, I know the reads
That fetch the bytes the query needs
There's spooling from a CTE
They've got recursion needlessly
I need to dig a little further
I worry there might be a cursor
The DBA has the plan_handle
He says it's not corrupt, he knows Paul Randal!

Repeat chorus

Bridge:
There is an index covering predicates with keys
But my developer has used inequalities
There is a range scan
Hiding truth
Hiding cost
Hiding you...

Repeat chorus x2

I should've looked the other way

© 2011 Rob Farley ;)

@rob_farley

In the PASS Summit Keynote (Day 1), they’re demonstrating tools to join data from Excel, Marketplace, SQL Azure, using the “Data Explorer”.

The thing that I’m liking about this is that you can specify that you want to do a lookup or a merge. Both joins, but two different types. I like this because when I write joins in T-SQL queries, I tend to have it in my head whether I’m expecting a Nested Loop (lookup) or a Merge Join. Seems Data Explorer lets me decide that right up front.

So you can pull data down from all kinds of different places, and publish it back out through the Marketplace. This means you can enhance other data sets, which other people can then enhance further, and this can continue to make some incredible data sources that can be consumed in OData just about anywhere.

I’m a guest blogger again!

This trip is already several days old for me. I arrived in America on Friday, delivered two sessions at Portland SQL Saturday event, walked (on Sunday) the Portland Half Marathon as a sponsor of Leukemia and Lymphoma Society’s Team in Training program, delivered a full-day summit precon seminar on Monday, and was in PASS Board meetings yesterday. Finally we’ve reached Day 1, and I could use a weekend!

But it’s now that things really kick off properly.

As I write this, Rushabh Mehta is on stage, talking about some of the numbers that apply to this summit. There are over attendees at the Summit this year, demonstrating the amazing reach that the PASS Summit has. And they also just talked about my involvement as an advisor in reaching the rest of the world better too. Every year, the Summit gets bigger, and PASS runs more events in more places. It’s an exciting time to be part of this community.

Microsoft’s Ted Kummert (Senior VP, Business Platform Division) is on stage now, and has announced that SQL Server Denali will officially be named SQL Server 2012.

Yes, the SQL book I was involved in two years ago has a sequel.

In the first book, I wrote chapters 7 and 40, and was one of 53 MVPs that contributed. And the proceeds went to War Child. Yes, none of the MVPs who wrote, edited, etc, got any royalties – that all went to charity.

This time, I wrote just one chapter (chapter 2), but there have been at least seventy MVPs involved. Sixty chapters all by different MVPs, with more MVPs involved as section editors, technical editors and more. And the money this time goes to Operation Smile, another international children’s charity.

It’s being published in time for the PASS Summit (which is now less than two weeks away), and there will be a book signing for people who have their copies already. You can pre-order through the Manning website at http://www.manning.com/delaney/, but if you do this, you might not have it for the signing. With most of the authors present, you might prefer to try your luck at picking up a copy at the Summit if you can make it.

If you’re not going to be at the Summit, then notice that you can pre-order the book and that this gets you an Early Access Edition – an electronic copy of some of the chapters as they become available.

But my chapter is different again – you can download it for FREE. No strings attached. You’ll see a link on the page to my chapter, and can enjoy it straight away, without having to pay anything at all.

I’m sure I will have missed someone in this list, but I have to provide it because it’s just so extraordinary. The list of MVPs I know were involved includes:

Johan Ahlen, Gogula Aryalingam, Glenn Berry, Aaron Bertrand, Kevin Boles, Robert Cain, Tim Chapman, Denny Cherry, Michael Coles, Rod Colledge, John Paul Cook, Louis Davidson, Kalen Delaney, Dave Dustin, Rob Farley, Grant Fritchey, Denis Gobo, Darren Gosbell, Sergio Govoni, Allan Hirt, Victor Isakov, Satya Shyam K Jayanty, Tibor Karaszi, Jungsun Kim, Tobiasz Koprowski, Hugo Kornelis, Ted Krueger, Matija Lah, Rodney Landrum, Greg Larsen, Peter Larsson, Andy Leonard, Ami Levin, Greg Low, John Magnabosco, Jennifer McCown, Brad McGehee, Siddharth Mehta, Ben Miller, Allan Mitchell, Tim Mitchell, Luciano Moreira, Jessica Moss, Aaron Nelson, Paul Nielsen, Shahriar Nikkhah, Robert Pearl, Boyan Penev, Pedro Perfeito, Pawel Potasinski, Mladen Prajdic, Abolfazl Radgoudarzi, Paul Randal, Denis Reznik, Rafael Salas, Edwin Sarmiento, Chris Shaw, Gail Shaw, Linchi Shea, Jen Stirrup, Jason Strate, Kimberly Tripp, Paul Turley, Bill Vaughn, Mike Walsh, Peter Ward, Joe Webb, John Welch, Allen White and Thiago Zavaschi.

Most are authors, some (like Aaron Nelson and Jen Stirrup) were technical editors, and some (like Paul Randal and Kimberly Tripp) were section editors. And Kalen Delaney was the overall editor and responsible for everything.

So a big thanks to Manning to make this possible, and to Kalen Delaney for leading the effort.

To potential buyers, see it as a series of short stories, and an opportunity to see a little into the minds of many of the industry’s leading lights.

And buy a copy – it’s for charity!