views:

222

answers:

3

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?

+3  A: 

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.

Ed Harper
Ed, I thought DbFit is kinda fragile, difficult to maintain. If you change something, you have to do spend a lot of time adjusting your tests. What do you think?
AlexKuznetsov
@AlexKuznetsov - To some degree, that's the nature of DB unit testing - it's the problem with all the tools I've tried. I found that DBFit goes the furthest to alleviate the problem, once you take account of the ability to include other tests/pages inside a test. With a bit of planning, tests can be structured to minimise the pain of refactoring.The other positive I've found is that the tests are comprehensible to less technical users in a way that, say, TSQLUnit/NUnit tests aren't.
Ed Harper
Ed, what do you do when your expected output has changed? DO you fix your tests manually or do you generate new expected output?
AlexKuznetsov
@AlexKuznetsov - as far as possible, I try to work with a "test first" methodology, so I'd manually alter the expected results before changing code.
Ed Harper
+6  A: 

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

Have a look at this TSQLUnit.

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.

StarShip3000
StarShip3000,Are you using TSQLUnit? Do you compare the whole result set against the expected values, or do you match individual values separately?
AlexKuznetsov
Currently no I'm not use TSQLUnit I was in the past. I now use a unit testing framework from Ruby and have Ruby execute my sql script which contains 1 test via a call to sqlcmd. This call then returns a pass fail value and string description to ruby. I'll dig up simple example when I have some time.
StarShip3000
+1  A: 

Like regular code, some things in T-SQL have to be tested differently than others. If you code-generate a lot of static things (like triggers or views), typically the testing load is lighter.

Our particular system is almost completely coded in T-SQL.

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

We have known good results that we compare against. On our monthly analysis runs we compare month on month and run on run to understand the impact of code and data changes. Our main tool is a stored proc which will compare two tables/views/subsets and identify differences (with threshold options).

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

We validate the data produced in most processes against historical behavior. When making code changes due to business requirements, an impact analysis is performed comparing outputs from both runs. We also rely heavily on exception reporting to determine in advance when data is not conforming to expectations/configuration (constraints in the schema where possible).

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

Yes

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

Our unit testing is at each of our "process" levels. Each process usually corresponds to a single stored procedure - so that's the unit we test. Comparing the final outputs would correspond to system testing.

Cade Roux
Cade Roux,Can you elaborate on why did you choose T-SQL to code your tests in?
AlexKuznetsov
@AlexKuznetsov Because our system is completely in T-SQL already (apart from a control panel), it made sense to have all the testing in T-SQL.
Cade Roux
@AlexKuznetsov Our system is also a monthly batch processing/data analysis system, so we have a large universe of data and expected outputs. After each month's production run, we are able to refactor and run many cycles, ensuring that the output changes only as expected.
Cade Roux
@AlexKuznetsov We also created tabs in our control panel which monitored the system health by doing semi-static analysis of code in the SPs - looking for unused columns in the dependency metadata, looking at un-indexed tables, looking for unexpected external database references (we used a lot of extended properties to tag our db objects with additional properties).
Cade Roux
Cade Roux, how do you store expected outputs. Do you hard code them, do you store them in a table, or XML file, or what?
AlexKuznetsov
@AlexKuznetsov We store each cycle's result history in an auxiliary table of the same name in another "History" schema with the addition of a CYCLE column. Then we can compare results of different CYCLEs by cimply comparing different sets within the same table. Our CompareTable SP has subset parameters for left and right sets, so we simply call it: CompareTable @table1Name = 'History.ResultsOfProcessX', @table2Name = 'History.ResultsOfProcessX', @subset1Criteria = 'CYCLE = 1', @subset2Criteria = 'CYCLE = 2'
Cade Roux