Testing T-SQL Made Easy


Everyone tests that code, but most people run a query, execute a procedure and run another query. This ad hoc, non-repeatable testing isn’t reliable, and encourages regression bugs. In this session you will learn how to begin introducing testing into your development process using the proven tSQLt framework. You’ll run tests with a click of a button, using an ever growing test suite that improves code quality. You will see how to handle test data, exceptions, and edge cases.

Level: 200 (I am assuming you know something about unit testing and tSQLt)


  • Creating exceptions to standards
  • Catching changes to large tables
  • Ensuring the join works correctly
  • Testing Getdate()
  • Isolating functions and stored procedures from other calls
  • Checking boundary conditions


Powerpoint: Testing T-SQL Made Easy.pptx

Code: .zip file or Github repo


  • SQL Bits XV – May 7, 2016

End to End Always Encrypted in SQL Server 2016

Abstract: Protecting our data from unauthorized access becomes more and more important all the time, however it has been difficult to ensure sensitive data is encrypted in SQL Server. The new Always Encrypted feature in SQL Server 2016 makes this much simpler for developers and DBAs with a framework for protecting data from the client, across networks, and inside of the database. This new feature allows for limiting access to the data, even from the DBAs and sysadmins that may control the database instance itself. Learn how to implement and use Always Encrypted in your applications.

Length: 60-75 Minutes

  • Demos:
  • Always Encryption Setup
  • Working with Data in a client application
  • Certification creation and transfer
  • Certificate Rotation

Powerpoint Deck: End to End Always Encrypted.pptx

Code: Github

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:

Code: GetTestingtSQLt.zip


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.