Nowadays, it is quite common to write unit tests for code that we are creating, but not for all programming languages. When we are writing code for www, desktop or mobile application we get used to write unit test for code that is related to application. Together with that we are not testing database code. It is correct when we are using some ORM tools because such tools generate code for us.

Situation looks quite different when we are writing SQL code by our own. Then this code should be unit tested similar to other production code. To do that we can use [mark]tSQLt[/mark] library. Usage of it is very easy and you can fake views, tables, functions and stored procedures. You can find more details on library webpage.

Let’s focus on the main topic. I would like to show you how you can measure SQL code coverage. To do that you can use [mark]SQLCover Code Coverage for SQL Server T-SQL[/mark]. After running this tool you will get coverage report for SQL code:

SQL code coverage report – summary

SQL code coverage report – summary

SQL code coverage report – details

SQL code coverage report – details

Before you will be able to generate such report you need to download following tools:

Then please unpack files you have downloaded. And last thing that you need to do is update of configuration stored in SQLCover.ps1 file. You will need to provide information about database connection, path to ReportGenerator and also path where generated reports should be saved. Below you can find example of such configuration:

$result = Get-CoverTSql "SQLCover.dll" "server=.;integrated security=sspi;initial catalog=tSQLt_Example" "tSQLt_Example" "tSQLt.RunAll"
Export-OpenXml $result "e:\testReport\"
Start-ReportGenerator "e:\testReport\" "ReportGenerator\reportgenerator.exe"

And that is all. You can try to generate coverage report for your SQL code.

Although this tool can provide a lot of useful information I found two disadvantages. First one, minor  one, all code related to creation table variable in SQL script will be marked as code that has not been executed in unit test even when it was. So finally tool will decrease total code coverage in report and mark some files as partly tested.

The second one is more important for me. Each SELECT statement is treated as one instruction. Even when this SELECT statement is very long and complicated. Because of that, tool will inform you that whole code related to SELECT statement has been tested just after first execution of such statement. In this case you will get 100% code coverage for code in which only one test case has been tested. Moreover, I don’t believe that this behavior can be improved.

Check it:

Default ThumbnailFree tool for testing code coverage – PartCover Learning tests T9 Keyboard 1.0 Default ThumbnailMTS 2012 – The devil is in the details