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

Version Control for Databases

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


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.


  • 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)

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:



Two in Two Days

It’s a busy week for me. I’ve got quite a few articles to review, feedback to write for Stairway Series authors, review and changes of some PowerPoint decks for later this month, and two User Group presentations.

This is on top of a busy first week of school in the household. I feel like I’m playing catch up all week.


I’ll be at the Boulder SQL Server User Group tonight. My presentation will be on Unstructured Data in SQL Server, looking at Filestream and Filetable and how they can be setup and used.

Fortunately I’ve done this before, and a little practice this week was enough to get me ready.

Hopefully I’ll see a few of you there as I haven’t been to Boulder in over a year.

Denver SQL

The Denver SQL Server User Group usually asks me do a presentation or two each year and this time I have a new one. They get to be my guinea pigs for the first delivery of this talk.

Get Testing with tsqlt, a preview of a talk I’ll be doing at SQL in the City, is on the agenda. I’ve been going over this one a few times this week, so hopefully it goes smoothly.

Apologies to my CI for Databases Attendees

I have to apologize to everyone that attended my Continuous Integration for Databases talk at SQL Bits. I ran over, a few demos didn’t work, and my machine flaked more than expected. I know those things happen, but I still need to apologize.

While there were problems with my demos, I should have moved on and gone through things a little quicker as well. I could have cut out, or cut down a few concepts, to get through things quicker. The session is really built for 75 minutes, but that’s no excuse. I need a 60 minute, smoothly delivered, version.

I also should have run through everything completely after some changes at Red Gate. I only set up part of it, which was a mistake. I don’t know if I’d have caught anything, but I might have.

I’ll work on getting some video and written content of the presentation up here, and hopefully that will help you understand the concepts, and perhaps start to implement CI with your own databases.

High Performance Encryption

It is becoming more and more important to protect your data from anyone that should not access it, including the DBA. Learn how you can use column level encryption as well as TDE to protect your data files, with minimal performance impact to your SQL Server instance.

This session will look at securing data

  • in flight
  • at rest
  • inside the database

We do this with

  • IPSec/SSL Communications
  • Transparent Data Encryption
  • Partial Hash Bucket values with symmetric encryption.



Your First Week as a DBA

Summary: There are many accidental DBAs as well as people new to SQL Server who are assigned the responsibility of managing a database system. Learn about the most important things for you to examine on your first week.

Abstract: Developers, system administrators, junior DBAs, and even managers often find themselves responsible for a new SQL Server system without knowing what things they need to worry about. Since a SQL Server instance can run for months without issues, these accidental DBAs don’t realize that they are neglecting maintenance and other operations that will ensure their systems are protected and running smoothly. This session will teach you the most important things you should do on your first week with a new instance.

Length: 60 minutes

Slides: (coming soon)

Code: (coming soon)

Maintaining High Performance When Using Encryption

Summary: Encryption is becoming required in more and more environments, but implementing encryption can dramatically affect performance. Learn how you can maintain high performance while using encryption in your database.

Abstract: Encryption is becoming required in more and more environments, but implementing encryption can dramatically affect performance. Learn how you can maintain high performance while still protecting your data with encryption. This session will examine communications, Transparent Data Encryption and a technique for using Symmetric Key encryption without a high performance penalty.

Length: 75 minutes

Slides: (coming soon)

Code: (coming soon)