Unit testing constraints in Sql Server

I generally break my unit tests for T-Sql into two broad categories, the first is the schema, things like foreign keys and defaults and the second is the actual code, stored procedures and functions (triggers as well but I do my hardest not to write triggers on account of them being so awful.)

When I look at what tests to write for constraints, I ask myself the question, as I do any time I write anything “what is the point of this, do I need to write it?” and I normally come back with the answer (it is a lot of fun in my head, it is like a regular party with everyone super excited about sql server) “if there is no point in writing the tests, is there any point in the original code”, I then think whether the constraint is necessary and adds anything, if it does then it stays and I write tests for it.

I write tests for the schema as a schema is code, it might be dml instead of ddl but you better believe it is possible to get something wrong and for someone to delete something by mistake and most importantly that I or someone else will want to refactor it later and knowing what will break when a change is made is extremely important.

Sample Schema:

sample schema to demonstrate testing constraints with tSQLt

sample schema to demonstrate testing constraints with tSQLt

To write the actual tests I use tSQLt as my preferred framework and I fake the child table and the reference table and use tSQLt.ApplyConstraint to test the actual constraint. If we take this simple schema, two tables and a foreign key, if we then look at how we test the constraint, what do we care about:

– I care whether the key is defined correctly so will accept values in the child table that are in the parent table
– I care whether the key stops incorrect values going into the child table

I don’t really care if the foreign key allows all the possible values, that is Sql Servers job, I just want to ensure it does what it is I have told it to do.

To write tests, I create a new test class:

exec tSQLt.NewTestClass ‘constraint on users.users gender_id’

I could have one class per table or one per database but there isn’t much of an overhead having lots of classes and it helps to keep them separate and organised so I create one class per constraint.

Once I have the test class, I create the setup routine that will be used by my test “methods” (procedures). The Setup procedure is run before each test and it means you don’t have to keep doing the same thing so it is less rrepetitive and less error prone, remember kids no one is testing the tests!

create procedure [constraint on users.users].[SetUp]
exec tSQLt.FakeTable ‘gender’, ‘users’
exec tSQLt.FakeTable ‘users’, ‘users’

insert into users.gender(gender_id, gender_description)
select 1, ‘Male’

exec tSQLt.ApplyConstraint ‘users’, ‘FK_users_gender_id_genders’, ‘users’

In this case, I fake out the two tables involved in the relationship and in the parent table I add a single row. I could add every possible row and then check they work but I believe that is overkill and while it might be straightforward for gender, it is a little more involved for products that have ever been sold at a major supermarket or retailer. With the data added I then ensure that the constraint is applied and ready to allow good data to go in and block bad data and keep it out.

I then have my “happy path” test which tests that I can add data to the child table:

create procedure [constraint on users.users].[test allows male]
insert into users.users(user_id, gender_id)
select 100, 1

I typically always expect this to work, but it I have setup the foreign key to point to another column by accident then the insert will fail as in my test setup I only inserted a valid value into the gender_id column in the genders table.

Once I have the good path that should work if I have set everything up correctly, I then look to the unhappy path, that is what happens when something goes wrong – if I try to insert data that is incorrect, does it fail? When it fails, does it fail in the way I expect?

create procedure [constraint on users.users].[test disallows unknown values]
exec tSQLt.ExpectException @ExpectedMessagePattern = ‘%INSERT%conflicted%FOREIGN KEY%’
insert into users.users(user_id, gender_id)
select 100, 94898

So here all I am doing is saying that I expect to get an exception somewhere in this procedure and when I do the error is going to match the pattern “%INSERT%conflicted%FOREIGN KEY%” if it doesn’t then the test will fail. I then do the insert with a value I know not to exist (remember I am faking both tables in the Setup so know exactly what values are in each table.

Finally I need to run the tests using either “exec tSQLt.RunAll” or “exec tSQLt.RunTest ‘constraint on users.users’

The tests will run and either pass or fail, to do TDD properly you should create and run the tests, the first one should always complete so it is hard to get the red before the green but the second test should fail before the foreign key is put in, when it is added then the test will succeed as there will be an error message.

I personally think that this finds the right balance between writing tests for the sake of it (1 test for every possible value in the reference table!) and writing enough to know that the constraint is in place and it is doing what it should do – irrespective of whether the values in the table are actually correct.

Like most things in life and in IT the amount and type of tests depends on the type of application and how the application is being used, a test database for me to play around with sql doesn’t really need unit testing but a database that is critical in a medical EMR application should be well tested.

The code that goes along with this is:



Modern T-SQL Development (#MTSQLD)

I was watching this TechEd (I have always loved that name!) video from 2011 about the new way of doing database development, in a modern way – Sql Server Data Tools or SSDT:


It got me thinking how far we have come in the last 3 years and I really feel strongly that as T-Sql developers we have tools and practices we can follow to make our code maintainable, reliable and enjoyable!

We can build systems which are well tested which brings with it a well defined host of benefits such as:

  • Ability to refactor T-Sql code & data schema
  • Confidence that T-Sql code is valid and works as expected

We can concentrate on designing schemas and writing code rather than writing scripts to upgrade schema’s when we deploy using:

  • DacPacs + SqlPackage.exe
  • Redgate Sql compare
  • ApexSql Diff

We can ensure that our code is safe and we can delete unused code because we have source control, my preferred is Git but anything will do, heck even the much derided visual source safe would be an improvement over most T-Sql code projects!

We can refactor code and schemas using SSDT to rename and move objects, we can make changes and build a project and get instant feedback about whether we have broken anything.

We can improve the speed of our development using intellisense and if we are lucky enough to have resharper we can just straight to objects we know the name of (ctrl+t with resharper shortcuts).

We can find out what references objects effectively and find unused objects etc.

We can build a continuous integration pipeline, we can check code in and have a build system like TeamCity checkout the code, build the project, deploy the project and tests and then run the tests – there are a few separate parts to set up here but it is easily doable.

To some extent we can debug T-Sql, this is an area that definitely needs improving but at least we can step through and see the values of variables – until we can visualise what is in tables I think T-Sql debugging will always be lacking.

I am really passionate about modern T-Sql development, please take a look at how you develop T-Sql code and think about whether you are using modern techniques to develop code and whether you life would be any better if you started employing some modern T-Sql development techniques.


In looking around for other resources for modern T-Sql development I was unable to find that much so thought it would be great to start a linked-in group to start collating and sharing best practices, if you are interested please head over to the new “Modern T-Sql Development group on linkedin:



SSDT & tSQLt – a serious double act!

Using SSDT to develop T-SQL code is really nice, although in my opinion it isn’t as nice as developing C# yet, it is a great improvement on using Sql Management studio alone. In this day and age all production code should be unit and integration tested and tSQLt is a great solution to help unit test T-SQL code and the two technologies together really make an excellent pair and give us all a great way to develop quickly and with the minimum amount of bugs, if your testing is done correctly.

I find that there are often parts of a database project which naturally feel like they should be done in Visual Studio SSDT such as creating tables and stored procedures and then some parts which work better in SSMS, particularly running tSQLt unit tests with the Redgate Sql Test tool. This doesn’t need to be the case and it is possible to both write and maintain a database schema completely from within Visual Studio which, I think, is one of the best development environments available.

In this post I am going to talk about how I structure my visual studio solutions and how the different parts interact to create a modern development workflow.

The major components in the projects I create are:

  • Database Schema project
  • Database tSQLt unit tests project, either one for all tests and the tSQLt framework or multiple depending on the size of the project
  • NUnit test runner to run the individual tests.

The NUnit test runner is not strictly necessary but I find it means that I can write and run tests from within visual studio (I would highly recommend resharper to help here) and the tests can be run the same way on the Continuous Integration server. There is a tSQLt plugin for team city but there is a NUnit test runner for pretty much every single build system out there.

Database Schema project

This is simple, create a sql server project and use it to write you schema and T-SQL code!

Database tSQLt unit tests project

We then need somewhere we can put our tests, I like to keep them separate from the production code as it means you can validate the production code and run things like the T-SQL Smells tool (http://dataidol.com/davebally/2014/01/28/tsql-smells-in-SSDT-the-monster-lives/) against them without worrying about test code. To be clear I am not saying the test code should be poorly written, just that we can ensure stricter controls over production code and ensure a separation boundary.

To get the tSQLt objects into an SSDT project I can deploy, I initially deployed them to an empty database and the used the schema compare in SSDT to bring the objects into an SSDT project:

tsqlt in ssdt project

NUnit test runner

You need to have some way to run your tests, some people include a post-deploy script which calls the unit tests but I prefer to spend the extra time writing wrapper methods using NUnit so that I can run the tests either as a whole, as a class or individually. I guess that I really prefer using NUnit because I have used it a lot in my c# development and the resharper add-in for running unit tests is really nice.

To help use NUnit I typically write a test helper in C# which creates a Sql connection and calls tSQLt.Run with the name of the test to run and ensure I read in all “InfoMessages”. When you run a tSQLt test you get some messages printed to the output window, including how many tests were run successfully which you need to check, rather than relying on there being 0 errors, there must also be 1 success. You may also get an exception which will be caught by NUnit and used to report that particular test as a failure.

The helper class looks like:

TestGateway.RunTest Helper

Those are the major components and now to add a test, I just need to create a new schema that will be used to store the tests. In order that tSQLt knows to treat a schema as a test class you need to add the extended property:

EXECUTE sp_addextendedproperty @name = N’tSQLt.TestClass’, @value = 1, @level0type = N’SCHEMA’, @level0name = N’HumanResourcesTests’;

You will need to change the level0name to the name of your test schema.

If the unit test and main schema are in different databases then I will also need to add a database reference and use the fully qualified name to get to the other database, this is really trivial and means you get intellisense in SSDT and can run the procs in the same database when deployed.

I am then free to add a test, the new test should be in the schema of the test class, in this case HumanResourcesTests. The test name should also begin with the word “test”:


tsqlt test


Finally I add the NUnit test so that the test helper can be called to run the actual test:

nunit test to run tSQLt test


Running Tests

To mock or fake a table tSQLt currently only supports tables in the same database so you need to deploy all of your code into the same database. You should ideally create a new database, insert any needed data and fake any tables you will interact with and then destroy or have some system to tidy up old databases.

To run the tests you need to deploy the main schema, then deploy the tSQLt project and your tests, when they have been deployed you can then run the NUnit tests, hopefully you are using TDD to write your T-SQL code so the test will fail, you then write the code to make it complete then re-deploy and re-run the tests and you should see little green ticks:

green tests

Including these tests into a continuous build process is quite simple, you just need to get your build system to use the dacpac files created by the SSDT projects, deploy those and then use whatever calls the NUnit tests to call these tests. Because you are using NUnit to run the tests, any failures and messages will work as if the tests were pure c# code.

I am sure there are lots of other ways of working with SSDT and tSQLt but this is how I use it and it feels really good to write, clean, unit tested T-SQL in a modern development environment (intellisense, find references etc).

I have put the sample project in this demo on github, feel free to grab it:



Dac Fx Api – Use it, write better tools for Sql Server!

The DacFx Api is an api for querying the object model that both DacPac files and SSDT use, it is a giant leap forward in the available tooling for Sql Server and hopefully there will be a raft of great tools to make T-SQL development better.

I really enjoy writing c# code. I typically use visual studio with Resharper and add NUnit and Moq to my test projects and this setup makes developing and testing really simple and removes a lot of the boring repetition you sometimes get. I also love developing T-SQL but the development environment is nowhere near as smooth.

When I first started looking at the DacFx api, I was a little confused as to how it worked, even after reading the introductory blog (http://blogs.msdn.com/b/ssdt/archive/2013/12/23/dacfx-public-model-tutorial.aspx) and when I finally realised what the implications were of the painless sounding sentence “The public model API is loosely typed: the TSqlModel contains loosely typed TSqlObjects that represent all the elements in your schema.” my heart sunk a little as it felt very similar to every object being returned as a System.Object and then having to cast it to the correct type (you don’t by the way, you leave it as the TsqlObject).

What this means is that all objects in the model, a table, schema, procedure etc are all the same type, a TSqlObject. To get any information such as what columns belong to what table or index or whether the index is clustered or not you need to query the objects and ask for the information you want, in reality what you have with the DacFx Api is a queryable set of classes, you just have to know what you have and what you want and although it is a different paradigm to pretty much every API I have ever seen, it is fairly straight forward. I have put an example application which opens a dacpac file and dumps out a list of indexes which gives an overview of how to use the api to get the information you want.

To open a dacpac file and get a TSqlModel object which is the entry point to the API you just need to call:

var model = new TSqlModel(filePath, DacSchemaModelStorageType.File);

When you have the model you can then query it for all the objects you would like, if you want all the indexes in the model just do:

model.GetObjects(DacQueryScopes.Default, Index.TypeClass)

GetObjects searches for and returns a list of specific object types, the query scope means all objects, you can limit it to just system objects or just user objects etc. Finally the TypeClass causes a list of just Indexes to be returned.

The way the TypeClass works is that every object such as a table, index, column etc has a TypeClass and also on the static class that has the TypeClass such as Index or Table there are a number of properties which allow you to access the actual properties you want. If this sounds a little strange, I think it is, but it is fairly straight-forward with an example! We will call the class that holds the TypeClass and other property classes the container class as that is what msdn seems to call them. If you retrieve a list of all the indexes and you have a TSqlObject that you got by using the Index.TypeClass (as above) then you can query the index for its properties. The Index container class has, amongst others, these properties:




You can then use these properties to get the value you want from your index TSqlObject instance, so if you do:

foreach (TSqlObject index in model.GetObjects(DacQueryScopes.Default, Index.TypeClass ){

var isClustered = index.GetProperty<bool?>(Index.Clustered)


You will get a nullable bool saying whether the index is clustered or not. If you do not know the type of the object you want you can call the non-generic version index.GetProperty(Index.Clustered). The example at: https://github.com/GoEddie/DacFxApi/tree/master/DumpIndexProperties (repo: https://github.com/GoEddie/DacFxApi/) shows some extra information like how to query the relationships to get the columns and included columns that are on the index (in, on, used by??) and how to generate the T-SQL script to deploy the index.

I strongly believe that as Sql Server developers we have the ability to really help move our tooling forward and if you throw into the mix the TSql scripting stuff, the next few years are really going to be exciting times for us Sql devs!