Tag Archives: SQL Server

PASS Summit Free Pre-Cons

Are you going to Seattle for the PASS Summit this year?

If you’re going to be in town by Monday/Tuesday and are looking for some free training, you have some options. I love that SQL-server related vendors have really taken up the community spirit and do as much as they can for us for low/no cost. Sure, these have some content specific to the company putting it on, but the audience isn’t beaten over the head about it. When a vendor tool is shown as a solution to a problem, the alternative solutions are typically covered as well.

These are listed in chronological order. If there are any other events not shown here, please let me know.

Red Gate SQL in the City

Monday, Oct. 26 – Hyatt at Olive 8
Free

In this continuation of a successful touring training event, they’re providing 2 tracks of content: SQL Development (code management, CI/CD processes) and SQL DBA work (performance tuning, uptime, DR, etc.). Just as with a PASS SQL Saturday, you can choose whichever sessions you want to attend. They will also be featuring a hands-on lab where you can try their products out.

SQL Solutions Group Summit Free-Con

Tuesday, October 27 – Hyatt Place Seattle/Downtown
Small fee for lunch

This is what happens when a group of MCMs decide they want to spend their time providing 8 hours of training to anyone who wants to sign up. Specifically, it’s Jason Brimhall ( b | t ), Randy Knight ( b | t ), Ben Miller ( b | t ), and Wayne Sheffield ( b | t ).

The session content is listed on their event page (link above) and on the SSG web site. All they ask in exchange is that you pay for your own lunch, which is < $12. You do need to sign up on the event site (the link above) because space is limited. I wouldn't wait too long. This one will likely fill up fast.


Also, I do see great value in the official PASS pre-cons. In fact, I’m personally taking a mix-and-match approach this year (including a PASS pre-con). But if you’re looking for quality content at low/no cost, you can’t go wrong with these options.

Test Data Builders at SQL Saturday Las Vegas (Sept 12)

At SQL Saturday Las Vegas (Sept. 12, 2015), I’ll be presenting about test data builders. I’ve attached the slide deck and demo files to this post for your convenience. They’re also available directly from the SQL Saturday site, just visit my session summary on the schedule.

Building Your Way to Better Database Testing – Phil Helmer – SQL Saturday LV 2015 #436.zip

You will also have the chance to hear about this topic at SQL Saturday San Diego on September 19.

Even if you aren’t interested in my presentation, you should consider coming out to one or both of these events. You get a day of high quality free training from people who really care about the SQL Server community.

T-SQL Test Automation (Part 4 of 4): Are we there yet?

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?

HELP!! The run-on sentences have been banished, but I’m now trapped in my office by a mixed metaphor generator and a punctuation doubler is guarding the door for it!!

If you have been following along as I published this series, thank you. If you sat down and read the whole thing front to back at once, thank you very much. If you read this and think I didn’t even cover everything I should have, you have my eternal gratitude.

Huh?

If you recall, I started this whole thing with a simple premise: get started. Make a rough plan and get going. It doesn’t matter what part of your situation you tackle first. Just get that boulder rolling (see Newton’s First Law).

We’ll deal with the finish line in a minute.
Continue reading

T-SQL Test Automation (Part 3 of 4): Time to focus

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?

After reading part 1 of this series, you were [hopefully] motivated to step up your testing work. Encouragement, yes!! From part 2, you learned the importance of knowing where you are and where you want to be. This time around, we’re going to illustrate where to spend your time and effort next.

WARNING: I still haven’t located a decent automated run-on sentence extractor!

Get out of the manual mindset

I’ve seen it dozens of times. Developers get so accustomed to their 9-keystroke shortcuts that they don’t bother looking for a better way. How many times have you shoulder-surfed a co-worker and told them something like this:
Continue reading

T-SQL Test Automation (Part 2 of 4): Live in the Now, Look to the Future

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?

In the first post of this series on T-SQL Test Automation, I laid the groundwork of why you should care and some pretty corny clichés as to how to get started.

First, some housekeeping is in order. I’m not going to dwell on the topic of how to test your T-SQL code in this series. Instead, I’m saving that for the future. If you can’t wait, there are several fine resources and frameworks out there.

Back to business. Part 1‘s clichés are gone1 and all we are left with is the stark reality of “the now”. No frilly tools. No push-button test executions. No manager-distracting shiny reports automatically emailed to anyone who cares (and a few who don’t). Welcome to your starting point.
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:

UPDATE  MyTable
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 (...)
SELECT  ...
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 (...)
SELECT  ...
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.

T-SQL OUTPUT

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.

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.