Voice of the DBA

Tag: T-SQL

  • Intermediate T-SQL: PIVOT, UNPIVOT, and APPLY

    This is the second of a series of talks I put together on intermediate T-SQL topics that many database developers might not understand. In this talk, we cover the other join-type operators that exist in the FROM clause, outside of the INNER, OUTER, FULL, and CROSS join clauses. We break this hour into three main areas, covering:

    • PIVOT and crosstabs
    • UNPIVOT
    • APPLY

    Most of the time is spent on the APPLY operator, which is arguably the most useful of the three. I show how APPLY is like an inner or outer join, and also how it can be used to improve performance of a scalar function, how it can be used to run some queries that are difficult with INNER JOINs, and how we can use this to easily find query plans and code in performance tuning.

    The PIVOT operator is compared to a crosstab, which is similar. We see how to write a query using either method. We also show the UNPIVOT operator.

    Slides: TSQL_IntermediateQueries.pptx

    Code: TSQLPIVOT_Unpivot_Apply.zip

  • Intermediate T-SQL: Writing Cleaner Code

    This session is an intermediate T-SQL session that helps users learn how to write better T-SQL code by covering a few items that many database developers might not be aware of. In an hour, users will lean how to:

    • write and use CTEs to simplify complex queries.
    • learn basic error handling, including recommendations for SQL Server 2012 and beyond with THROW.
    • use templates to store common code elements
    • tricks in SSMS to speed up code development
    • understand what a tally table is and how to use it to perform a few common tasks.

    This is the beginning of a three hour series I have on intermediate T-SQL code.

    Slides: TSQL_WritingCleanerCode.pptx

    Code: TSQL_CleanerCode.zip

  • Testing your API

    I think learning to better test our software, including the database objects, is one of the ways in which we’ll build better software applications in the future. Testing is a complex subject, but this is part of a series that looks at ways in which you can use tSQLt.

    Checking Table Metadata

    One of the easy tests you can write is to compare the meta data of an object to a known quantity. The easy way to do that in tSQLt is to use the AssertResultSetsHaveSameMetaData function. This function compares the structure of two result sets, covering names, ordering, and data types, to determine if they are the same.

    Here’s a quick example of how that’s done.

    Let’s assume I have this table:

     create table Articles  (
    [ArticlesID] [int] identity(1,1) not null,
    [AuthorID] [int] null,
    [Title] [char](142) null,
    [Description] [varchar](max) null,
    [Article] [varchar](max) null,
    [PublishDate] [datetime] null,
    [ModifiedDate] [datetime] null,
    [URL] [char](200) null,
    [Comments] [int] null
  );

    If I wanted to write a test in tSQLt to check this table for changes or alterations, here’s what I’d do in code:

    create procedure Articles.[test Articles_Check_metadata]

    as

    begin

      –Assemble

    create table Articles.Expected

      (

        [ArticlesID] [int] identity(1,1) not null,

        [AuthorID] [int] null,

        [Title] [char](142) null,

        [Description] [varchar](max) null,

        [Article] [varchar](max) null,

        [PublishDate] [datetime] null,

        [ModifiedDate] [datetime] null,

        [URL] [char](200) null,

        [Comments] [int] null

      );

     
      –Act

     
      –Assert

    exec tsqlt.AssertResultSetsHaveSameMetaData

      @expectedCommand = N’select * from Articles.Expected’,

      @actualCommand = N’select * from articles’

      ;

    end

    ;

    go

    Note that I don’t have an ACT section in this test.

    The Assemble section is easy. I record the size and shape of my table. This will be what I compare the actual table to in the database.

    The Assert section is a call to AssertResultSetsHaveSameMetaData, with a SELECT * from the real table being compared to the same SELECT from the expected result table I created. If these match, I pass the test. If they don’t, the test fails.

    Why?

    This seems silly, I know. What does it matter if the table changes, and it certainly will need to change. I definitely questioned the value of a test like this when I first saw the example. However when I thought about it, and thought about the places in which I’ve developed databases, this makes some sense.

    Imagine that I have 3 or 4 (or more) developers. As we get new requirements, we’ll change the schema over time. Imagine that I actually have views built on this table, and other procedures and functions, all of which have some tests on them. If I change this schema, and run a test suite, I could see multiple failures. If I did that, one would hope I realized that the addition of a column here (or a rename) would cause those issues. However if I changed a couple things before running a test, which is something I might do at times, having this test fail tells me quickly that the schema was altered. If someone else changed the schema, I also quickly see that this change was to the schema.

    It’s not a big change, but it does allow me to determine that I need to refactor all the objects (potentially) that depend on this table. I can go do that work now, or add it to the list of tasks for this particular development task, and also fix the tests, which should go quickly.

    If the work doesn’t go quickly because I have a lot of objects, then I’m really glad that I learned now this is an issue.

    This becomes even more valuable with views and procedures returning result sets. If I add a column, then I may or may not want views to change, but certainly a check of view meta data will tell me if they do.

  • Crosstabs over Pivots

    I wrote about a basic PIVOT query recently. It’s an interesting way to write a query and turn row data into columns. That’s handy, and lots of people have a need for it. However I’d never used PIVOT in production code. I’ve always written a crosstab query instead.

    If I look back at the query I wrote, it looks like this:

    select
    *
  from
    ( select
          team
        , opponent
        , teamscore
        from
          scores results
    ) as rawdata 

pivot

 ( avg(teamscore) for [Opponent] in ( [KC], [OAK], [SD] )

 ) as pivotresults;

    This gives me these results:

    team   KC   OAK  SD

    DEN    31   35   24

    However I could also write this query:

    select 
    'team' = team
,   'KC' = sum( case when Opponent = 'KC' then TeamScore else 0 end) / 2
,   'OAK' = sum( case when Opponent = 'OAK' then TeamScore else 0 end) / 2
,   'SD' = sum( case when Opponent = 'SD' then TeamScore else 0 end) / 2
 from scores s
 where team = 'DEN'
 group by team

    That gives me the same results.  The AVG item gets tricky as can don’t want zeros to be included in results. If there were a dynamic number of scores, I’d have to write a few subqueries to solve this issue.

    Which one is more clear and easier to understand? That’s a good debate. However I will say that if I need to add a column, I think it’s easier to do so in the crosstab.

    select 
    'team' = team
,   'KC' = sum( case when Opponent = 'KC' then TeamScore else 0 end) / 2
,   'OAK' = sum( case when Opponent = 'OAK' then TeamScore else 0 end) / 2
,   'SD' = sum( case when Opponent = 'SD' then TeamScore else 0 end) / 2
,   'NE' = sum( case when Opponent = 'NE' then TeamScore else 0 end)
 from scores s
 where team = 'DEN'
 group by team
;

    Here’s the PIVOT:

     select
    *
  from
    ( select
          team
        , opponent
        , teamscore
        from
          scores results
    ) as rawdata
  pivot 
   ( avg(teamscore) for [Opponent] in ( [KC], [OAK], [SD], [NE] ) 
   ) as pivotresults;

    I’ll also point out that Jeff Moden has written a few articles on this subject (Part 1 and Part 2) and his performance analysis shows that a crosstab performs better in almost all cases.

    I wouldn’t recommend one over the other. You need to test them both at larger than expected data sets to determine which one works in your situation.