Using Automated Tests to Raise Code Quality


Agile development practices can speed the development of applications and increase the rate at which you can deploy features for your customers. But unless you include a high level of test coverage in your code, these practices can also increase the number of bugs that are released. Databases can be particularly challenging for developers. This session examines database refactorings that introduce bugs, which are detected by automated tests. This approach allows developers to rapidly fix their code before a customer is affected.

Level: 200


These are the demos shown in this talk.

  • Adding test data inline
  • Added test data in a procedure
  • Adding test data from a separate set of tables.
  • Exclusions to SQL Cop or other tests with Extended Properties.
  • Using FakeFunction
  • Using SpyProcedure
  • The boundary issues with multiple requirements for a function.
  • 0-1-Some testing
  • Catching dependencies.


Here are the downloads for the talk.

A Practical Approach to Version Control


Chances are, your team has several point-in-time backups for your databases. After all, they’re essential for recovering the system in an emergency. And, chances are, you’ve got a version control system (VCS) to provide the same capabilities for your applications.

But what about version control for the database, too?

Come to this session to see how you can create a more efficient database development platform by integrating your VCS with SQL Server. In real-time, you’ll see how versioning, branching, merging, and the other manual tasks you hate can fade away with just a few tips, tricks, and tools.

Level: 200


I’ve built these demos:

  • Connecting your database to a version control system. I use Git here, but can use SVN (or TFS online with connectivity)
  • History and Changes – Getting a few of the changes made over time, tracking to a user, undoing changes.
  • Object Locking – Using Redgate’s SQL Source Control, but explaining how a semaphore is needed
  • Branching – A look at creating a branch in Git and then working with a second database.


Powerpoint: SITC15_SteveJonesPracticalVCS.pptx

Avoiding a DBA’s Worst Days with Monitoring

SQL in the City Abstract: When things go wrong with a database, it can be the start of the worst day of a DBA’s life. Join Steve Jones as he examines the problems uncovered by The DBA Team and how you can prevent them with proactive monitoring and in-depth knowledge of SQL Server.

General Abstract: A DBA usually has a bad day because they are unprepared for issues that commonly occur or unaware of situations that can cause problems. Learn about the five things Steve Jones finds to be most important for DBAs and how you can be ready to handle issues in these areas:

  • Backups
  • Space
  • Security
  • Resources
  • Deployment

This session does include Red Gate tools but explains how issues can be avoided with your own utilities.

Slides: DBAsWorstDays.pptx

Placeholder resources:

Custom Metrics used in the demo:

Intermediate T-SQL: Window Functions

This is the third hour of a three hour set of presentations on intermediate T-SQL techniques and features.

This presentation covers the window functions that are available in SQL Server. Some of these functions were introduced in SQL Server 2005 and some in SQL Server 2015. We start with a brief introduction to what a window is, how a partition works, and then look at ordering and framing of rows. Then we cover the following in demonstrations:



The OVER clause and windowing in SQL Server 2012

Using PARTITION BY and ORDER BY in the OVER clause

Default framing with RANGE and how ROWS are different

PERCENTILE() functions

This covers a lot of information in an hour, but I try to use small data sets to explain what is happening with the window functions and how they can improve the performance, as well as simplify the code, for a number of common queries.

Slides: TSQL_Windowing.pptx


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

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


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


SQL in the City This Fall

We’ve got a couple of large SQL in the City events coming this fall.  I’m speaking at both, which I think means my boss is happy with my work for Red Gate Software. At least, that’s what I’ll tell myself.
In a little over a week I’ll be in both London and Seattle, delivering a variety of sessions on DBA and development topics. I start the day off with a session for the DBAs with Avoiding a DBA’s worst days with monitoring. This is based on The DBA Team’s 5 Worst Days in a DBA’s Life where we look at some of the situations you can avoid by keeping an eye on your environment. I then cover Version control and CI Fundamentals for databases, looking at how you can get your database code in a VCS and build a Continuous Integration (CI) process. I’ll also cover testing, using the free tSQLt framework to write some tests against your databases.
It’s a quick set of sessions for me, but I’ll be around all day to talk to you about your database delivery, deployment, development, or administration issues. Or just shake your hand if you’d like.
I fly to London for our event on Fri, Oct 24 and then will be in Seattle on Nov 3 for the second stop on the tour. A lot of travel for me, but I’m sure it’s an easier commute for most of you.  I hope you’ll join us and come learn a bit more about SQL Server than you might know today.

Get Testing with tSQLt

tSQLt is a testing framework that is designed to help you write repeatable, isolated tests against your database code. In this session we will briefly examine the goals of testing, where it can be used in an automated deployment pipeline, and introduce you to the tSQLt framework. We’ll show you how to design and write tests to enforce standards, check calculations and other DML operations as well as check exception handling. You will see how a real world refactoring task can be tested in a repeatable manner. Learn how you can incrementally write tests that help you build higher quality code and minimize the introduction of errors against your existing objects.

We will examine a variety of tests, including

  • Checking table metadata
  • Checking function calculation
  • Isolating a procedure test from an embedded function
  • Checking for error handling

PowerPoint slides: