I recently had the chance honour to spend twenty minutes talking to Boris Hristov about testing with Sql Server for his Google Hangouts series, catch the video here
One of the things I mentioned was that having a suite of unit tests helps to document the code and in this post I would like to demonstrate that. If we take the first stored procedure in the AdventureWorks2012 database to see how it is documented, if we take the procedure definition, we get:

If we look at this in terms of documentation, if we start with the name “uspGetBillOfMaterials”, I guess usp means it is a stored procedure and it gets a bill of materials. There aren’t any comments in the header. If we then look at the parameters we have StartProductID and CheckDate which help to describe how it is used.

We then have three extended properties, the first is most useful for documenting the procedure:

“Stored procedure using a recursive query to return a multi-level bill of material for the specified ProductID.”

Finally we have a comment in the procedure itself:

“Use recursive query to generate a multi-level Bill of Material (i.e. all level 1 components of a level 0 assembly, all level 2 components of a level 1 assembly) The CheckDate eliminates any components that are no longer used in the product on this date.”

For me, although this gives a technical description of what is happening, I have to look in three different places to get the overview and it doesn’t really explain how the procedure works and what it should and should not do. If instead of extended properties and a comment inside the procedure we had a set of test stored procedures called something like:

[uspBillOfMaterials][test returns all level 1 components of a level 0 assembly]
[uspBillOfMaterials][test returns all level 2 components of a level 1 assembly]
[uspBillOfMaterials][test does not return level 2 components of a level 0 assembly]
[uspBillOfMaterials][test does not return components no longer used by CheckDate]

These tests tell us what the procedure should do and shouldn’t do additionally if we always have a set of tests it makes it much easier to explore your T-Sql code. To highlight the difference a good set of tests can make, take these two screenshots - which one is clearer and documents the procedure better? When you look at both of these consider that this is a pretty small procedure, 5000+ line procedures are pretty common (unfortunately!) so if this was more complicated, there could be more tests, possibly separated into sections and there could be more comments anywhere in the code or even worse and very common is no comments or misleading comments:

-or-

Hopefully this has helped explain why having unit tests helps to document code and as I said on the hangout, if you have any questions about testing with Sql Server please ask as I know we can figure out a way to use unit testing to improve the life of Sql DBA’s and Developers!

&nbsp