Code Coverage

What is code coverage?

When you write some code and then test it, how sure are you that you have tested the whole thing? Code coverage gives you an idea of how well tested a bit of code is.

If you have lots of branches in your code (not something I am advocating) it is important to make sure you test it all so we can use code coverage to get an idea of how much of a particular piece of code has been tested (or not).

How code coverage works with SQL Server (i.e T-SQL) is that because SQL provides a pretty good interface for tracking statements which have been executed (extended events) we can tell which statements have been called. So if we take this example stored procedure:

create procedure a_procedure(@a_value int)

as

begin

if 1=1

begin

select 1

end

if @a_value = 9

begin

end

select a, case when @a_value = 1 then 'a' else 'b' end from table_name;

end

If we execute this stored procedure we can monitor and show a) how many statements there are in this and also b) which statements have been called but we can't see which branches of the case statement were actually called. If it was a compiled language like c# where we have a profiler that can alter the assembly etc then we could find out exactly what was called but I personally think knowing which statements are called is way better than having no knowledge of what level of code coverage we have.

How do you use code coverage

I use it as a way to explore what parts of the code have low test coverage as it means that I need to be more careful about making changes in those areas.

I mostly work with legacy applications rather than new ones (my favorite thing is debugging) so often end up looking at big pieces of T-SQL without any tests which have an air of spaghetti code about them (not always just mostly!) so it is a good way to make sure as I write tests, all the diffent things that the code is supposed to be doing.

How do you not use code coverage

Don't set a requirement that the code must have X % of covered code.

Why

Each piece of code is unique, sometimes you don't really need to test it and sometimes you need to make sure a statement is called at least 20 different ways - code coverage is a guidance and advice tool not a cut and dry sort of a thing.

How do you measure code coverage in SQL Server?

This brings me neatly to the point of all this - you can either do it manually or you can use my new version of the SSDT Dev Pack (groan):

If you grab it from:

https://visualstudiogallery.msdn.microsoft.com/435e7238-0e64-4667-8980-5...

In SSDT if you do Tools->SSDT Dev Pack-->Code Coverage" you end up with this lovely little window:

If you click on "Start Capture" it will ask to connect to SQL Server, go ahead and put in the server details and choose the database you want to monitor.

When you have done that go and run your test code - this could be a manual script, a tSQLt.Run or even you could run your application - just do whatever you want to see how much of you database is covered.

When you have finished then click "Stop Capture" and the dev pack will then enumerate your entire solution for procedures and functions and find out how many statements there are and it will then parse the results of the extended events trace and find which of the statements in the SSDT projects have been covered. You then get this nice little tree view which shows the cumulative amount of code coverage (statements that were executed as part of the test):

In this case we can see that there is one project and one procedure and we have around 62% code coverage (pretty good for a SQL Server project!).

You get ths usual things like double clicking on an item takes you to the document that it exists in but more interestingly we can show the code coverage in the code itself, if you enable "tools->ssdt dev pack-->Display code coverage in Documents" and then click on the procedure in a document window you get:

which is pretty cool if you ask me! What this shows is the statements that have been covered and we can see here that the statement wrapped in the "if 1 = 2" was not called which is lucky (otherwise it would be a pretty poor demo).

Things to note

This uses extended events so you need to have the right permissions to create them (you should probably be using localdb or a local dev instance anyway).

You will need to manually delete the extended events trace files periodically, they are written to the sql log directory and are called CoveCoderage*.xel (and xem on SQL 2008 r2).

If you change the document then I don't know if the offsets will match up so I stop showing the covered statements until you re-run the trace.

If you need any help with any of this give me a shout 🙂

Ed