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

Getting Started with Encryption in SQL Server 2016

The release of SQL Server 2016 gives developers a number of ways in which they can securely encrypt and protect their data. In this introductory session, you will learn about the encryption options in SQL Server 2016, watching Always Encrypted, TDE, Row Level Security, Dynamic Data Masking, and server side column encryption can secure and protect your data from unauthorized users.

Length: 60-75 minutes

Code: Github

Powerpoint deck: Getting Started with Encryption in SQL 2016.pptx

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

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

Code: TSQL_Windowing_code.zip

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

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