T-SQL Test Automation (part 1 of 4): Get back on course

The series at a glance:

  1. Get back on course
  2. Live in the now, look to the future
  3. Time to focus
  4. Are we there yet?

WARNING: Excessive metaphors and clichés ahead!


Sometimes Often in life, the hardest thing to do is to just get started on something. Pay attention to how many of your thoughts begin with “I should…” Yep, same here. So, my most recent “I should” was “I should really use this blog site since I bothered to pay for a domain name.”

And on that theme, I offer you a plan to do the same… for the code in your SQL Server database.
Continue reading

SoCal Code Camp San Diego 2012 session

If you’re looking for the slides & code files for my SoCal Code Camp San Diego 2012 session on the T-SQL OUTPUT clause, here you go:

Up Your Game with OUTPUT (T-SQL)

Composable DML (OUTPUT clause) inside of a T-SQL Common Table Expression (CTE)

Disclaimer: the following was tested on SQL Server 2008R2.

When I presented my T-SQL OUTPUT topic to the San Diego SQL Server user group last month, a question came up regarding the composable DML use case. For those of you who aren’t familiar with that term, it’s a way in which you can choose to audit only certain rows from a data-changing operation, while using the OUTPUT clause. On its own, the OUTPUT clause returns all rows affected.

So, the basic syntax would look like this:

SET     ...
OUTPUT  inserted.ID,
        deleted.Amount AS OldAmount,
        inserted.Amount AS NewAmount
INTO    AuditTable

When you run such a statement, a row is inserted into AuditTable for each row updated in MyTable. If you make that a sub-query, however, you can do something like this:

INSERT INTO AuditTable (...)
FROM    (
            UPDATE  MyTable
            SET     ...
            OUTPUT inserted.ID,
                deleted.Amount AS OldAmount,
                inserted.Amount AS NewAmount
        ) AS UpdatedData
WHERE  UpdatedData.OldAmount > 300;

This time around, you only have rows in AuditTable where the value of Amount was 300 before the data changed.

The question which came up is whether you can use that same arrangement, but inside a common table expression. In other words, since OUTPUT returns the equivalent of a SELECT statement, you can place that inside of a CTE, right?

WITH UpdatedData AS (
    UPDATE  MyTable
    SET     ...
    OUTPUT inserted.ID,
        deleted.Amount AS OldAmount,
        inserted.Amount AS NewAmount
INSERT INTO AuditTable (...)
FROM    UpdatedData
WHERE    UpdatedData.OldAmount > 300;

Well, it turns out you can’t. Unfortunately, the error message isn’t terribly helpful:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'UPDATE'.
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near ')'.

So, while you can do some nifty things with OUTPUT and CTEs, it looks like SQL Server doesn’t think that these are 2 great tastes that taste great together. We’ll have to leave that to the peanut butter cups.

T-SQL OUTPUT (San Diego SQL Server User Group)

The slides & files from tonight’s presentation on the T-SQL OUTPUT function are available.


I must say, I do have a particular affinity for language constructs that have come to the rescue on several occasions, and OUTPUT is definitely one of them.

My PASS Summit 2011 T-Shirt

PASS Summit 2011 was my first time at a PASS Summit. It was such a wonderful experience that I decided to make a custom t-shirt for it. The problem is that even with all of my 3XL real estate, I ran out of space. Why? There were so many things that made it such a great week.

“Edit the list!”, you say? Yeah, I tried that, but I feel so strongly about each of these that I’ll have to make due with this blog post. So, here it is:

I went to PASS Summit 2011 and all I got was:
• Educated by experts
• Reunited with old friends
• Dozens of new friends
• Greeted like a rock star at the welcome reception
• A chance to witness @Markvsql rip the roof off #SQLKaraoke
• An excuse to use Twitter
• A chance to meet with a ranking member of the KC DBA Mafia
• Hit by flying crustacean pieces at the Crab Pot
• Conversations with 3 regional mentors other that my own
• Pleasant greetings from the 2 nicest Andy’s you’ll ever meet
• Face-to-face with 30 MVPs in less than 10 minutes
• Solutions to my 2 trickiest technical problems at no extra cost
• A free book and a $25 Amazon gift card*
• Free consulting from SQL CAT
• Projected on a 30-foot screen with @ctesta_oneill and @retracement (http://www.youtube.com/user/SQLPASSTV#p/u/11/5oRR4UlSidQ)
• The chance to see a vendor that premiered their product at our local user group show up on the expo floor in fine fashion
• Less than 30 minutes of awake usage of the hotel room per day

* Thank you very much, every vendor that sponsors us. We do know the without you, these events would be much fewer and farther between.

SQL Saturday 95 (San Diego) CTE demo files

These files will make more sense if you attend(ed) my presentation “Problem-Solving with Common Table Expressions”. However, feel free to give them a shot even if you didn’t.

Download CTE scripts.

To get the most use of these scripts, you should install the following AdventureWorks databases:

  • AdventureWorks (2005; the OLTP one)
  • AdventureWorks2008R2
  • AdventureWorksDW2008R2

They are available from CodePlex.


Presentation Preparation

So, the SQL Saturday San Diego speaker dinner just wrapped up. Margaritas prior to a presentation, always a good idea, right?

Good news: the demo code works
Better news: we’re in San Diego
Even Better news: we get to meet old friends and make new ones
Best news: all of this help and learning is nearly free (travel expenses only)

Rather than bore you with rambling details about…. oh, already am. Whoops!!

Here’s the bottom line. If you’re going to present information to an audience in any educational setting, there is one big thing you can do to make life easier for you and more effective for the audience: know what you’re talking about.

No, I don’t mean you should know the name of the presentation. You should know the information so well that you don’t need to read from slides or can’t answer questions. The whole idea is to turn it into a conversation, not a “class”. For a technical topic, you describe a problem, then demonstrate/describe a solution. Simple, right?

The key is to make it so that the audience follows you on a journey where you are the guide. Who cares if you know your stuff? That’s why you get paid to do your day job. Don’t try to impress the audience with what you know. Impress them with what you can do to help them. Who knows, maybe you’ll even inspire them to become guides themselves.

Well, I’m in the lead-off spot tomorrow morning, so it’s time to hit the sack and I’ll see y’all cloudy & early-ish.

San Diego Code Camp 2011

Can I get a w00t!!!! It’s time for San Diego Code Camp 2011!

What’s that? You haven’t registered? That’s ok, come on down to UCSD and learn your derrière off! If nothing else, show up Sunday morning for a T-SQL double-shot.

I’ll be presenting PIVOT & UNPIVOT at 9am on Sunday and Common Table Expressions at 10:15. If you want to follow along with the demos or want to get your hands on the material after the fact, then you can download the files below. Don’t let the pre-posting of content fool you. There’s lots to learn that isn’t in those files.

SQL Server Data Rotation with PIVOT & UNPIVOT

Common Table Expressions

I look forward to meeting a lot of you at the event and at the dinners!

SoCal Code Camp 2011 Sessions Added

I have just submitted 2 sessions to the Southern California Code Camp, to be held in San Diego on June 25th & 26th.

Should you be interested in either (or both) topics and will be in the area that weekend, please register on the site and vote for them.

If you’re going to be in the area that weekend, I encourage you to attend as the variety of topics is quite broad. You’ll likely find many topics of interest among the submitted session abstracts.

Transact-SQL PIVOT & UNPIVOT redux

That was awesome!! The presentation went well, although it did go 2 minutes over. It’s hard to top the feeling you get when the audience is engaged and asking a lot of great questions.

I’m glad I went with PIVOT & UNPIVOT as my first presentation. By pure luck, a couple of people in the audience had just used PIVOT to solve a reporting problem with it and some others learned about it for the first time. Better yet, at least one of them will be using PIVOT right away in their own work to get rid of the CASE nest that you get if you don’t use these clauses.

Thank you SD SQL PASS chapter and especially all of you who came out on a work night to listen to me go on for 15 17 minutes. It’s ok, we all know you really showed up for the PowerPivot demo. Great job Lakshmi.

As promised, the slide deck and scripts are also available.