What is code coverage for?

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

Add new comment