Unit Testing

Learn how to unit test SQL Server T-SQL code

  • Posted on: 5 September 2017
  • By: Ed Elliott

A free email course on how to use tSQLt including the technical aspects of writing unit tests AND the art of writing repeatable, useful unit tests for even the most complicated T-SQL code

UPDATE: I thought that if I got a certain number by October 1st I would run the course but in two days I had three times the amount of people subscribe than my initial target so I have closed the first course, sign up if you want to join the wait list or the next course!

Unit testing helps us to write better code, make rapid changes to our code and has been generally seen as a good idea for about 10 years. Writing tests for T-SQL code is made much easier by using tSQLt but there is quite a high barrier to entry both in terms of the technical skills in getting tSQLt running and also how to approach large code bases of, sometimes, unfriendly T-SQL code and taming the code with unit tests.

I have successfully unit tested T-SQL code in a number of different environments including clean greenfield environments as well as legacy projects and I have written this course to help people get started with unit testing but also help them to turn unit testing into a part of their development process that they can use everyday to improve the quality of their work and the speed at which deployments can be made.

Are you any of these people?

  • An application developer experienced with other testing frameworks for testing application code?
  • A T-SQL developer with no testing experience?
  • A -TSQL developer with testing experience in other languages or frameworks?

If you are then you should sign up (https://www.getdrip.com/forms/317903840/submissions/new) and let me help you learn tSQLt unit testing for SQL Server.

Why an email course?

I thought it would be an interesting way to provide actionable information regularly and to allow a level of assistance and feedback that I don't think is possible with blogging or writing articles.

How do I sign up?

Run over to: https://www.getdrip.com/forms/317903840/submissions/new and pop in your details.

The course is going to start on the 1st of October and as it is the first one I am limiting the amount of people who can start it. If the first one is a success then I will run it again but it won't be until at least 2018.

What will be the format?

The course will be one email a week which will include an overview of the weeks topic, some detail into the parts that need it and an excercise for the week which can be done on a demo database or any SQL Server database code you have.

and it is free?

yep, gratis. I am not open sourcing yet - maybe in the future but the course itself is free, aka "no service charge"

SQLCover Fixes and Download location

  • Posted on: 30 September 2016
  • By: Ed Elliott

There have been a couple of fixes in SQLCover this week, kindly submitted by John Mclusky (https://github.com/jmclusky):

Code coverage not reported correctly for CTEs at the end of a stored procedure if the 'with' is immediately preceded with a semicolon

and

DeclareTableVariableStatement statements cannot be covered, so report falsely as missing coverage

I have also changed where the releases can be downloaded from to use the github releases:

https://github.com/GoEddie/SQLCover/releases

The previous version is still available but I would recommend the latest version.

Finally I have started a seperate page to bring all the links together and will add a FAQ at some point:

https://the.agilesql.club/Projects/SQLCover

Happy testing!

SQLCover v 0.2 - Bug fixes and Azure V12 Support

  • Posted on: 5 May 2016
  • By: Ed Elliott

I have released a new version of SQLCover which is a code coverage tool for T-SQL (let's you identify where you need to focus when writing tests).

This includes a few minor fixes but also support for SQL Azure so if you run your test in a v12 database or higher you can now get an idea of code coverage from that.

If you are interested in using this but don't know where to start, there is a powershell script in the download (https://the.agilesql.club/SQLCover/download.php) and if you also get reportgenerator (https://github.com/danielpalme/ReportGenerator/releases/tag/v2.4.5.0):

Once you have downloaded SQLCover, extract the files and right click "SQLCover.dll" go to properties and click "Unblock"

Then in powershell run:

. .\SQLCover.ps1
$result = Get-CoverTSql "path\to\SQLCover.dll" "connection string" "database name" "query"

$outputFolder = "C:\some\path"
mkdir $outputFolder

Export-OpenXml $result "$outputFolder"
Start-ReportGenerator "$outputFolder" "c:\path\to\ReportGenerator.exe"
 

Change the path to SQLCover, the connection string, database name, query (tSQLt.RunAll), output path and path to report generator (phew) then run it and it should create an "out" directory under the output folder - open index.html and see the awesomeness of the reportgenerator output:

look how awesome code coverage is for t-sql

If you want to run mstest tests or nunit or something else completely then have a look in the SQLCover.ps1 which includes some examples at the bottom but Get-CoverExe is probably your friend.

Any questions please shout!

ed

What is code coverage for?

  • Posted on: 11 April 2016
  • By: Ed Elliott

Code coverage gives you an indication of how well tested a particular area is. It is not a measure of code quality and having a statement covered by a test does not mean that the code is accurate.

Using code coverage you can see how risky changes are to a particular area of code. If you have a high level of code coverage you can be fairly confident in changes, if you have low code coverage in an area then when you make changes you will need be careful.

If we take an example, you could have this code:

create procedure blah
as
if (select count(*) from table where col = 'a') >= 1
begin
declare @res int = (select count(*) from table where col = 'a' and (select count(*) from table where col 'a') = 0;
select @res;
end

If you also have this test:

exec tSQLt.FakeTable 'table';
exec blah;

Technically there is a test around the procedure but because it doesn't setup any test data or check the result it is only covering the "if" statement and not the "then" part of it.

If we use code coverage we can see that the actual work to generate "@res" is not run so it gives us a pointer that we have poor tests covering this procedure.

Should I aim for 100% code coverage?

This is a difficult one to answer because although having a high percentage of code coverage is useful, it really depends on the code and what it is doing. If you have a stored procedure such as:

create procedure blah
as
if (select count(*) from table where col = 'a') >= 1
begin
declare @res int = (select count(*) from table where col = 'a' and (select count(*) from table where col 'a') = 0;
select @res;
end

The I would like to see 100% code coverage, if I had something like:

create procedure blah
as
begin try
insert into table(column) select 'value';
end try
begin catch
print 'this should never happen'
end catch

Then is it the most important thing to have the print statement covered?

Where it gets really complex is if you have different branches which mean that 100% for a particular area just isn't enough and you want to aim for each branch to be hit the right number of times, for example:

create procedure blah
as
declare @operation int = (select count(*) from table where value = 'a')
select @operation = @operation + (select count(*) from table where value = 'b')
if @operation > 1
declare @res int = (select count(*) from table where col = 'a' and (select count(*) from table where col 'a') = 0;
select @res;
end

In this case I am interested in the two different ways to get into the "then" part of the if statement, whether we have values in the table for a or b.

The thing to note is that coverage is used to give you an indication of where you have a full set of tests rather than as a way to gauge the correctness of the code.

What about sub-queries?

It is possible to write queries such that sql will not use part of them when executing the statement and although it would be interesting to know what parts of the query run and return valid results that is really the point of writing unit tests and proving the correctness of the code. Code coverage tells you whether you have tests that execute the actual statement and it is down to you to make sure the code works as you expect.

SQLCover

To view code coverage in SQL Server grab a copy of SQLCover:

https://the.agilesql.club/blogs/Ed-Elliott/2016-04-08/SQLCover-Code-Cove...

ed

SQLCover Code Coverage for SQL Server T-SQL

  • Posted on: 8 April 2016
  • By: Ed Elliott

Open Source code coverage tool for T-SQL, SQL Server 2008+

What is code coverage?

Code coverage is a way to see how many statements in your database code have been executed when you ran your tests(s). It is a way to see how well covered with tests a particular area is - the better covered with tests, the less likely you will miss issues when you make changes in those areas.

What is code coverage for SQL Server?

SQL Server gives us a great tracing mechanism, either profiler or extended events which details exactly which statements have been run. SQL Server doesn't do so well at telling us what statements we could possibly run in some code but the Transact Sql Script Dom that is part of the DacFx does give us the ability to break T-SQL code into statements so combining the two we have the ability to take a stored procedure such as:


create procedure abc.def
as

--select 100 from a_table but_is_commented_out_so_do_not_count_me
select 100;
select 200
select 300
begin
select 400;
end
if 1=2
begin
select 500
end

go

and we can use the scriptdom to tell us that there are 6 statements or "lines of code" in this procedure and when we run it while covering it we get the details that all of the statements apart from "select 500" are run which gives us a code coverage of 5 out of 6 statements.

Who decides what a statement is?

Partly me but mostly SQL Server itself, if you run a trace with SQL Server/sp_statement_xx you can pretty easily see that the select's are statements, the begin/end by itself is not a statement and the if is a statement - this makes sense to me, the begin/end doesn't actually do anything it is just to help you keep your code tidy, for example this is valid SQL and is only 1 statement:


begin
begin
begin
begin
select 'wowsers'
end
end
end
end

Where can this be used

This version of code coverage is a .net dll with some examples in powershell so it can be used with any build server or test framework, but it was written primarily for tSQLt and has some filtering to filter out tSQLt objects and test procedures so anything that is in a tSQLt test class will be ignored. It was also written to fit into the Redgate DLM automation suite so if you have that then I would recommend using it.

How do I use it?

This first example I will use the SQLRelease module from the DLM suite...

  • 1. Grab the zip from: https://github.com/GoEddie/SQLCover/releases/
  • 2. In powershell dot source the "SQLCover.ps1" file
  • 3. Call "Get-CoverRedgateCITest", you will need to pass in the path to the "SQLCover.dll", the connection string to the database, the path to the nuget package to test and a server and database where the code can be deployed to.
  • 4. The return from Get-CoverRedgateCITest is a 2 object array, the first is the Redgate results from the tests themselves "RedGate.SQLRelease.Compare.SchemaTesting.TestResults" which can output a junit xml file. The second is a SQLCover.CoverageResult object which as well as giving the count of statements in the database and covered statements while running the tests lets you output the code coverage results as xml or in the open coverage xml format which can be converted to a pretty report using the https://github.com/danielpalme/ReportGenerator tool.

In powershell code that is:


. .\SQLCover.ps1
$results = Get-CoverRedgateCITest ".\SQLCover.dll" "server=.;initial catalog=database_name;integrated security=sspi;" ".\path\to\nuget.nupkg" "." "database_name"
Export-OpenXml $results[1] "c:\output\path\for\xml\results"
Start-ReportGenerator "c:\output\path\for\xml\results" "c:\path\to\reportgenerator.exe"

You will need to obviously provide the correct paths, a nupkg file and if you want a pretty report then reportgenerator.exe.

If you don't have SQLRelease then you can do the same thing but you must deploy the code yourself first:

  • 1. Grab the zip from: https://the.agilesql.club/SQLCover/download.php
  • 2. In powershell dot source the "SQLCover.ps1" file
  • 3. Call "Get-CoverTSql" this returns a CoverageResult object which as well as giving the count of statements in the database and covered statements while running the tests lets you output the code coverage results as xml or in the open coverage xml format which can be converted to a pretty report using the https://github.com/danielpalme/ReportGenerator tool.

In powershell code that is:


. .\SQLCover.ps1
$result = Get-CoverTSql ".\SQLCover.dll" "server=.;initial catalog=database_name;integrated security=sspi;" "database_name" "exec tSQLt.RunAll"
Export-OpenXml $result "c:\output\path\for\xml\results"
Start-ReportGenerator "c:\output\path\for\xml\results" "c:\path\to\reportgenerator.exe"

If you wanted to call an exe, for example an nunit test runner or msbuild then instead of calling Get-CoverTsql you can call Get-CoverExe which will start the coverage, then start the exe and stop the coverage when it completes. If you want to completely control when coverage sessions are started or stopped then SQLCover.dll has a .Start() and .Stop() methods, see Get-CoverRedgateCITest for examples.

What output do you get?

You get a CoverageResult object which has two public properties:


public long StatementCount;
public long CoveredStatementCount;

These give you the ability to react to a poor coverage result or you can convert the CoverageResult to:

  • 1. Basic Html report "$result.Html()"
  • 2. Open Cover Xml format which can be converted to something pretty or you can parse it yourself

I would strongly suggest you grab a copy of reportgenerator so you get reports that look like:

Code coverage report generated using reportgenerator.exe

Code coverage detail generated using reportgenerator.exe

Oooh pretty....

SSDT Dev Pack

The ssdt dev pack already has code coverage but it is only within an ssdt project and it isn't possible to generate code coverage results as part of a build process so although I will still work on that, I will migrate that over to using this at some point.

Finally.

I just wanted to say a big thanks to David Atkinson at Redgate, he sponsored the project which meant that I could focus on it and provided some great feedback and guidance along the way :)

Unit Test Sql Server Code

  • Posted on: 3 March 2015
  • By: Ed Elliott

In part 3 of this series of blogs on "getting more agile" I am going to look at unit testing and why we need to do it.

My code is perfect, I need no stinking tests

Ah you are like me, my code is perfect as well, good point. blog end.









































actually...your code may well be perfect but:

  • a) It only got perfect by you writing the code and writing a test script to make sure it worked (you did at least test it once, didn't you?)
  • b) Everyone else in the world (basically everyone other that you and me who are the only people, dear reader, who write perfect code) write horrible code and do terrible things like change table and column definitions, the blighters
  • c) Those other horrible developers and DBAs need examples of how to use the code
  • d) They also need documentation on how it works, what it returns in all cases and what side effects it has as they are too simple to work it out for themselves, the welps

ok, I admit it, I am one of those welps - what is more, I make changes to code without examining every stored procedure, every function, every piece of application code to see if it will cause any issues - I am literally a renegade throwing caution to the wind plus I write very little documentation, I certainly never explain what I have done to a stored procedure, I don't need to. I can do this because I write unit tests and make sure that every piece of code that can run is covered by a unit test.

The tests:

  • Prove that the code I write, does what it should do (the testing part)
  • Stops me breaking any other part of the database or application, without me knowing about it and giving me a chance to either fix it or reasses the change (this is a big one)
  • Helps document the code and give examples of how it works, see https://the.agilesql.club/Blogs/Ed-Elliott/Unit-Tests-Help-Document-Your...

Tests are also an enabler for much more exciting things namely continuous integration and continuous deployment. To get CI/CD working you need a number of things, one of those is unit tests - without these the whole thing is pretty much pointless.

But I have a big database without unit tests, I can't start now

Yes you can, write tests for all new code you write and all changes you make to the existing code - but before you do that, go read this: http://www.amazon.co.uk/Working-Effectively-Legacy-Robert-Martin/dp/0131...