T-SQL Tricks – Trigger Your Memory

I was scanning Twitter the other day and saw a note from someone that they had written a query using an obscure T-SQL command and were glad it had worked. I exchanged a note with the person and they mentioned that they had to look up the command and syntax periodically when they had to write a similar query.

I mentioned templates.

If  you haven’t used these, you should, and I wrote a basic post about how to access them and one on customizing these for yourself. These templates are like Snippets in SQL Prompt (Which are way more useful to me), and they are a tool every DBA should use.

Here’s one way I think they’re really helpful:

Suppose I need to write a PIVOT query. I rarely do this, and it’s not too hard, but I write this query:

select
    *
  from
    ( select
          runner
        , miles
        , mins
        from
          results
    ) as rawdata pivot ( avg(mins) for [miles] in ( [3], [5], [10] ) ) as pivotresults
;
GO

That’s easy enough, but it’s specific for my tables. However when I glance at it, I can see that there’s an aggregate columns, and I know the PIVOT requires that I list the values that are to be used in the columns.

What if I change the query? I can do this:

select
    *
  from
    ( select
          runner
        , <pivotcol, varchar, miles>
        , <aggcol, varchar, mins>
        from
          results
    ) as rawdata pivot ( avg(<aggcol, varchar, mins>) for [<pivotcol, varchar, miles>] in ( [3], [5], [10] ) ) as pivotresults
;
GO

Now if I make this a template:

I can drag this into a new query window. When I see it, I can CTRL+Shift+M and get this:

Now I change a few values and I have a pivot.

Of course, I need to actually enter the values I want, but this gets my PIVOTs done quickly without the need to decode BOL or swing by SQLServerCentral. Once I do that, I have a query I can use.

I’d encourage you to use templates. They’re very, very handy for quick sections of code that you use often, or want to remember in the future.