Voice of the DBA

Category: Blog

  • 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

  • SQL in the City 2014 – Two countries in two weeks

    I have had a fairly busy travel schedule the last few years. While I don’t love sitting on airplanes and being away from home, I do look forward to the events and seeing the amazing #SQLFamily and community we have for SQL Server. I’ve also learned a few tricks to make travel smoother, which reduces stress.

    It’s a good thing since the SQL in the City 2014 event schedule is going to be short in duration, but long in miles. We have two days coming this fall: London and Seattle. We open in London on Fri, Oct 24, 2014 and then a little over a week later will be in Seattle on Mon, Nov 3, 2014. It’s a packed schedule that has me flying 6,000 miles to the UK before returning home and getting ready for a 1,300 mile trip up to the Pacific Northwest.

    However I’m excited for both events. London draws a bit crowd, with a variety of SQL Server professionals that are excited to learn new ideas, tips, tricks, and tools to help them in their professional work. We’ll once again be near the center of town, at St Grange, just off the St Pauls and Blackfriars Underground stations.

    Seattle is a great town, and while I’m sure I’ll see some Summit attendees, I look forward to meeting lots of local SQL Server data professionals that won’t attend the Summit. I hope that if you’re in the area, you come to our event. After all, how can your boss say "no" to a free day of training? Especially if you’re not attending the Summit later that week. We’ll be at McCaw Hall, near the Space Needle.

    It’s a short tour this year, but an exciting one. We’ll also continue to expand our SQL in the City seminars to other cities in the future, and hope we get the chance to meet more of you as we look to support, educate, and participate in this amazing SQL Server community.

  • Version Control for Databases

    This is based on my Team Based Development with Version Control talk, which has evolved a bit.

    Abstract

    Getting your database under version control is ultimately a way of communicating changes and collaborating with team members, maintaining an audit trail and ensuring you always have a version to roll back to.

    In this session, we’ll cover why this gives you and your team a crucial advantage in reducing the risk in your development processes. We’ll also discuss another door that your team opens by adopting solid version control: setting up the fundamentals of continuous integration. You will learn what build automation means as a first step towards continuous integration and the value it brings as part of your evolving deployment process. We’ll also discuss branching strategies, and how to manage this for databases as part of your evolving deployment process.

    Goals

    • Understand the value of version/source control for databases
    • Learn the tools, standards, patterns and best practices needed to manage a database from source control
    • Identify the necessary flow within a team needed to develop a database with source control

    This talk includes the following demos:

    • Move database DDL into VCS manually
    • Link database DDL and lookup data with SQL Source Control
    • Make development changes and check into VCS
    • Setup a new developer DB linked to VCS
    • Changes flowing back and forth between developers
    • Resolving conflicts between development changes made by two people
    • Basic branching of code for bug fixes and linking a database for development work to an old branch

    Length: 45-75 minutes. Shorter lengths with fewer demos.

    Slides: Get Database Under Source Control.pptx (Slideshare)