How do you unit test your T-SQL [closed]

Question

How do you unit test your T-SQL? Which libraries/tools do you use?

What percentage of your code is covered by unit tests and how do you measure it? How do you decide which modules to unit test first?

Do you think the time and effort which you invested in your unit testing harness has paid off or not?

If you do not use unit testing, can you explain why not?

Answer 1

How do you unit test your T-SQL? Which libraries/tools do you use?

Have a look at this TSQLUnit and this tSQLt.

I have experimented with using a few different frameworks in many languages to test T-SQL, such a junit or nunit. The reason I went this route was to take advantage of the rich testing environments in these other languages. If for example you use nunit it has a nice command line and gui viewer to look at the status of your tests and because your using .net to write your tests it hooks into sql server very easily. JUnit has some nice integration with a lot of commercial and open source IDE environments such as NetBeans and IDEA as well making T-SQL testing more like Java code testing, which is very rich. The negative is you are not only writing T-SQL you are also writing java or .net to test your T-SQL.

I have also used Ruby with Rake(it's like make or ant) and made system calls to sqlcmd to executed sql scripts which in turned contained tests. The scripts return values and strings back to ruby to pass or fail a test. I liked this approach the best as it was very lean and easy for others to pick up on. The other routes mentioned above required developers to use .net or java which if you have all DBA types might be hard to overcome where the ruby with sql scripts approach is easier to sell from my experience. DBA types tend to be open and able to pick up scripting like languages easy and Ruby has a low learning curve and the scripts are easy to run relative to a .net or java class.

What percentage of your code is covered by unit tests and how do you measure it?

Probably only 20% only because most databases systems have historically not had tests created for them as they were created so I'm in the process of retro actively adding tests and adding tests as new defects and enhancements come up.

Do you think the time and effort which you invested in your unit testing harness has paid off or not?

The database is the bread and butter of most businesses. It always pays off in my opinion. If your business tolerates junk and high failure rates for the customer then no testing is probably not worth it as it's not free to do.

If you do not use unit testing, can you explain why not?

I will love to see if anyone comes up with an answer for this that isn't absurd. Kinda like why don't you put your kid in a car seat when driving down the road..."They cost too much"..."Takes too much time to put the child in it".."He's safe without it".."What could go wrong to justify it".."There's just not enough time" All of these are bs.

Yeah I know a little extreme maybe, but really if you have a system and it brings the company value it should be united tested to help catch some issues before they become production issues. Unit testing isn't a panacea, but it's one tool we have to do the best we can.

I would say overall somehow most people give the database a free pass when it comes to structured testing. I have no idea why this is, but I have seen it at company after company. I would love to see that change.

Answer 2

For a unit-testing tool, I've had most success with DBFit.

I've never found a tool to measure TSQL test coverage.

Having accepted that there's a level of pain involved in setting up tests, I've found that I can improve the quality of my code by unit testing, once a project reaches a more than trivial level of complexity.